Excel VBA syntax question...

[gestaltphoenix]

Hey all...

I'm a bit new to VBA so bear with me...

I currently using a UserForm to have people input 5 different spreadsheets that will be merged. I used the GetOpenFile method to store the filepaths & filenames in a variable so I could open the spreadsheet by using the Application.Workbooks.Open (xxxxxx) command (where x is the variable holding the string with the filepath and name).

Say, John Doe opens c:\My Documents\TPSreport.xls using my input form. That path and filename is stored in the variable 'FirstReport'. Then I say Application.Workbooks.Open (FirstReport). Now the active window opened is named "TPSreport.xls". If I want to reference that window later, I don't want to hardcode
Windows("TPSreport.xls&quot.activate in case Jane Doe has the same report saved as TPSreport1.xls (making the opened window TPSreport1.xls, and resulting in error).

So how do I take JUST the filename (and hence the Window name) as a variable to reference when I want to activate it?

Thanx a bunch...sorry if unclear )

GP

 

[KellyK]

GP,
You could set up a string, for example strFile and then use

strFile = Dir(FirstReport)

to return just the file name. Not sure if this is the best way, but it will work.
Kelly

 

[acron]

When John Doe opens the file with Application.Workbooks.Open etc.., immediately use say FirstFile = ActiveWindow.Caption. Then when you wish to activate that file use Windows(FirstFile).Activate.

A.C.