Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply macro to closed workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply macro to closed workbooks
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 "Belinda & Jamie" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply macro to closed workbooks
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ---------------------------------------------------------------------------- "Bill Lunney" wrote in message ... 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 "Belinda & Jamie" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply macro to closed workbooks
Or you could use the code i submitted earlier.
Regards, Scott "Belinda & Jamie" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply macro to closed workbooks
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 Bill Lunney wrote in message ... 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 "Belinda & Jamie" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply macro to closed workbooks
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Data from Closed Workbooks | Excel Worksheet Functions | |||
Duplicate Macro for Closed Workbooks | Excel Worksheet Functions | |||
Copying From Closed Workbooks | Excel Worksheet Functions | |||
Information from Closed workbooks | Excel Discussion (Misc queries) | |||
Macro for Pivot Table - Apply to multiple workbooks | Excel Programming |