Building CSV's from Multiple Workbooks

[Store300]

I am trying to build a csv from 150 seperate excel workbooks (all identically formatted with an extraction range). Does anyone know of anyway to do this without actually opening the workbooks in question as its the slowest part of the process? I'm currently using a loop to enter a link into my 'roll-up' workbook and then printing the link's result to a csv but its messy.....help?

 

[Ruairi]

Here is some code i used to save an excel file to a csv file. It accepted input and output filenames from the command line and saved the excel file. You could Modify this code slightly to loop through the files (close each file but not excel) and keep a string with all the filenames in the form

'before the loop 
FileStr = "copy "
'Inside the loop 
FileStr = FileStr & "CurrentFileName + " 
'After all files looped through 
FileStr = FileStr & " FinalCSVFileName"

then Shell out to this string to append all the files.
I think it would be cleaner than using links to the other worksheets, and might speed up the process but i'm not making any guarantees on that. Just another option

Private Sub Form_Load()

Dim ObjXL As Excel.Application
Dim CmdArgs()

CmdArgs() = GetCommandLine 'Split the command line into 2 arguments the first is the
'excel file to convert, the second is the name to save the CSV file as. Store these 2
'filenames in the array CmdArgs

CSVDir = Mid(CmdArgs(2), 1, InStr(CmdArgs(2), "\"))

On Error Resume Next
Kill CmdArgs(2) 'if the CSV file already exists delete it so excel can save to that
'filename without prompting the user
Kill CSVDir & "convert"
On Error GoTo 0

Set ObjXL = CreateObject("excel.application") 'create a new instance of excel in
'memory to handle the conversion. this is completely seperate from any already running
'instances of excel, and the opening and conversion is done in the background.

ObjXL.Interactive = False    ' This line tells excel to ignore user input. this is
'not really nessecary because we created an 'invisible' instance of excel, but it is
'always a good idea when controlling excel progromatically
ObjXL.DisplayAlerts = False  'This line tells excel not to ask whether to overwrite
'files, change from excel format to csv, etc..

ObjXL.Workbooks.Open FileName:=CmdArgs(1) 'Open the file that is specified in the first
'command line argument

ObjXL.ActiveWorkbook.Saved = True ' "trick" excel into thinking the original excel file
'has been saved. again not really necessary since excel will not be prompting for user
'input, but always a good idea if you have no changes to save to the excel file

ObjXL.ActiveSheet.SaveAs FileName:=CmdArgs(2), FileFormat:=xlCSV, CreateBackup:=False
'Saves the excel file as a CSV file with the name specified in the second argument
'the FileFormat:=xlCSV is the important part that does the conversion

ObjXL.Workbooks.Close 'Close the file
                          
ObjXL.Quit 'Close the instance of excel in memory

ObjXL.Interactive = True   'Let Excel accept user input again
ObjXL.DisplayAlerts = True 'Let excel prompt before saving, closing, etc.

Set ObjXL = Nothing 'Release the refrence to excel, this allows the instance of excel
'to be cleared from memory

Unload Me 'Unload the invisible XLToCSV form from memory, allows the program to terminate
End Sub

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com