Excel Open

[rnordquest]

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.

 

[GregLocock]

Here's a disgusting way:

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 for tips on how to make the best use of Eng-Tips.

 

[MortenA]

These lines of VB hould do the trick:

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

 

[psafety]

On the Tools menu, click Options.
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.

 

[GregLocock]

Wow, will it open 4000 files (as in the original post)? Nice tip anyway.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[prost]

When you say you need to open and process the files, do you mean you want them loaded into Excel, so the data appears on a worksheet? Given there are 400 files as you indicated, that would seem a bit tedious to sit there and watch them open and close. But that's up to you of course!

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?

 

[rnordquest]

Thanks Morten,

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