Apply macro to closed workbooks

  • Thread starter Thread starter Belinda & Jamie
  • Start date Start date
B

Belinda & Jamie

Is there any way that I can apply a recorded macro to a selection of Excel
files, without having to open each one?
ie. I have created a macro to copy particular cells to a new worksheet and I
want to copy the same cells from about 240 spreadsheets onto a new sheet,
but don't want to have to open each one up...

Thanks in advance.
Belinda
 
AFAIK there isn't a way that is as simple as you would probably like.
However it's possible to write a macro to open & close all the workbooks
within a directory. With this your only remaining step would be to apply
the macro you have just recorded to the newly opened workbook before closing
it and going onto the next.

Here's a snippet of code from another post which shows you how to process
the files in a directory. It should give you a head start on this.


Option Explicit


Public Sub ReadExcelFiles(FolderName As String)
Dim FileName As String

' Add trailing \ character if necessary
'
If Right(FolderName, 1) <> "\" Then FolderName = FolderName & "\"

FileName = Dir(FolderName & "*.xls")

Do While FileName <> ""
Workbooks.Open (FolderName & FileName)

' Do whatever workbook manipulation here

Workbooks(FileName).Close
FileName = Dir()
Loop
End Sub

Public Sub test()
ReadExcelFiles ("c:\temp\test")
End Sub


--

Regards,


Bill Lunney
www.billlunney.com
 
If you don't like macros, there is another option if you have some kind of list of your workbook
names anywhere, or they are all the same name with a different number that is easy to create a
list with, Book1, Book2, Book3 etc.

You can create a full filepath to a closed workbook and it will suck out the data you need. You
cannot use INDIRECT to do this on a closd workbook, but assuming you had a list of your workbooks
in one column, and you knew the full filepath, you could have the filepath in one column, the
workbook in another and the cell reference in another. Simply use =A1&B1&C1 with any tweaks
necessary to create the full reference, then when you have done that, select all of the column
with the formulas, and do edit / copy, then edit paste special / values.

EXAMPLE assuming you wanted data out of cell A1 on sheet 1 in workbooks Test1, Test2, Test3,
Test4,xls etc

In a new blank book

A1 ''C:\4 Temp\Test\[ (Note the quotes at the beginning are 2 single quotes)
B1 Test
C1 1
D1 .xls]Sheet1'!$A$1

E1 ="="&A1&B1&C1&D1

Fill down the data from C1, so that you get 2,3,4,5,6 etc in the rows below. Copy the other
columns down, then select all of column E, do edit / copy then edit paste special values and you
will have created the filepaths, and the data should now pour in from the other books.
 
If the workbook is being changed, then the close command should set
savechanges to false to prevent a dialog

Workbooks(FileName).Close Savechanges:=False

If you want to save the changed workbooks, then you should not use this code
as structured. You need to collect the names in an array, then in a
separate loop, loop through the array and process the workbooks.

Regards,
Tom Ogilvy
 
Thanks for the info.
I used Ken's advice at it was more straightforward (for me), however the
data didn't 'pour in from other books'!

So I now have a spreadsheet with about 500 spreadsheets' full pathnames
in the form ='c:temp\[book4.xls]Sheet1'!$A$1 - this info taken from
other columns, so the formula for the cell is ="="&A4&b4&c4&d4

It seems to look as expected but the actual value cell A1 of book4.xls
is not coming through anywhere..

I'm stuck. Can anybody help me further?
Thanks a lot.
Belinda
 
Back
Top