Re: combining excel worksheets

On Sep 18, 12:24 pm, T_Condit <TCon...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
This should help you out Glenn. Watch word wrap of course. This can be
modified to attach to other computers and drives if needed. Right now it
looks for xls files under c:\scripts\excel(\\Scripts\\Excel\\). Good luck.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

'Location of xls files
Set colFiles = objWMIService. _
ExecQuery("Select * from CIM_DataFile where Path = '\\Scripts\\Excel\\'
and Extension = 'xls'")

Set objExcel = CreateObject("Excel.Application")
Set objNewWorkbook = objExcel.Workbooks.Add

i = 1
For Each objFile in colFiles
Wscript.Echo objFile.FileName
Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
objExcel.Visible = True

Set objRange = objExcel.Range("B3", "B11")'Range of data you want to capture
objRange.Copy
objWorkbook.Close

objNewWorkbook.activate
objExcel.Cells(1,i).Select
objExcel.ActiveCell = objFile.FileName 'Name offiledata was copied from
objExcel.Cells(2,i).Select
objExcel.Active***.paste
i = i + 1

Next

objExcel.Cells(1,1).Select



"Paul Randall" wrote:

"Glenn" <nos...@xxxxxxxxx> wrote in message
news:ekEJQMX%23HHA.4584@xxxxxxxxxxxxxxxxxxxxxxx
I have a folder that contains severalexcelworkbooks.

Each of the workbooks contains a work*** that has similiar "fields" of
data (in the same columns) as the other workbooks, but different data.

Is there a way to run a script that will create a new workbook / ***
that reads all the data from the rest of them and makesonecombined
***?

Thanks.

You could create a VBA script that does what you want.
The newsgroup microsoft.public.excel.programming (or something like that)
might help you with VBA, and you may find the VBA help you need here too.

This is a VBScript group, so I'm assuming you want a VBScript-only solution.
A recent post demonstrated how to use VBS and ADO (builtintomost recent
Windows OSs) to use SQL statements to extract info from an access database,
putting the resultintoa .CSVfile, then uses SQL to copy the info from the
..CSVfileto a number ofsheetsin a workbook (XLSfile) that the script
creates. You can find this VBScript by searching groups.google.com for
everything on the following line:
ekkehard nordwind group:*.scripting.vbscript
Hopefully someone will post a snippet that demonstrates extracting info from
a *** in a workbook, and then you will have all the pieces to do what you
want.

-Paul Randall- Hide quoted text -

- Show quoted text -

I have an excel workbook that contains MANY identical worksheets/tabs
that I would like to combine into one .csv file (because its too many
rows for .xls) using a macro of some sort. Along with that i would
like it to remove the header row from the 2nd work***/tab going
forward, so that in the end I have one .csv file with a header row and
all the subseqent combined data. any ideas?

.