Solved

Vbscript to concatenate excel files of different names?

Posted on 2008-06-11
10
1,660 Views
Last Modified: 2011-10-19
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?
0
Comment
Question by:grace101
10 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 21762768
does it have to be vbs? Could it be vba?

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?
0
 
LVL 8

Expert Comment

by:Thejaka
ID: 21766195
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
0
 

Author Comment

by:grace101
ID: 21769414
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
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21790439
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.
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

Open in new window

0
 

Author Comment

by:grace101
ID: 21793799
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 65

Expert Comment

by:RobSampson
ID: 21797576
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.
0
 

Author Comment

by:grace101
ID: 21801288
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
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 21809878
See if it works as is, with CSV files, by changing 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


to this
If Left(LCase(objFile.Name), 3) = LCase("Log") And (Right(LCase(objFile.Name), 4) = LCase(".xls") Or Right(LCase(objFile.Name), 4) = LCase(".csv"))And LCase(objFile.Name) <> LCase(strResultFile) Then


Regards,

Rob.
0
 

Author Closing Comment

by:grace101
ID: 31466205
That did the job. Thanks very much Rob.

That script will come in very handy:-)
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21818008
No problem.  Thanks for the grade.

Enjoy.

Rob.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now