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,

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+ ?

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+ ?
>
>

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+ ?
>>
>>

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.

I stand impressed.

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+ ?
> >>
> >>
>

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+ ?
>>>>
>>>>
>>