Member_2_6492660_1
asked on
How to merge CSV files into spreadsheet via batch
Using Excel 2010
I have an application that creates several .csv files in the same folder.
I would like to combine all the .csv files into one master spreadsheet.
I used copy d:\inventory\*.csv n:\inventory\master.csv
That works but it is not what I want.
That places all the .csv files into one sheet.
I would like to create and individual sheet for each file
d:\inventory\test1.csv
d:\inventory\test2.csv
d:\inventory\test3.csv
the would create a spreadsheet
sheet1 sheet2 sheet3
Thanks in advance for your help
I have an application that creates several .csv files in the same folder.
I would like to combine all the .csv files into one master spreadsheet.
I used copy d:\inventory\*.csv n:\inventory\master.csv
That works but it is not what I want.
That places all the .csv files into one sheet.
I would like to create and individual sheet for each file
d:\inventory\test1.csv
d:\inventory\test2.csv
d:\inventory\test3.csv
the would create a spreadsheet
sheet1 sheet2 sheet3
Thanks in advance for your help
ASKER
Thanks for the fast response
I ran it but getting this error
C:\test>cscript.exe csvmerge.vbs *.csv
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
C:\test\csvmerge.vbs(7, 40) Microsoft VBScript compilation error: Expected ')'
Option Explicit
Sub Main()
Dim fso
Dim CurrentFolder
Set fso = CreateObject("Scripting.Fi leSystemOb ject")
Set CurrentFolder = fso.GetFolder(c:\test)
Dim NewWb As Workbook
Set NewWb = Workbooks.Add
ProcessFolder CurrentFolder, NewWb
NewWb.SaveAs CurrentFolder & "\test.xlsx"
NewWb.Close
End Sub
Sub ProcessFolder(ByVal Folder, NewWb As Workbook)
Dim Files
Set Files = Folder.Files
Dim FromWB As Workbook
Dim File
For Each File In Files
If File.Name Like "*.csv" Then
Set FromWB = Workbooks.Open(File)
FromWB.Sheets(1).Copy Before:=NewWb.Sheets(1)
FromWB.Close
End If
Next
'REMOVE THE LINES BELOW HERE TO REMOVE SUBFOLDERS
Dim SubFolders: Set SubFolders = Folder.SubFolders
Dim SubFolder
For Each SubFolder In SubFolders
ProcessFolder SubFolder, NewWb
Next
'REMOVE THE LINES ABOVE HERE TO REMOVE SUBFOLDERS
End Sub
What did I do wrong with this line?
Set CurrentFolder = fso.GetFolder(c:\test)
c:\test is the directory where the .csv files are located
I ran it but getting this error
C:\test>cscript.exe csvmerge.vbs *.csv
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
C:\test\csvmerge.vbs(7, 40) Microsoft VBScript compilation error: Expected ')'
Option Explicit
Sub Main()
Dim fso
Dim CurrentFolder
Set fso = CreateObject("Scripting.Fi
Set CurrentFolder = fso.GetFolder(c:\test)
Dim NewWb As Workbook
Set NewWb = Workbooks.Add
ProcessFolder CurrentFolder, NewWb
NewWb.SaveAs CurrentFolder & "\test.xlsx"
NewWb.Close
End Sub
Sub ProcessFolder(ByVal Folder, NewWb As Workbook)
Dim Files
Set Files = Folder.Files
Dim FromWB As Workbook
Dim File
For Each File In Files
If File.Name Like "*.csv" Then
Set FromWB = Workbooks.Open(File)
FromWB.Sheets(1).Copy Before:=NewWb.Sheets(1)
FromWB.Close
End If
Next
'REMOVE THE LINES BELOW HERE TO REMOVE SUBFOLDERS
Dim SubFolders: Set SubFolders = Folder.SubFolders
Dim SubFolder
For Each SubFolder In SubFolders
ProcessFolder SubFolder, NewWb
Next
'REMOVE THE LINES ABOVE HERE TO REMOVE SUBFOLDERS
End Sub
What did I do wrong with this line?
Set CurrentFolder = fso.GetFolder(c:\test)
c:\test is the directory where the .csv files are located
Quotation marks will be required for folder name...
You are running the macro from Excel?
It is not currently in a format to be run as a VBS.
To run as vbs will need a few tweaks to add the excel objects.
Set CurrentFolder = fso.GetFolder("c:\test")
You are running the macro from Excel?
It is not currently in a format to be run as a VBS.
To run as vbs will need a few tweaks to add the excel objects.
ASKER
Thanks
VBS is not my specialty
I need to run this in batch not from within excel
I did add the quotes the error I get now is
C:\test>cscript.exe csvmerge.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
C:\test\csvmerge.vbs(9, 15) Microsoft VBScript compilation error: Expected end o
f statement
VBS is not my specialty
I need to run this in batch not from within excel
I did add the quotes the error I get now is
C:\test>cscript.exe csvmerge.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
C:\test\csvmerge.vbs(9, 15) Microsoft VBScript compilation error: Expected end o
f statement
I will look to change the code to run as a script (vbs) rather than Excel macro (VBA).
ASKER
should I post another question and close this one?
No need to post another question, you are still to get the answer you require.
I am working on the vbs atm, should only be about 30 min.
Does the script need to work on sub-folders?
I am working on the vbs atm, should only be about 30 min.
Does the script need to work on sub-folders?
ASKER
No the directory structure is this
all .csv files are in E:\inventory
the master.csv output file will be placed in N:\Inventory
Thanks
all .csv files are in E:\inventory
the master.csv output file will be placed in N:\Inventory
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Barman
Works great thanks a million.
Works great thanks a million.
happy to help.
ATB
Steve.
ATB
Steve.
ASKER
I finally got a chance to run this on my Widows 2003 server
Had to install Excell 2010 first.
I am getting this error
csvmerge.vbs(23,17) Microsoft Excel Cannot rename a sheet to the same name as another sheet a referenced object library or a workbook referenced by visual basic
Had to install Excell 2010 first.
I am getting this error
csvmerge.vbs(23,17) Microsoft Excel Cannot rename a sheet to the same name as another sheet a referenced object library or a workbook referenced by visual basic
Run the file from the location the text files are in
Open in new window
Rename-test.xlsm