×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Open

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.

RE: Excel Open

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

RE: Excel Open

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

RE: Excel Open

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.

RE: Excel Open

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

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

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?

RE: Excel Open

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources