Why does my Access VBA to add subtotals in Excel work in one database but error 1004 in another?

Question

I am new to VBA and using Access to run a query and save the results as a spreadsheet on a local drive. Once saved, Access opens the Excel file and adds subtotal rows and some basic formatting. This works fine in my sandbox database. When I copy and paste the same code into the production database (along with the query and macro), I get this error message:

error message

Public Sub autoformat()
wkbookpath = "H:\1401_by_division.xls"
Dim XL As Object

On Error Resume Next
Set XL = GetObject(, "Excel.Application")
On Error GoTo 0
If XL Is Nothing Then
  Set XL = CreateObject("Excel.Application")
End If

With XL
    .Visible = True
    .DisplayAlerts = False
    .Workbooks.Open wkbookpath
        .Range("1:1").Font.FontStyle = "Bold"
'       .Range("A2").Activate
        .Range("A1").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
          8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Replace:=True, PageBreaks:=False, _
          SummaryBelowData:=True
    .Range("U:U").EntireColumn.Hidden = True
    .Range("1:1").WrapText = True
     .Columns("A:U").EntireColumn.AutoFit
    End With     
End Sub

Here is an example of the spreadsheet I'm trying to subtotal.

enter image description here

Am I not properly referencing the active sheet? That is the only thing I can think of that would explain functioning in one database and then not in another--they are exact copies of each other.

Help!

Answer 1

My guess is that your sandbox database actually has an Excel reference and the production database doesn't. Even though the code is late bound in both of them, you're using xlSum, which is a member of the Excel.XlConsolidationFunction enumeration.

The only way this would work in your sandbox is if the enumeration can be resolved or you're setting it as a constant somewhere. The fact that it compiles in your production database also indicates that you don't have Option Explicit specified in the module (get in the habit of always adding it to avoid this in the future). Otherwise you'd get a compiler error that highlights it. If it isn't declared, it's being treated as an uninitialized Variant, which casts to 0 and throws a 1004 when passed as the Function argument.

If you're late bound without a reference, either use the numeric value of xlSum (-4157)...

.Range("A1").Subtotal GroupBy:=1, Function:=-4157, TotalList:=Array(4, 5, 6, 7, _
  8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Replace:=True, PageBreaks:=False, _
  SummaryBelowData:=True

...or declare it as a constant somewhere:

Public Const xlSum As Long = -4157