Run-time error '9'

[Ashfaque]

Hello,

I have managed to activate Excel sheet2 thru my below vba code but sometime it works perfectly and sometime it produces subjected error on Red color line.

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

Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("D:\SLS DB\CUSTOMINV.xls")
Set objSht = objWkb.Worksheets("Sheet1")
.........
...........
.............

Set objSht2 = objWkb.Worksheets("Sheet2")

Workbooks("CUSTOMINV.xls").Worksheets("Sheet2").Activate

objSht2.Cells(12, 2).Value = rst!CustomerName
objSht2.Cells(13, 2).Value = rst2!CustAddress
objSht2.Cells(14, 2).Value = rst2!City + ", " + rst2!Country
objSht2.Cells(16, 2).Value = rst2!Tel + " Fax:" + rst2!Fax
objSht2.Cells(15, 6).Value = rst2!PaymentTerms
objXL.ActiveSheet.Name = "D00" + Forms!F_SOHeader!CustomDNNum

....
......
........

Set objSht = Nothing
Set objSht2 = Nothing
Set objWkb = Nothing
Set objXL = Nothing
....
......


When I am closing my form and opening again, it works without any problem.

Can someone let me know where it went wrong?

Regards,
Ashfaque

 

[NigelShaw]

Hello,

I have managed to activate Excel sheet2 thru my below vba code but sometime it works perfectly and sometime it produces subjected error on Red color line.

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

Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("D:\SLS DB\CUSTOMINV.xls")
Set objSht = objWkb.Worksheets("Sheet1")
.........
...........
.............

Set objSht2 = objWkb.Worksheets("Sheet2")

Workbooks("CUSTOMINV.xls").Worksheets("Sheet2").Activate

objSht2.Cells(12, 2).Value = rst!CustomerName
objSht2.Cells(13, 2).Value = rst2!CustAddress
objSht2.Cells(14, 2).Value = rst2!City + ", " + rst2!Country
objSht2.Cells(16, 2).Value = rst2!Tel + " Fax:" + rst2!Fax
objSht2.Cells(15, 6).Value = rst2!PaymentTerms
objXL.ActiveSheet.Name = "D00" + Forms!F_SOHeader!CustomDNNum

....
......
........

Set objSht = Nothing
Set objSht2 = Nothing
Set objWkb = Nothing
Set objXL = Nothing
....
......


When I am closing my form and opening again, it works without any problem.

Can someone let me know where it went wrong?

Regards,
Ashfaque

Hi

I haven't tried your code but as an observation, why set objSht when it isn't being used?

Nidge

 

[Ashfaque]

Thanks Nidge,

off course I m using objsht somewhere in the code that I didnt mentioned just to reduce the length of my vba code.

FYI, I used objsht for sheet1 in my code where I just mentioned
.........
...........
.............

Above dot means some other lines of lengthy codes.

Thanks,

 

[Galaxiom]

You seem to be all over the place with your objects. You set them to variables then refer to them other than through the variable.

Set objSht2 = objWkb.Worksheets("Sheet2")
[COLOR=black]Workbooks("CUSTOMINV.xls").Worksheets("Sheet2").Activate

[/COLOR]
might make more sense as:

Set objSht2 = objWkb.Worksheets("Sheet2")
objSht2.Activate

Review how you are managing objects and it will probably become clear.

 

[Ashfaque]

Thank you very much Galaxiom,

It works well now...

Regards,
Ashfaque