Help on subscript out of range error (VB6/VBA)
 

Hi,

I want to work with multiple excel workbooks en worksheets using a
stand alone VB6 application.

1. First I open and close the workbooks and populate a listbox with
sheetnames.

2. Then I select some sheets and fill an Array. (This array contains
sheetnames from different workbooks)

3. The last step is to open and close the workbooks again and loop
(For Each) through the sheets to get the information I need using the
above mentioned array.

The only problem is that I receive an "Subscript out of Range" error.
This error occurs because I'm attempting tot access a worksheet that
doesn't exist in a particular workbook.

So I used "On error Resume next" and "On error Goto 0" trying to skip
the Sheet that doesn't exist in one workbook and go to the next sheet.
Unfortunately with no results sofar. (either is does nothing at all or
I receive the subscipt out of Range error )

Question:
1) Can anyone point me in the right direction ?

Help is appreciated.

Help on subscript out of range error (VB6/VBA)
 

Dim sh as Excel.worksheet

On Error Resume Next
set sh = xlApp.Workbooks(1).Worksheets(sName)
On Error goto 0
if not sh is nothing then
' sheet exists, work with it
myval = sh.Cells(1,1).Value
end if

--
Regards,
Tom Ogilvy

farmer wrote in message
...
Hi,

I want to work with multiple excel workbooks en worksheets using a
stand alone VB6 application.

1. First I open and close the workbooks and populate a listbox with
sheetnames.

2. Then I select some sheets and fill an Array. (This array contains
sheetnames from different workbooks)

3. The last step is to open and close the workbooks again and loop
(For Each) through the sheets to get the information I need using the
above mentioned array.

The only problem is that I receive an "Subscript out of Range" error.
This error occurs because I'm attempting tot access a worksheet that
doesn't exist in a particular workbook.

So I used "On error Resume next" and "On error Goto 0" trying to skip
the Sheet that doesn't exist in one workbook and go to the next sheet.
Unfortunately with no results sofar. (either is does nothing at all or
I receive the subscipt out of Range error )

Question:
1) Can anyone point me in the right direction ?

Help is appreciated.

Help on subscript out of range error (VB6/VBA)
 

A better solution might be to store which workbook has which worksheet names
and not try to access a workbook that doesn't have that worksheet name.

--
Regards,
Tom Ogilvy

farmer wrote in message
...
Hi,

I want to work with multiple excel workbooks en worksheets using a
stand alone VB6 application.

1. First I open and close the workbooks and populate a listbox with
sheetnames.

2. Then I select some sheets and fill an Array. (This array contains
sheetnames from different workbooks)

3. The last step is to open and close the workbooks again and loop
(For Each) through the sheets to get the information I need using the
above mentioned array.

The only problem is that I receive an "Subscript out of Range" error.
This error occurs because I'm attempting tot access a worksheet that
doesn't exist in a particular workbook.

So I used "On error Resume next" and "On error Goto 0" trying to skip
the Sheet that doesn't exist in one workbook and go to the next sheet.
Unfortunately with no results sofar. (either is does nothing at all or
I receive the subscipt out of Range error )

Question:
1) Can anyone point me in the right direction ?

Help is appreciated.