Open all files in folder automatically using VBA

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

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
[email protected]


<[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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

[email protected] 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

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
[email protected]


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> [email protected] 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

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?

Thanks.. I have made a new thread..