Friday, January 25, 2008

Collecting Data from Multiple Excel Workbooks into a Single Text File using VBScript

Introduction: It’s often a very tedious ask to collect data through copy and pate from multiple MS Excel files. There are ways in VBA to automate the process, but unfortunately, some time ill spreadsheet practices like uses of merged cell may trigger cumbersome situations. Using Vbscript and DOS bat file we can automate this process in just few seconds.

Before we start with the cods, I am assuming that all the Excel files are in same folder as in my case it’s in “C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\Xl
Now, we need to create one more folder for storing CSV files in example below the path is “C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\CSV\”. You need to change both these paths per your requirement.

Step One: Open Notepad paste the codes as below; make sure you change the CSV path per your requirement.






cd C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\CSV
copy *.xls all.txt
del *.xls



Step Two: Save this file in C drive as ConsolidateCSV.bat

Step Three: Open Notepad again paste codes as below; make sure you replace the CSV and Xl path with the folder which you have created to store the CSV file and Folder where your MS Excel files are located.







strPath = "C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\Xl"
'Path of Excel File, you may use standard or special folder names as per your need
csvpath = "C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\CSV\"
'Path of CSV File
Set objExcel = CreateObject("Excel.Application")
'Ceating a MS Excel Object instance
Set WSHShell = CreateObject("Wscript.Shell")
'Ceating a Windows Shell object to run the bat file
objExcel.Visible = False
'Better to keep Excel hidden as it might eat up some more memory
objExcel.DisplayAlerts = False
'switch off the Display Alerts in Excel
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files
'Looping through all the Excel files in folder
If objFso.GetExtensionName (objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
objWorkbook.SaveAs csvpath & objFile.name, 6, True o
bjWorkbook.Close
'MsgBox objFile.name & " Saved", 64,"Example"
'You may uncheck this option if you want to notify the user after each file is saved
End If
Next
objExcel.DisplayAlerts = True
'Switch on the Excel Display Alerts
WSHShell.Run ("C:\ConsolidateCSV.bat")
' Run the ConsolidateCSV.bat file
Set objExcel = Nothing
Set WSHShell = Nothing
'Free the ojects
MsgBox "All.txt is created!",64,"Example"
'Noify the user about all.txt file that has all the data




Step Four: Save the File with .vbs extension.

If you have performed every step appropriately, double clicking the .VBS file run a script to consolidate data from first worksheet of all workbooks into a text file with name all.txt.

Ps: In you have comments or a better way of doing it please post it.

1 comment:

  1. how can i drop down ?
    sheet 1 create words sheet two and activate. without using macro

    ReplyDelete