Error 1004 Copying Excel Workshtee from Access

[ted.martin]

I am outputting from Query and then have Excel Object code to copy the Excel worksheet and creating sub-totals.

I am aware that the 1004 can occur during this process; there is lots of inromation but it the solution I am after. The code runs properly every alternate time (again this is on Interweb too)

Here is my code and anything solutions would be appreciated.

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook

Set xl = Nothing
Set xlwb = Nothing

Set xl = CreateObject("Excel.Application")

Set xlwb = xl.Workbooks.Open(strFile)

Dim xlWS As Worksheet
Set xlWS = Nothing
Set xlWS = xlwb.Worksheets(1)

xl.Visible = True

' CREATE the Sub-Totals

xlwb.Sheets(1).Select
xlwb.Sheets(1).Copy Before:=Sheets(1)    ' THIS IS THE LINE THAT ERRORS


'FROM HERE ON IT IS FINE.

Set xlWS = xlwb.Sheets(2)
xlWS.Select
xlWS.Name = "Sub-Totals"

xlWS.Range("A1").Select
xlWS.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    
xlWS.Select
    xl.Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

With xlWS
    .Columns("F:F").EntireColumn.AutoFit
    .Columns("B:B").EntireColumn.AutoFit
    .Range("A1").Select
End With

xl.ActiveCell.SpecialCells(xlLastCell).Select

xlwb.Save

MsgBox "Total Value: " & Format(xl.ActiveCell.Value, "Currency"), vbInformation, "Sub-Totals"

Set xlWS = Nothing
Set xlwb = Nothing
Set xl = Nothing

 

[michaeljryan78]

xlwb.Sheets(1).Copy Before:=xlwb.Sheets(1)

with late binding you may have to reference the owrkbook object you created. See if that works.

 

[ted.martin]

Hey that worked but then it 1004'd on

xlWS.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Thats new but almost there. Can you give me the latebinding for this line too please.

 

[michaeljryan78]

the xlLastCell is an integer value. I have to look it up.

xlWS.Range(Selection, ActiveCell.SpecialCells(11)).Select

 

[ted.martin]

Sorry that errored as a 91 NOT 1004 = Object Variable not set; it did not like the 11.

Again it works the first time but not the 2nd. 3rd is OK.

 

[ted.martin]

GOT IT with your HELP

xlWS.Range(xl.Selection, xl.ActiveCell.SpecialCells(xlLastCell)).Select

Just need to undersatnd more about Late Binding.

 

[JHB]

Try this:

xlWS.Select
xlWS.Copy Before:=xlWS

Instead of :

xlwb.Sheets(1).Select xlwb.Sheets(1).Copy Before:=Sheets(1)    ' THIS IS THE LINE THAT ERRORS