# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Open all files in folder automatically using VBA

## cfatz1@gmail.com

Hi-

I have a folder with multiple excel files in it and currently have a
macro that goes through each file and pulls certain data into Access.
The problem I have is that this folder is dynamic.  The names and the
number of files varies from month to month.  The way my macro works now
is it opens a specific file....runs the macro....and then closes the
file.  I have about 60 individual instructions for each of the files.
Open specific file.....call Macro....close file.....open next
file....call Macro.....close file.....etc.

My question is this:  Is there a way...using VBA...to point to a
specific folder and run a macro for EVERY file in that folder??  I
don't want to tell Excel what file to open....I just want it to open
every file in the folder....run this macro against each file....and
then close the files.

Anyway this can be done or do I have to be specific in what I tell
Excel to open??

Thanks in advance!!

-Chris

----------


## William

Hi

This may help...

Sub openAllfilesInALocation()
Dim i as integer, wb as workbook
With Application.FileSearch
..NewSearch
..LookIn = "C:\MyFolder\MySubFolder"
..SearchSubFolders = False
..FileName = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(FileName:=.FoundFiles(i))
'Perform the operation on the open workbook
wb.Worksheets("sheet1").range("A1")=Date
'Save and close the workbook
wb.save
wb.Close
'On to the next workbook
Next i
End With
End Sub



XL2003
Regards

William
willwest22@yahoo.com


<cfatz1@gmail.com> wrote in message
news:1125626562.529116.20300@f14g2000cwb.googlegroups.com...
> Hi-
>
> I have a folder with multiple excel files in it and currently have a
> macro that goes through each file and pulls certain data into Access.
> The problem I have is that this folder is dynamic.  The names and the
> number of files varies from month to month.  The way my macro works now
> is it opens a specific file....runs the macro....and then closes the
> file.  I have about 60 individual instructions for each of the files.
> Open specific file.....call Macro....close file.....open next
> file....call Macro.....close file.....etc.
>
> My question is this:  Is there a way...using VBA...to point to a
> specific folder and run a macro for EVERY file in that folder??  I
> don't want to tell Excel what file to open....I just want it to open
> every file in the folder....run this macro against each file....and
> then close the files.
>
> Anyway this can be done or do I have to be specific in what I tell
> Excel to open??
>
> Thanks in advance!!
>
> -Chris
>

----------


## cfatz1@gmail.com

Thanks for the help William....I appreciate it!

I do have one problem  with the code.  I am getting a "Run-time error
'9':  Subscript out of range" error when I try to run this code.  Here
is the code:

Sub openfilesInALocation()
Dim i As Integer, wb As Workbook
With Application.FileSearch
..NewSearch
..LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest"
..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'Perform the operation on the open workbook

----------->  Errors on the following line:

wb.Worksheets("sheet1").Range("A1").Select

'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub

If anyone can solve this for me I would appreciate the help!

-Chris

----------


## Dave Peterson

Maybe you don't have a worksheet named "sheet1" for each of those workbooks.

If you wanted the leftmost worksheet, maybe you could use:
wb.Worksheets(1).Range("A1").Select

cfatz1@gmail.com wrote:
>
> Thanks for the help William....I appreciate it!
>
> I do have one problem  with the code.  I am getting a "Run-time error
> '9':  Subscript out of range" error when I try to run this code.  Here
> is the code:
>
> Sub openfilesInALocation()
> Dim i As Integer, wb As Workbook
> With Application.FileSearch
> .NewSearch
> .LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest"
> .SearchSubFolders = False
> .Filename = "*.xls"
> .Execute
> For i = 1 To .FoundFiles.Count
> 'Open each workbook
> Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
> 'Perform the operation on the open workbook
>
> ----------->  Errors on the following line:
>
> wb.Worksheets("sheet1").Range("A1").Select
>
> 'Save and close the workbook
> wb.Save
> wb.Close
> 'On to the next workbook
> Next i
> End With
> End Sub
>
> If anyone can solve this for me I would appreciate the help!
>
> -Chris

--

Dave Peterson

----------


## William

Just to add to Dave's point....

If you want to actually select a cell within the workbook opened (which is
unnecessary 99% of the time), you should select the worksheet first and then
the cell, so....

---------------
wb.Worksheets(1).Select
wb.Worksheets(1).Range("A1").Select
---------------


--
XL2003
Regards

William
willwest22@yahoo.com


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43185714.E32D0AF7@verizonXSPAM.net...
> Maybe you don't have a worksheet named "sheet1" for each of those
> workbooks.
>
> If you wanted the leftmost worksheet, maybe you could use:
> wb.Worksheets(1).Range("A1").Select
>
> cfatz1@gmail.com wrote:
>>
>> Thanks for the help William....I appreciate it!
>>
>> I do have one problem  with the code.  I am getting a "Run-time error
>> '9':  Subscript out of range" error when I try to run this code.  Here
>> is the code:
>>
>> Sub openfilesInALocation()
>> Dim i As Integer, wb As Workbook
>> With Application.FileSearch
>> .NewSearch
>> .LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest"
>> .SearchSubFolders = False
>> .Filename = "*.xls"
>> .Execute
>> For i = 1 To .FoundFiles.Count
>> 'Open each workbook
>> Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
>> 'Perform the operation on the open workbook
>>
>> ----------->  Errors on the following line:
>>
>> wb.Worksheets("sheet1").Range("A1").Select
>>
>> 'Save and close the workbook
>> wb.Save
>> wb.Close
>> 'On to the next workbook
>> Next i
>> End With
>> End Sub
>>
>> If anyone can solve this for me I would appreciate the help!
>>
>> -Chris
>
> --
>
> Dave Peterson

----------


## umarpak123

I am having a problem.

The problem is a simple one but I cannot seem to figure it out.

I have around 30 .xls, .xlsx and .xlsm files.

I need to fetch data from their "specific cells" that are scattered around in the 30 sheets but their formating is the same e.g. if cell of "Date of Financials" field is J7. This J7 is constant for all the 30 files. Similarly, other fields are also constant.

I need to fetch the data in a summary.xlsm file in a table form which is present in the same folder as these files. I need to make a table of data e.g.

Serial Number - Name of Client - Date of Financials - Current Ratio
1                    ABC Company     31-12-2012           1.1
2                    DEF Company     30-06-2012           1.5
3                    HIJ  Company     31-12-2012           1.8

Can anyone help me out?

----------


## Fotis1991

* umarpak123
*

Welcome to the Forum.

Unfortunately you have to start your own thread for this.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## umarpak123

Thanks.. I have made a new thread.. :Smilie:

----------

