Excel 2003 Error "Method 'Range' of Object '_Global' Failed"

[DougP]

This code is in Access 2003 which opens Excel and populates a sheet

Getting error: Method 'Range' of Object '_Global' Failed
on red line below.

It only happens if I run it and then close Excel and then re-run it. After a bit it seems to work again.

Dim objXL As New Excel.Application
    Dim objXLBook As Excel.Workbook

    objXL.Application.Visible = True
    PathToTemplate = "\\servero2\data files\TimeSheet2\AllTimeTemplate.xls"
    Set objXLBook = objXL.Workbooks.Open(PathToTemplate)
    
    With [COLOR=red]objXLBook.ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\TimeSheet2\OHCPay.csv", Destination:=Range("A1"))[/color]
        .Name = "OHCPay" & Format(Now, "mm-dd-yy")
        .FieldNames = True
        .RowNumbers = False
	…
…
   End with
    Set objXL = Nothing
    Set objXLBook = Nothing

TIA

DougP

 

[SkipVought]

Doug, Please post CODE questions in Forum707.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[combo]

..
Destination:=objXLBook.ActiveSheet.Range("A1")

combo

 

[DougP]

OK Skip I keep forgetting

combo,
Where am I putting that line?



DougP

 

[combo]

In the red line. 'Range' is an excel worksheet's object and need to be marked so.

combo

 

[SkipVought]

Doug, HOW MANY TIMES?

Combo, he keeps coming back as we enable!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}