Need File Name

[Montez659]

I am trying to open a spreadsheet and read from that sheet except I am encountering a "subscript out of range" error on the

Set whatBook = Workbooks(myFile)

portion of what you see below. I figure that is because I am trying to reference the full path and not the file name, so how do I get the file name exculding path? Or am I off base?

myFile = Application.GetOpenFilename("Microsoft Excel Files (*.xls*), *.xls*")
Workbooks.Open (myFile)
skippedLines = False
'On Error GoTo skipIt
Set whatBook = Workbooks(myFile)

With whatBook.Sheets("Sheet1")...

 

[VoG]

Try like this

myFile = Application.GetOpenFilename("Microsoft Excel Files (*.xls*), *.xls*")
Set whatbook = Workbooks.Open(myFile)
skippedLines = False

 

[shg]

A workbook object variable refers to a workbook in memory, not a workbook on disk, so you omit the path and just use the name.

The Open method returns the opened workbook, so you can set the variable when you do the Open:

Set whatBook = Workbooks.Open(myFile)

 

[Montez659]

Boom! Thanks gentlemen. You wouldn't believe all the posts I searched through and I still couldn't find that.

 

[Montez659]

Of course, I say gentlemen, but only if you are both male!