Subscript out of range setting Workbooks.Item(bookname)
 

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

Subscript out of range setting Workbooks.Item(bookname)
 

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 wrote:

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

Subscript out of range setting Workbooks.Item(bookname)
 

Wow Thanks for the prompt response

"Dave Peterson" wrote in message
...
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

Subscript out of range setting Workbooks.Item(bookname)
 

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

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

MP wrote:

Wow Thanks for the prompt response

"Dave Peterson" wrote in message
...
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