How to Activate unknown file name
 

Hi,
I am creating a macro where the user will choose the xls
file to open. I then want to activate that file, but am
having some trouble figuring out how to write code to
specify which workbook to activate since the user will
choose the workbook and therefore I don't know it
beforehand.
Here is the code so far:

Dim fileToOpen
Dim fileName

fileToOpen = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls),
*.xls")
Workbooks.Open fileName:=fileToOpen
'Workbooks("*****").Activate


I am not sure what to put where the asteriks are in the
last line of code.
I was thinking of finding the filename in the file path by
using the InStrRev to find where the \ is and take the
name between the \ and .xls, such as
InStrRev(fileToOpen, "\")
However, I thought there might be a better way that I
don't know about.

Any help would be much appreciated!
Thanks!
Ashley

How to Activate unknown file name
 

Hi Ashley,

If you want to be able to refer to the new workbook you've opened, you can
use an object variable to do this:

Dim wb As Workbook

Set wb = Workbooks.Open(Filename:=fileToOpen)

wb.Activate

'/ other stuff here

wb.Close SaveChanges:=False
Set wb = Nothing

That said, you shouldn't need to activate the workbook, as it is activated
when you open it. Also, activating and selecting workbooks, worksheets, and
ranges is typically unnecessary - most things can be done without modifying
the selection.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Ashley wrote:
Hi,
I am creating a macro where the user will choose the xls
file to open. I then want to activate that file, but am
having some trouble figuring out how to write code to
specify which workbook to activate since the user will
choose the workbook and therefore I don't know it
beforehand.
Here is the code so far:

Dim fileToOpen
Dim fileName

fileToOpen = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls),
*.xls")
Workbooks.Open fileName:=fileToOpen
'Workbooks("*****").Activate


I am not sure what to put where the asteriks are in the
last line of code.
I was thinking of finding the filename in the file path by
using the InStrRev to find where the \ is and take the
name between the \ and .xls, such as
InStrRev(fileToOpen, "\")
However, I thought there might be a better way that I
don't know about.

Any help would be much appreciated!
Thanks!
Ashley