GetOpenFilename not returning a usable variable

L

Lilivati

I am trying to identify a workbook as a variable, but GetOpenFilename
is not returning something which can be passed to any of the Workbooks
functions.

My code:

Public modelfile As String
Public nassisfile As String

Sub Workbook_Open()
modelfile = ThisWorkbook.Name

nassisfile = Application.GetOpenFilename(MultiSelect:=False)
Workbooks.Open nassisfile
Workbooks(modelfile).Activate

'it works fine up until this point:
Workbooks(nassisfile).Activate

'I have also tried: Workbooks("nassisfile").Activate

End Sub

The error it gives me is "subscript is out of range"

Please help!
 
D

Dave Peterson

nassisfile will return the full path to the file.

Workbooks(variablenamehere) doesn't want the drive or path--just the stripped
down filename.

I'd use something like:

Sub Workbook_Open()
Dim nassisfile As Variant
dim NassisWkbk as workbook

nassisfile = Application.GetOpenFilename(MultiSelect:=False)
if nassisfile = false then
exit sub 'user hit cancel
end if

set nassiswkbk = workbooks.open(filename:=nsassisfile)

'then you can use:
thisworkbook.activate 'no need for keeping track via modelfile

'or
nassiswkbk.activate 'refer to the workbook variable

end sub

And for the most part, you don't need to activate/select stuff to work with
them.

You could do:

Dim rngtocopy as range
with nassiswkbk.worksheets("sheet9999")
set rngtocopy = .range("a1:E" & .cells(.rows.count,"A").end(xlup).row)
end with

rngtocopy.copy _
destination:=thisworkbook.worksheets("sheet888") _
.range("a1").end(xldown).offset(1,0)

To copy a range to sheet888 of the workbook with the code (ThisWorkbook).
 
Top