Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting up a list where an item can only be picked once in Excel | Excel Worksheet Functions | |||
Workbooks, subscript out of range | Excel Programming | |||
"Subscript out of range" error for: Workbooks("Test1.xls").Save | Excel Programming | |||
Setting Onaction for File Save on Menu bar using item or index numbers | Excel Programming | |||
Setting OnAction of custom menu item? | Excel Programming |