Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error:Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of range error | Excel Discussion (Misc queries) | |||
Subscript out of range error | Excel Programming | |||
Run time error '9' Subscript out of range | Excel Programming | |||
Subscript out of range error | Excel Programming |