GetOpenFilename not returning a usable variable

  • Thread starter Thread starter Lilivati
  • Start date Start date
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!
 
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).
 
Back
Top