Run time error 9 subscript out of range

[presuming_ed]

I'm trying to create an excel spreadsheet using the following code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql5 As String
Dim objxl As Excel.Application
Dim objxlwrkbk As Excel.Workbook
Dim objxlwrksht As Excel.Worksheet
DoCmd.OutputTo acOutputTable, "null", acFormatXLS, "C:\stock_tracker.xls", False

strsql5 = "select * from email_this"

Set db = CurrentDb
Set rs = db.OpenRecordset(strsql5)

If rs.bof Then
Dim end_of_table As String
Else
Set objxl = CreateObject("Excel.Application")
Set objxlwrkbk = objxl.Workbooks.Open("c:\stock_tracker.xls")
Set objxlwrksht = objxlwrkbk.Worksheets("sheet1")
rs.MoveLast
rs.MoveFirst
objxlwrksht.Range("A1:A3").Select
While Not rs.EOF
objxlwrksht.Range("A1:A3").Font.ColorIndex = 3
'objxlwrksht.Selection.Font.ColorIndex = 3
rs.MoveNext
Wend
objxlwrkbk.Close savechanges:=True
objxl.Quit
End If


.... but I get a subscript out of range error message. I've looked at previous posts but got no joy. I've checked all references are setup. Any ideas?

Thanks.

 

[AnnePep]

Check that you have a worksheet called "sheet1". If it isnt there, you will get that error.

HTH

Anne

 

[AnnePep]

I tried it out - you need to change:

Set objxlwrksht = objxlwrkbk.Worksheets("sheet1")


to

Set objxlwrksht = objxlwrkbk.Worksheets("null")

It seems that when creating the new workbook, excel will name the worksheet after the object your are outputting.

(Just curious - do you have a table named "Null" or are you sending that value to the outputTo function. If so, you dont need the quotes)

 

[presuming_ed]

AnnePep.... thanks, you're a god damn genius. Yes I do have a table called Null so you're absolutely right.

Thanks again.

 

[AnnePep]

nah - not a genius, just wrote some bad code...

I do a lot of excel automation and have run into this error before.

Glad I could help! Anne