Any Excel macro talent out there?

[bagman67]

I have a few hundred Excel files that represent output from several customer service inquiry databases.  Each line has to be evaluated separately for relevance to requests for information that we are satisfying.  I have an awk script that will take each line of a flat text file and write it to a separate html file for record-by-record review and production, but I lack the chops to convert these Excel files to flat text files in the first place.  Anyone got any guidance?  I'd prefer not to sit in front of my computer for eight hours opening, saving-as, and closing.

 

[DavyDave53]

Create a program to walk the directory structure of Excel files.  Open each file and export the data to a database.

Then run the data through either a report server or an analytical engine.

A library like this may help, http://freenetexcel.codeplex.com/

But there are many ways to skin a cat...

 

[bagman67]

Thanks, DavyDave - I appreciate the insight.  If I were further upstream in the lifecycle of this data with access to a real programmer, your input would have been spot - on.


Regrettably, now it's just little ol' me - not a developer by even the most generous of definitions - and all I have are some newbie chops in awk and sed, and no VB experience at all.  So I can manually convert each file - but I'm not looking forward to it, especially since our deadline looms and it might take longer to figure out to program the desired result than to just hammer away at it.


But don't stop throwing out ideas - I'm all ears!


Thanks -


Bagman

 

[ocguy106]

I don't know the specifics of what you are doing but as long as you are able to convert to what you need without the use of VB specific functions couldn't you just record a macro that mimics your manual inputs from start to finish then you only need to hit a button and it would open convert save and close for you. that would be the simplest way to do it with out more information.

 

[stratamania]

Perhaps get an XML add in for excel. Then use it to take Excel to XML.


http://www.excel-easy.com/examples/xml.html

http://office.microsoft.com/en-gb/excel-help/create-an-xml-data-file-and-xml-schema-file-from-worksheet-data-HA010263509.aspx


Once you have XML then to HTML

http://www.w3schools.com/xml/xml_to_html.asp

No SED and AWK then. 

 

[Jumble Jumble]

OK, save this (all of it, note there's a scrollbar there) as XlsToCsv.VBS in the same folder as all the XLS files. Then just double click it.

Dim oFSO
Dim oExcel
Dim oFolder
Dim oBook
Dim sFullFilePath

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(".")

Set oExcel = CreateObject("Excel.Application")

For Each oFile in oFolder.Files
    If UCase(Right(oFile.Name,4)) = ".XLS" Then
        sFullFilePath = oFolder.Path & "\" & oFile.Name
        Set oBook = oExcel.Workbooks.Open(sFullFilePath)
        oBook.SaveAs Left(sFullFilePath,Len(sFullFilePath) - 3) & "csv", 6
        oBook.Close False
        Set oBook = Nothing
    End If
Next

oExcel.Quit

WScript.Echo "Done"

Hey presto, a bunch of csv files.

 

[stratamania]

An elegant solution JumbleJumble.    :icon_thumright:

Perhaps with that script and the one that Bagman67 already has, set to run from bat file each in turn it could be all automated.

 

[bagman67]

You guys are awesome.  Thank you.  I'll try the solutions above and see how I do; will report back either way.


IGB

 

[bagman67]

Jumble, holy crap, that's perfect.


I'm tremendously grateful.


I am proud to be part of this community yet again.


Bagman