Excel Open
Excel Open
(OP)
How would I get a macro to open all the files in a folder? I want to open, process, close, repeat. There are 400 files so I don't want to be typing the filenames in by hand.
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
|
RE: Excel Open
SHELL runs a .bat that creates a directory listing in a file dir > dir.txt
Then import dir.txt into excel, parse it, and create a list of files
then run each file.
Noen of that is hard, but the parsing will be a bit tedious to write.
Cheers
Greg Locock
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel Open
Application.ScreenUpdating = False
tf = "YOURFOLDERNAMEHERE"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(tf)
Set fc = f.Files
For Each f1 In fc
Workbooks.OpenText Filename:= tf & f1.Name, Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True
............. YOU CODE HERE
next
Please note that the fileopen line gets cut up by the editor for this BBS. You gotta "cut it right" yourself.
All the variables a variant
It will open all files in the specified directory - no matter what type. I use this for opening a lot of text files that are output from another program - convert the output and aling it for "graphs"
Best regards
Morten
RE: Excel Open
On the General tab, in the At Startup, open all files in box, type the full path of the folder you want to use as the alternate startup folder.
Excel will open every file in the alternate startup folder.
RE: Excel Open
Cheers
Greg Locock
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel Open
Or do you just need to open the file, read in the data, extract whatever information you need, close the file, move on to the next file?
RE: Excel Open
That worked and did just what was needed. I opened my data file (non-excel file), formatted it, extracted the info, etc. pretty much just as prost described. It didn't take only a couple min for it to run so it really wasn't tedious. It would have probably run faster if I didn't use 3 Find's to locate my data. But doing it that way made sure changes in the base format wouldn't screw anything up.
Roger