Run-time Error 9 On Activate

[DAVEG]

Learning VB6 by trial and error from code snippits on the web, however, am embarrassed to say I can't solve this simple one:

Trying to Open an Excel sheet and read it from VB6 code written in another Excel file.

Please Login or Register  to view this content.

...gives run-time error 9 , subscript out of range, which Microsoft site tells me is because I have not fully qualified the name, which I have.

Please Login or Register  to view this content.

works

Please Login or Register  to view this content.

gives run-time error 9 too

Lots of examples on the web seem to indicate that both the error lines should work...though took me some time to work out that the file had to be open before using Activate !
Also tried opening it manually then Activate, but same result.
Any help welcome.

Thanks
Dave

[mikerickson]

Please Login or Register  to view this content.

refers to the open workbook abc. Since your file is not open yet, it errors. (The index "abc" is out of bounds.)
If you open it with the second syntax, the newly opened workbook should become the ActiveWorkbook.

[DAVEG]

Thanks, but I get error 9 with the first Open syntax, even if I've manually opened the file...does my VB6 Workbooks object not know about this open ?

[mikerickson]

Please Login or Register  to view this content.

takes workbook names as its argument. Your first code gives it the full file path.

The correct syntax would be

Please Login or Register  to view this content.

Caveat: I work with a Mac and am not confident with PC file format stuff. The ".xls" may not be part of the workbook name. Opening the workbook and runnning

Please Login or Register  to view this content.

will settle the matter.

[jindon]

Learning VB6 by trial and error from code snippits on the web, however, am embarrassed to say I can't solve this simple one:

Trying to Open an Excel sheet and read it from VB6 code written in another Excel file.

Please Login or Register  to view this content.

...gives run-time error 9 , subscript out of range, which Microsoft site tells me is because I have not fully qualified the name, which I have.

Please Login or Register  to view this content.

[Norie]

Dave

You shouldn't include the path when using Workbooks.

You also probably shouldn't be using Activate.

The best way to work with workbooks is to create references to them.
For example:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Leith Ross]

Hello DaveG,

So everyone is on the same page, this is Visual Basic 6 (VB), and not Visual Basic for Applications (VBA). To use VB with Excel you need to set a reference in your VB program to the Excel x.x Object Library. If you don't do this, your code will fail.

Sincerely,
Leith Ross

[Norie]

Leith

Are you sure about that?

I would expect to see some sort of code to create an instance of Excel if the code was in VB.

The OP hasn't posted any.

[Leith Ross]

Hello Norie,

You wouldn't see the reference in the code. The reference is added manually by the programmer before the code is compiled.

Sincerely,
Leith Ross

[Norie]

Leith

That's not what I mean.

In the code posted so far do you see anything like CreateObject, GetObject or Dim xlApp As New Excel.Application?

By the way it isn't always necessary and can actually sometimes be desirable to not set a reference.

[Reafidy]

The OP specified in there first post they are using VB6.

DAVEG, can you confirm you are using VB6 and not VBA as you have stated so everyone is on the same page.

[DAVEG]

Apologies to all re VB6...I am using VB through the Excel 97 VB editor("About" just says VB 1987-1996), which I now realise is probably VBA, not VB6. However, it's my aim to slowly build up enough knowledge to tackle the full version, so all info is helpful. It also begins to make sense why some code snippets go straight for the open spreadsheet(VBA ?), while others (VB I guess) labour at creating Excel objects.
Thank you for all your comments.
Does the difference between VB and VBA make any difference to my error of passing the full path, rather than filename only, to the Open method ?
I am still unclear why this is wrong, as your example and the VBA help says the full path is OK.

[Reafidy]

Dave,

Sounds like you are using VBA (Visual Basic For Applications). Thats Alt-F11 from any office program.

VB6 is a standalone program for creating applications for any 32-bit Windows operating system. It is not sold any more this is because Microsoft has replaced Visual Studio 6.0 with Visual Studio .NET, which includes an updated version of VB - VB .NET.

Have you tried Nories suggestion in post #6?