Subscript Out Of Range - Run-time error 9 (1 Viewer)

[Ashfaque]

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT T_JobOffer.CNO, T_JobOffer.Cname, T_JobOffer.Cposition, " & _
"T_JobOffer.CExpDateOfJoining, T_JobOffer.Cposition, T_JobOffer.CDept, T_EOSB.LastWorkingDay, T_EOSB.LeavingReason " & _
"FROM T_JobOffer INNER JOIN T_EOSB ON T_JobOffer.cno = T_EOSB.Cno WHERE T_JobOffer.CNO =" & Forms!F_EOSB!CNo)

Set objXL = New Excel.Application
objXL.Visible = True

Set objWkb = objXL.Workbooks.Open("D:\Ashfaque\EOSB.xlsx")
Set objSht = objWkb.Worksheets("Sheet1")

Previously it was working well but now the above bold line producing error.

Where might have gone wrong?

Please help me over come this issue.

Thanks,

 

[arnelgp]

Maybe the sheet has been renamed to something.
You can explicitly refer to sheet1:

Set objSht= objWkb.Worksheets(1)

 

[Isaac]

Just to expound a little bit on the arnelgp reply - this method is referring to the position (rather than the name) of the sheet within the workbook, as opposed to using either its end-user visible name or its vba class name.
I agree - that error on that type of line of code definitely means the sheet doesnt' exist.

 

[Ashfaque]

Yes you are correct Arnel,

I was renamed....now working fine.

Thanks,