Macro Switch between Workbooks

[rnordquest]

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&quot.Activate"?

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

 

[rnordquest]

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

 

[joerd]

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.

 

[rnordquest]

Here's what I did:

Application.FindFile
On Error Resume Next
Workbooks("Process.xls&quot.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.