run vba code on more than one xl file

[Peter]

Is there a way to run a VBA function on more than one
Excel file at the same time? Preferably without having to
open each file. any direction would be greatly
appreciated. thank you.
peter

 

[Bob Phillips]

If by 'at the same time' you mean issuing the same command to multiple
workbooks at the same time, no. You could Process each one sequentially, and
merge the results.

To access closed workbooks, you could use ADO and run a SQL query.

To give more help, we would need to know what you are trying to do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[peter]

I would like to know how to do that. The function
basically takes info on one sheet and puts it onto another
sheet in a different desired format. Would there be a way
to write code to make it Open "1.xls", "Run
formatting", "close 1.xls". Open "2.xls", "Run
formatting", "Close "2.xls.", etc. your help is
appreciated. thank you. peter

 

[Bob Phillips]

In simple terms it is this

sFile = "C:\myFiles\1.xls"
Workbooks.Open Filename:=sFile
FormatFile1
Workboks("1.xls").Close

sFile = "C:\myFiles\2.xls"
Workbooks.Open Filename:=sFile
FormatFile2
Workboks("2.xls").Close

sFile = "C:\myFiles\3.xls"
Workbooks.Open Filename:=sFile
FormatFile3
Workboks("3.xls").Close

WhereFormat1,2,3 will fromat the 3 files. I anticipate that you want to copy
them to another book.

Is that okay, or do you need more?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[peter]

I would like to copy them into a clean worksheet. thanks
again.

 

[Bob Phillips]

Like This?

thisWB = Activeworkbook

sFile = "C:\myFiles\1.xls"
Workbooks.Open Filename:=sFile
Activesheet.Copy After:= thisWB.Worksheets(thisWB.Worksheets.Count)
Workbooks("1.xls").Close

sFile = "C:\myFiles\2.xls"
Workbooks.Open Filename:=sFile
Activesheet.Copy After:= thisWB.Worksheets(thisWB.Worksheets.Count)
Workbooks("2.xls").Close

sFile = "C:\myFiles\3.xls"
Workbooks.Open Filename:=sFile
Activesheet.Copy After:= thisWB.Worksheets(thisWB.Worksheets.Count)
Workbooks("3.xls").Close

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)