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

Open all files in folder automatically using VBA
 

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



wrote in message
ups.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

Open all files in folder automatically using VBA
 

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

Open all files in folder automatically using VBA
 

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

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

Open all files in folder automatically using VBA
 

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



"Dave Peterson" wrote in message
...
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

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