×
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

Macro Switch between Workbooks

Macro Switch between Workbooks

Macro Switch between Workbooks

(OP)
Macro in one xls file opens file to process using Application.FindFile.  It extracts data and outputs it in a macro opened txt file.  How do I switch between files?  I obviously know the name of the file the macro is in and the output file but I can pick any file to process with the .Find.  How do I pass the macro opened filename to the macro so I can "Workbooks("???.xls").Activate"?

The user might have already opened other workbooks so "Workbooks("BOOK?").Activate" won't work since I don't know what number the latest one used.

RE: Macro Switch between Workbooks

(OP)
No need to answer orig ?.  I already found a way around this by doing save as.  I'd still rather know how to access the file name.

Roger

RE: Macro Switch between Workbooks

You can see the name of all open workbooks by:

For each W in Application.Workbooks
    Debug.Print W.Name, W.FullName
Next W

Maybe this helps you along a bit

Cheers,
Joerd

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

RE: Macro Switch between Workbooks

(OP)
Here's what I did:

    Application.FindFile
    On Error Resume Next
    Workbooks("Process.xls").Close SaveChanges:=False
    Kill "Process.xls"
    On Error GoTo 0
    ActiveWorkbook.SaveAs FileName:="Process.xls"

I closed a file name if open, deleted it and then saved as a name I've chosen.  Now I'm sure of the name.  Your's would show me all the names but it wouldn't tell me which one the user just opened.

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