grace101
asked on
Vbscript to concatenate excel files of different names?
Hello Experts,
I trying to concatentate the contents of all excel files in a certain folder into a resultant excel file
result.xls.
All files I want to concatenate to result.xls start with the letters logXXXX2008 where XXXX is the day and month. These are different in each file.
Can this be done in vbscript?
I trying to concatentate the contents of all excel files in a certain folder into a resultant excel file
result.xls.
All files I want to concatenate to result.xls start with the letters logXXXX2008 where XXXX is the day and month. These are different in each file.
Can this be done in vbscript?
This can indeed be done using vbs.
How's the contents of the files structured?
What's the intended structure of the final file?
(Do you simply wish to pack all sheets to a single workbook?)
http://www.activexperts.com/activmonitor/windowsmanagement/scripts/msoffice/excel/
http://www.gregthatcher.com/Papers/VBScript/ExcelExtractScript.aspx
How's the contents of the files structured?
What's the intended structure of the final file?
(Do you simply wish to pack all sheets to a single workbook?)
http://www.activexperts.com/activmonitor/windowsmanagement/scripts/msoffice/excel/
http://www.gregthatcher.com/Papers/VBScript/ExcelExtractScript.aspx
ASKER
Thank you for your responses.
I'm using vbs because ive sort of half used it before. (No expert though by a long shot!)
Ive attached an example of one of the files i want to process. There are multiple files in the same folder (as i said before with different timestamps) that i want to
concatenate together.
The data in the sample file is temperature data from 7 ovens. Im interested in Oven 3. (Column C of each spreadsheet)
I'd like to concatenate all values from column C of each spreadsheet file using a vbscript and place the result in a text file. Result .txt.
Is something like this possible?
Grace
Log11062008.xls
I'm using vbs because ive sort of half used it before. (No expert though by a long shot!)
Ive attached an example of one of the files i want to process. There are multiple files in the same folder (as i said before with different timestamps) that i want to
concatenate together.
The data in the sample file is temperature data from 7 ovens. Im interested in Oven 3. (Column C of each spreadsheet)
I'd like to concatenate all values from column C of each spreadsheet file using a vbscript and place the result in a text file. Result .txt.
Is something like this possible?
Grace
Log11062008.xls
Hi Grace,
Try this VBS file.
Just change this line to suit the path to your XLS files:
strFolder = "C:\TEMP\Temp\Test script\Excel"
and it should then create a new Results.xls file in the same folder as the VBS file.
Regards,
Rob.
Try this VBS file.
Just change this line to suit the path to your XLS files:
strFolder = "C:\TEMP\Temp\Test script\Excel"
and it should then create a new Results.xls file in the same folder as the VBS file.
Regards,
Rob.
strFolder = "C:\TEMP\Temp\Test script\Excel"
strResultFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Results.xls"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
objExcel.Visible = True
objExcel.ScreenUpdating = False
Set objResultWB = objExcel.Workbooks.Add
For Each objFile In objFSO.GetFolder(strFolder).Files
If Left(LCase(objFile.Name), 3) = LCase("Log") And Right(LCase(objFile.Name), 4) = LCase(".xls") And LCase(objFile.Name) <> LCase(strResultFile) Then
intRow = objResultWB.Sheets(1).Cells(65536, "C").End(xlUp).Row
If intRow > 1 Then intRow = intRow + 1
Set objSourceWB = objExcel.Workbooks.Open(objFile.Path, False, False)
objSourceWB.Sheets(1).Range("C1:C" & objSourceWB.Sheets(1).Cells(65536, "C").End(xlUp).Row).Copy objResultWB.Sheets(1).Range("C" & intRow)
objSourceWB.Close False
End If
Next
objExcel.DisplayAlerts = False
objResultWB.SaveAs strResultFile, True
objExcel.DisplayAlerts = True
objResultWB.Close
objExcel.ScreenUpdating = True
objExcel.Quit
ASKER
Thanks very much Rob for posting that:-)
Can you show me how to modify it so that it ll concatenate files starting with log and ending in a variable number of (characters/numbers etc..) for future reference?
I have other files that id like to concatenate that dont all necessarily include the timestamp "XXXX2008" that id like to use this script on in the future:-) (Ive already spent 2 hours attempting this modification but have failed miserably!)
Thanks, Grace
Can you show me how to modify it so that it ll concatenate files starting with log and ending in a variable number of (characters/numbers etc..) for future reference?
I have other files that id like to concatenate that dont all necessarily include the timestamp "XXXX2008" that id like to use this script on in the future:-) (Ive already spent 2 hours attempting this modification but have failed miserably!)
Thanks, Grace
Hi Grace,
So far, the only line that does any validation of the file name itself is this line:
If Left(LCase(objFile.Name), 3) = LCase("Log") And Right(LCase(objFile.Name), 4) = LCase(".xls") And LCase(objFile.Name) <> LCase(strResultFile) Then
Which will pick up any file that start with "log" (by the Left function), and ends with ".xls" (by the Right function). Currently it does not validate anything else in the middle, so it should pick up
LogXXXXXXXX.xls
LogANYTEXT.xls
Log.xls
Does that not work for you?
Regards,
Rob.
So far, the only line that does any validation of the file name itself is this line:
If Left(LCase(objFile.Name), 3) = LCase("Log") And Right(LCase(objFile.Name),
Which will pick up any file that start with "log" (by the Left function), and ends with ".xls" (by the Right function). Currently it does not validate anything else in the middle, so it should pick up
LogXXXXXXXX.xls
LogANYTEXT.xls
Log.xls
Does that not work for you?
Regards,
Rob.
ASKER
Sorry for the delay Rob,
Ive finally figured out those other files I wish to concatenate are comma seperated value files. Can your script be modified to handle those too please?
Thanks very much for your patience:-)
Grace
Ive finally figured out those other files I wish to concatenate are comma seperated value files. Can your script be modified to handle those too please?
Thanks very much for your patience:-)
Grace
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did the job. Thanks very much Rob.
That script will come in very handy:-)
That script will come in very handy:-)
No problem. Thanks for the grade.
Enjoy.
Rob.
Enjoy.
Rob.
Do your excel files have more than one sheet?
Do you want to end up with many worksheets or just one with all the data?