Currently I have a mcro that opens up one workbook: -

Sub Macro1()
Workbooks.Open Filename:="C:\Data\Book2.xls"
End Sub

Can I make this Macro open all .xls files in the current Directory: -
Like

Sub Macro1()
Workbooks.Open Filename:="C:\Data\*.xls"
End Sub

Any ideas

--
Message posted from http://www.ExcelForum.com

Sub Macro1()
Dim sName as String
sName = Dir("C:\Data\*.xls")
do while sName < ""
Workbooks.Open Filename:=sName
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy


"ianripping " wrote in message
...
Currently I have a mcro that opens up one workbook: -

Sub Macro1()
Workbooks.Open Filename:="C:\Data\Book2.xls"
End Sub

Can I make this Macro open all .xls files in the current Directory: -
Like

Sub Macro1()
Workbooks.Open Filename:="C:\Data\*.xls"
End Sub

Any ideas?


---
Message posted from http://www.ExcelForum.com/

Tom & Ianripping,

This is just the code I've been looking for. It works great! I can add
wildcards to suit my macros, but I would like some more help with 2 problems
that are similar to the first.

My first macro is used to open about 50 *A*.csv files, formats and saves
each file as *A*.xls. Then it opens 50 *B*.csv files, and processes them
the same way. My problem is that the directory which holds the files
changes. Can I use "sName = Application.GetOpenFilename" with "*A*.csv"?
If not, how do I select a folder, and open multiple files with a similar
name & file type, eg. *A*.csv.

With my next macro, I open 3 report csv files (*_accept, *_warning &
*_rejected), format & combine them, then split them up into 3 xls workbooks
containing portions of each file. The new workbooks are named A*.xls,
B*.xls & C*.xls. Currently, I open each file individually, then save then
individually, without any problems. However, I wish to update the macro so
that I specify a portion of the file names to open all 3 files, and use that
same string as part of the saved file name. Using the "Acorn Method", I
have written this macro which will return the answer that is given;

Sub Ask_and_Answer()
Dim fAns As String
fAns = InputBox("What number will you choose?")
MsgBox fAns, , "Your Answer is:"
End Sub

How do I use the answer box to open fAns_accept.csv, fAns_warning.csv &
fAns_reject.csv, process them as above, and save them as A_fAns.xls,
B_fAns.xls & C_fAns.xls?

Any help would be greatly appreciated.


Thanks in advance
Andrew


"Tom Ogilvy" wrote in message
...
Sub Macro1()
Dim sName as String
sName = Dir("C:\Data\*.xls")
do while sName < ""
Workbooks.Open Filename:=sName
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy


"ianripping " wrote in message
...
Currently I have a mcro that opens up one workbook: -

Sub Macro1()
Workbooks.Open Filename:="C:\Data\Book2.xls"
End Sub

Can I make this Macro open all .xls files in the current Directory: -
Like

Sub Macro1()
Workbooks.Open Filename:="C:\Data\*.xls"
End Sub

Any ideas?


---
Message posted from http://www.ExcelForum.com/