Out of Range

J

Jeff

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
 
B

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)
 
N

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
Click to expand...

will be the active workbook.
 
D

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.
 
Top