Out of Range

[Guest]

Hello,
This is my VBA macro. I'm getting a "Subcription out of range #9" error
message on: Windows(fname).Activate
Can someone help fix it, if possible?
Regards,
fname = Application.GetOpenFilename
Workbooks.Open filename:=fname

Windows(fname).Activate

 

[Norman Jones]

Hi Jeff,

Drop the line:

Windows(fname).Activate

The opened workbook is the active workbook.

 

[Bob Phillips]

Jeff,

This is because the fname variable will be a full path and filename, whereas
Windows just needs the filename.

But, it is all unnecessary, as the workbook becomes the active workbook when
opened, so there is no need to activate it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you,
But what if have have 2 or three workborks active ?
Regards,

 

[Norman Jones]

Hi Jeff,

You can have multiple workbooks open but only one will be the active
workbook. The workbook opened by the instruction:

Workbooks.Open filename:=fname

will be the active workbook.

 

[Dave Peterson]

I like to do stuff like this:

dim curWkbk as workbook
dim newWkbk as workbook
dim fName as variant

set curwkbk = activeworkbook
fname = Application.GetOpenFilename
if fname = false then
exit sub 'cancelled
end if
set newwkbk = workbooks.open(filename:=fname)

Then I can refer to the newwkbk like:

newwkbk.worksheets(1).range("a1").value = "hi"

or the previous workbook:
curwkbk.worksheets("sheet1").range("b99").value = "there"

And I don't have to use the Windows collection.