Subscript out of range in Excel worksheet

[Danielf]

Hi,

Hi,

I have written the following program code:

Dim xlApp As New Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strXLS As String

strXLS = "c:\Access\Myfile.xls"

Set xlwrkBk = xlApp.Workbooks.Open(strXLS)


Set xlSheet = xlwrkBk.Worksheets("singles")

Set c = xlSheet.Cells.Find(minfoxls, LookIn:=xlValues)
' minfolxls is a string variable)

If c Is Nothing Then

Else
a = c.ADDRESS

xlSheet.Range(a).EntireRow.Delete


xlwrkBk.Save

End If

xlwrkBk.Close 'Close the spreadsheet object
Set xlSheet = Nothing 'Delete the worksheet object
Set xlwrkBk = Nothing 'Delete the spreadsheet object
xlApp.Quit 'Close the excel application object
Set xlApp = Nothing 'Delete the application object




The program is stopping at " Set xlSheet = xlwrkBk.Worksheets("singles")"

and I am receiving the following error box:

Runtime error 9
Subscript out of range.

I don't understand this error because I am using the same program code with
another Excel file in another module and it works perfectly.

Thanks for help.

Daniel

 

[pr2-eugin]

Might be the Sheet Singles might not exist.. Spelt differently, or all together a different name..

 

[Rx_]

I think your right.
Also, where is the dim for C in the Set statement?

 

[Danielf]

Might be the Sheet Singles might not exist.. Spelt differently, or all together a different name..

No it exists . I have checked it twice. I have tried the same code with the other Excel file and it works. I think this has something to do with my Excel file but
I don't know what.

 

[Danielf]

I think your right.
Also, where is the dim for C in the Set statement?

It is set elsewhere but it works with another Excel file

 

[Rx_]

Got it. Let me ask a few silly questions based on my own silly past experience.
1. By chance, is the Excel workbook that works created with the same version of Excel as the Excel workbook that doesn't work?
2. In Tools References - module - is the version of Excel correct?


Set xlSheet = xlwrkBk.Worksheets("singles")
Add a breakpoint to this line of code -

In the debug (immediate) window enter
? xlwrkBk.Visible
(did the expected workbook become visible?)
? xlwrkbk.worksheets.count
(did the count come out right?)
? xlwrkbk.worksheets(1).Name
(enter the different numbers for the index... do the names come out correct?)


As a person who produces and consumes data from Excel with automation, sometimes I prefer to write code to determine what is the proper sheet name.
For example, they send a Sheet1, Sheet2, TheData, Sheet4 in a workbook.
Using For Each Worksheet to look at the name - look at the one that is not like "sheet"
Then return the ordinal number for the one not like "sheet" instead of its actual name.
In other words, open it by its ordinal sheet number (index).
This way, if they change a name on you, it still comes back rather than being hard coded as a name.

Let us know how it goes. Sounds like it is close to working.

example:
Dim ws As Worksheet, flg As Boolean
For Each ws In Sheets
If LCase(ws.Name) Like "*alpha*" Then
ws.Select Not flg
flg = True
Debug.Print ws.Name
Debug.Print ws.Index ' the worksheet number
End If
Next

 

[Danielf]

Got it. Let me ask a few silly questions based on my own silly past experience.
1. By chance, is the Excel workbook that works created with the same version of Excel as the Excel workbook that doesn't work?
2. In Tools References - module - is the version of Excel correct?


Set xlSheet = xlwrkBk.Worksheets("singles")
Add a breakpoint to this line of code -

In the debug (immediate) window enter
? xlwrkBk.Visible
(did the expected workbook become visible?)
? xlwrkbk.worksheets.count
(did the count come out right?)
? xlwrkbk.worksheets(1).Name
(enter the different numbers for the index... do the names come out correct?)


As a person who produces and consumes data from Excel with automation, sometimes I prefer to write code to determine what is the proper sheet name.
For example, they send a Sheet1, Sheet2, TheData, Sheet4 in a workbook.
Using For Each Worksheet to look at the name - look at the one that is not like "sheet"
Then return the ordinal number for the one not like "sheet" instead of its actual name.
In other words, open it by its ordinal sheet number (index).
This way, if they change a name on you, it still comes back rather than being hard coded as a name.

Let us know how it goes. Sounds like it is close to working.

example:
Dim ws As Worksheet, flg As Boolean
For Each ws In Sheets
If LCase(ws.Name) Like "*alpha*" Then
ws.Select Not flg
flg = True
Debug.Print ws.Name
Debug.Print ws.Index ' the worksheet number
End If
Next

Hi Rx,

Thanks a lot for your explanation.
I found that there was a problem with my Excel file. I have modified it and now
it works.

But again thank you very much.

Daniel