Run-time Error 1004 Global Object Failed

[akphidelt]

Hey guys, i've been doing some playing around trying to get this project im working on to come together. Below I wrote a code to export a query from access to excel. Now it works perfectly the first time I run it, but then if I run it again the line below in red stops the code and gives me a Run-time error '1004'

"Method 'Range' of object'_Global' failed"

I have tried everything I could find including closing down the application and setting all the objects to nothing... and i still get this message after everytime I run it once.

Private Sub Command44_Click()

Dim objXls As Excel.Application
Dim MyBook As Excel.Workbook
Dim MySheet As Excel.Worksheet, MySheet2 As Excel.Worksheet
Dim MyFile As String
Dim MyRow As Integer
Dim MyRange As Range

DoCmd.TransferSpreadsheet acExport, 8, "qryWeeklyHSET", "C:\Documents and Settings\tmshepe\Desktop\HSET Database\HSETTest.xls", True

Set objXls = CreateObject("Excel.Application")
    MyFile = "C:\Documents and Settings\tmshepe\Desktop\HSET Database\HSETTest.xls"
    objXls.Workbooks.Open ("" & MyFile)
    objXls.Visible = True
    
Set MyBook = objXls.Workbooks("HSETTest.xls")
Set MySheet = MyBook.Worksheets("qryWeeklyHSET")
Set MySheet2 = MyBook.Worksheets("Incidents")

    MySheet.Activate
    
MyRow = MySheet.Range(Range("A1"), Range("A1").End(xlDown)).Count

    MySheet.Range("A2:L" & MyRow).Copy
    MySheet2.Activate
    MySheet2.Range("A2").End(xlDown).Offset(1, 0).PasteSpecial
    objXls.Application.DisplayAlerts = False
    MySheet.Delete
    objXls.Application.DisplayAlerts = True
    MyBook.Saved = True
    MyBook.Close
    objXls.Application.Quit
    objXls.Visible = False
Set objXls = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
Set MySheet2 = Nothing
End Sub

Any suggestions? Thanks in advance.

[shg]

If MySheet is not active then you need to qualify all the references:

with mysheet
    MyRow = .Range(.Range("A1"), .Range("A1").End(xlDown)).Count 
end with

[akphidelt]

Geez you guys are smart... worked perfectly. Thank you very much!

[shg]

For what you're doing, this is simpler:

MyRow = mysheet.Range("A1").End(xlDown)).row