Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
Hi:
I am trying to create a macro that will select all files within a folder to open them. The folder name will change as will the number of files within each folder. I need to open 800+ files then run another macro once they are open. Any suggestions on how I can select the folder I want (it's ok if I have to manually change folder names), then have the macro continue and select all files within the folder to open them? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
I can't give you the exact code you need, but it would be a loop with a
similar command to: Workbooks.Open FileName:= _ "C:\Documents and Settings\USER\My Documents\theName.xls" where theName is a filename. However; Opening over 800 files simultaneously will surely exceed your computer's memory spec, if not crash before you reach it. Did you mean open and close file 1 then open and close file 2 then open and close file 3... then file 800+ ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
No sorry.....
in one folder there may be 20 files. I need to open these files run another macro to automatically insert some calculations save and close each as the macro runs. Then I want to move to another folder (separately) which may contain 25 files and do the same thing. "JakeyC" wrote: I can't give you the exact code you need, but it would be a loop with a similar command to: Workbooks.Open FileName:= _ "C:\Documents and Settings\USER\My Documents\theName.xls" where theName is a filename. However; Opening over 800 files simultaneously will surely exceed your computer's memory spec, if not crash before you reach it. Did you mean open and close file 1 then open and close file 2 then open and close file 3... then file 800+ ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
Hi Theresa
This will loop through each excel file in a folder. The folder is hardcoded but you could prompt for the folder using Application.GetOpenFileName (see VBA help) or just an InputBox. Sub OpnFiles() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder("c:\Temp") 'change directory For Each objFile In objFolder.Files If objFile.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=objFolder.path & "\" & objFile.Name 'do whatever Workbooks(objFile.Name).Close True 'saves changes End If Next End Sub Hope this helps Rowan Theresa wrote: No sorry..... in one folder there may be 20 files. I need to open these files run another macro to automatically insert some calculations save and close each as the macro runs. Then I want to move to another folder (separately) which may contain 25 files and do the same thing. "JakeyC" wrote: I can't give you the exact code you need, but it would be a loop with a similar command to: Workbooks.Open FileName:= _ "C:\Documents and Settings\USER\My Documents\theName.xls" where theName is a filename. However; Opening over 800 files simultaneously will surely exceed your computer's memory spec, if not crash before you reach it. Did you mean open and close file 1 then open and close file 2 then open and close file 3... then file 800+ ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
OK! I just tried opening 800 blank workbooks on my not-so-high-spec
machine and got stuck at 313! If your folders and files are named sequentially or in a pattern, use a loop to cycle through them eg. ThisFolder1\MyFile1 ThisFolder1\MyFile2 ThisFolder2\MyFile1 ThisFolder2\MyFile2 etc. Else you'll literally have to say 'Open the file with name blahblahblah then dostuffwithit' for each individual file. That is as far as I know. If any clever people know code for 'open the first file in a folder, then the next' I'd be impressed to see it. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
I stand impressed.
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
Works great!
Thanks "Rowan Drummond" wrote: Hi Theresa This will loop through each excel file in a folder. The folder is hardcoded but you could prompt for the folder using Application.GetOpenFileName (see VBA help) or just an InputBox. Sub OpnFiles() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder("c:\Temp") 'change directory For Each objFile In objFolder.Files If objFile.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=objFolder.path & "\" & objFile.Name 'do whatever Workbooks(objFile.Name).Close True 'saves changes End If Next End Sub Hope this helps Rowan Theresa wrote: No sorry..... in one folder there may be 20 files. I need to open these files run another macro to automatically insert some calculations save and close each as the macro runs. Then I want to move to another folder (separately) which may contain 25 files and do the same thing. "JakeyC" wrote: I can't give you the exact code you need, but it would be a loop with a similar command to: Workbooks.Open FileName:= _ "C:\Documents and Settings\USER\My Documents\theName.xls" where theName is a filename. However; Opening over 800 files simultaneously will surely exceed your computer's memory spec, if not crash before you reach it. Did you mean open and close file 1 then open and close file 2 then open and close file 3... then file 800+ ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro to Select All Files Within A Folder
You're welcome!
Theresa wrote: Works great! Thanks "Rowan Drummond" wrote: Hi Theresa This will loop through each excel file in a folder. The folder is hardcoded but you could prompt for the folder using Application.GetOpenFileName (see VBA help) or just an InputBox. Sub OpnFiles() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder("c:\Temp") 'change directory For Each objFile In objFolder.Files If objFile.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=objFolder.path & "\" & objFile.Name 'do whatever Workbooks(objFile.Name).Close True 'saves changes End If Next End Sub Hope this helps Rowan Theresa wrote: No sorry..... in one folder there may be 20 files. I need to open these files run another macro to automatically insert some calculations save and close each as the macro runs. Then I want to move to another folder (separately) which may contain 25 files and do the same thing. "JakeyC" wrote: I can't give you the exact code you need, but it would be a loop with a similar command to: Workbooks.Open FileName:= _ "C:\Documents and Settings\USER\My Documents\theName.xls" where theName is a filename. However; Opening over 800 files simultaneously will surely exceed your computer's memory spec, if not crash before you reach it. Did you mean open and close file 1 then open and close file 2 then open and close file 3... then file 800+ ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to decide folder-depth or How to select more folders/subfolders (folder-tree) ? | Excel Discussion (Misc queries) | |||
Excel macro to open files within a folder | Excel Programming | |||
"Folder Select" Dialogue - Opening multiple files from selected folder | Excel Programming | |||
Help - applying macro to all files in folder | Excel Programming | |||
Macro to Print All Files in a Folder | Excel Programming |