Subscript out of range setting Workbooks.Item(bookname)

[MP]

Trying to write a class wrapper to handle calls to excel object model via
vb6.

'in cls
Private moExcelApp as Excel.Application
Private moWorkBook as Workbook

'FullPath = "Z:\0\0code\vb\excel\TestBook.xls"
Public Sub OpenWorkBook(FullPathName as String)
Dim fName as String

fName = FileNameOnly(FullPathName)
'fName = "TestBook"

If FileExists(FullPathName) Then
moExcelApp.WorkBooks.Open FullPathName

'>>>>>>>>>>>>>>> subscript out of range error here
Set moWorkBook = moExcelApp.WorkBooks.Item(fName)


Else
LogError "File not found " & FullPathName
End If

I thought one could use a name as index to .Item property
what am I doing wrong?
Thanks
Mark

 

[Dave Peterson]

You didn't share the FileNameOnly function.

Any chance you're stripping the extension in that function. If you are, then
don't do that. Depending on a windows setting (to show extensions for know file
types), you could have this trouble.

Another option would be to replace this:

moExcelApp.WorkBooks.Open FullPathName
Set moWorkBook = moExcelApp.WorkBooks.Item(fName)

with
set moworkbook = moExcelApp.WorkBooks.Open(FullPathName)

 

[MP]

Wow Thanks for the prompt response

You didn't share the FileNameOnly function.

Any chance you're stripping the extension in that function. If you are,
then
don't do that. Depending on a windows setting (to show extensions for
know file
types), you could have this trouble.

Yes I am stripping the extension...I assumed local workbook names would just
be the name without the extension...thanks for the clarification...

Another option would be to replace this:

moExcelApp.WorkBooks.Open FullPathName
Set moWorkBook = moExcelApp.WorkBooks.Item(fName)

with
set moworkbook = moExcelApp.WorkBooks.Open(FullPathName)

ok that's great...
The help in excel and in vba in excel did not say that the .open method had
a return value....I should have just tried it

is there a "developer" help in excel hidden away somewhere? other than
opening vba in excel and going to help from there?


Thanks again
Mark

 

[Dave Peterson]

You're welcome. Here's hoping it was right!

(and it should have been "...knowN extensions...")