VBA Run-time error 1004: Method Range of object _Global failed when trying to create tables in Excel 2013

Question

I understand similar questions with these errors have been posted before, but I found nothing when it came to formatting tables so don't close this please. In my VBA code in MS Access 2013 it exports data from MS Access to Excel. 6 different queries get exported into 1 excel file, each on a different worksheet. This works fine. I then format each sheet to have all the data in a table. I have a form which lets the user choose the path to save the file. If it is the first time creating the file, it works properly. If it is the second time creating the file in that same directory, it doesn't work and it gives me the error:

Run-time error 1004: Method Range of object _Global failed

I figured this was because I was overwriting my file instead of deleting it and recreating it. So I added in some code to check if the file exists, and if it does, delete it. I added breakpoints and while running through this part of the code, I was watching my documents folder. The file successfully got deleted and then recreated which is what I wanted. It still gave me that error. I manually went to delete the file and then reran my code again. It worked properly.

How come I need to manually delete this file in order to rerun my code? Or is it something else that is causing the problem? Here is the important parts of my code as the whole thing is too long to post:

'Checks if a file exists, then checks if it is open
Private Sub checkFile(path As String)

Dim openCheck As Boolean
'If file exists, make sure it isn't open. If it doesn't, create it
If Dir(path) <> "" Then
    openCheck = IsFileLocked(path)
    If openCheck = True Then
        MsgBox "Please close the file in " & path & " first and try again."
        End
    Else
        deleteFile (path)
    End If
Else

End If

End Sub

Sub deleteFile(ByVal FileToDelete As String)

    SetAttr FileToDelete, vbNormal
    Kill FileToDelete

End Sub

Private Sub dumpButton_Click()

On Error GoTo PROC_ERR

Dim path As String
Dim testBool As Boolean

path = pathLabel4.Caption
path = path & Format(Date, "yyyy-mm-dd") & ".xlsx"

checkFile (path)
dumpQueries (path)
formatFile (path)

'Error Handling
PROC_ERR:

If Err.Number = 2001 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 2501 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 3021 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 2302 Then
    MsgBox "A file may have been sent to " & path
    Exit Sub
ElseIf Err.Number = 0 Then
    MsgBox "Your file has been stored in " & pathLabel4.Caption
    Exit Sub
Else
    MsgBox Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & "New Error. Please contact the IT department."
End If


Private Sub dumpQueries(path As String)

Dim obj As AccessObject, dB As Object

Set dB = Application.CurrentData
For Each obj In dB.AllQueries
    testBool = InStr(obj.name, "Sys")
    If testBool <> True Then
        If obj.name = "example1" Or obj.name = "example2" Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, obj.name, path, True, editWorksheetName(obj.name)
        End If
    End If
Next obj

End Sub


'Autofits the cells in every worksheet
Private Sub formatFile(path As String)

Dim Date1 As Date, strReportAddress As String
Dim objActiveWkb As Object, appExcel As Object

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
appExcel.Application.Workbooks.Open (path)

Set objActiveWkb = appExcel.Application.ActiveWorkbook
With objActiveWkb
    Dim i As Integer
    For i = 1 To .Worksheets.count
        .Worksheets(i).Select
        .Worksheets(i).Cells.EntireColumn.AutoFit
        .Worksheets(i).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).name = "myTable1"         
    Next
End With

appExcel.ActiveWindow.TabRatio = 0.7
objActiveWkb.Close savechanges:=True
appExcel.Application.Quit
Set objActiveWkb = Nothing: Set appExcel = Nothing

End Sub

The error occurs near the bottom of the code. It's the line:

.Worksheets(i).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).name = "myTable1"

There may be a couple functions I left out but they work fine and shouldn't be needed for answering the question.

Answer 1

This is the only relevant code:

Set objActiveWkb = appExcel.Application.ActiveWorkbook
With objActiveWkb
    Dim i As Integer
    For i = 1 To .Worksheets.count
        .Worksheets(i).Select
        .Worksheets(i).Cells.EntireColumn.AutoFit
        .Worksheets(i).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).name = "myTable1"         
    Next
End With

Things get easier to follow when you trim the fluff away and start naming things - there's no need to .Select anything, appExcel is already an Application object, and there's no need to make a copy reference to the active workbook just to use in a With block, especially if that copy is going to be an Object variable anyway - if the copy were a Workbook object then you would at least get IntelliSense for its members...

Your source range is ambiguous. Range("A1") in Excel-VBA is an implicit reference to the active worksheet.. but this is Access-VBA, so there's no such thing, xlSrcRange is an enum value defined in the Excel object model, so if you don't have a reference to the Excel object model (you're late-binding this, right?), and Option Explicit isn't specified, then xlSrcRange is treated by VBA like just another undeclared/uninitialized variable, and therefore you're passing a 0 there, and the xlSrcRange enum value stands for a 1 - and 0 happens to be the underlying value for xlSrcExternal. Same with xlYes.

Since we cannot possibly guess what the actual source range is supposed to be from the code you posted, I'm leaving you with this:

Dim target As Object
Dim srcRange As Object
Set srcRange = TODO

With appExcel.ActiveWorkbook
    Dim i As Integer
    For i = 1 To .Worksheets.Count
        .Worksheets(i).Cells.EntireColumn.AutoFit
        Set target = .Worksheets(i).ListObjects.Add(1, srcRange, , 1)
        If target Is Not Nothing Then target.Name = "myTable1"
    Next
End With

Side question... why name the table myTable1 when Excel will already have named it Table1 anyway? Also note, if .Add fails, your code blows up with a runtime error 91 because you'd be calling .Add off Nothing. Verifying that the target is not Nothing before setting its Name will avoid that.

To answer your question in the comments:

@Mat'sMug is this what you were talking about? because it gives me this error: "438: Object doesn't support this property or method" Here's the code: .Worksheets(i).ListObjects.Add(SourceType:=xlSrcRange, Source:=.Cells(1).CurrentRegion, _ XlListObjectHasHeaders:=xlYes, TableStylename:="TableStyleMedium1").name = "Table"

The reason this throws a 438 is because your With block variable is a Workbook object, and a Workbook object doesn't have a .Range member.

What I was talking about, is that in Excel VBA unqualified calls to Range, Row, Column, and Cells are implicitly referencing the ActiveSheet, and unqualified calls to Worksheets, Sheets and Names are implicitly referencing the ActiveWorkbook - that's a recurrent problem in a lot of VBA code and a very common mistake to make. The solution is basically to say what you mean, and mean what you say; in this case the failure is on "mean what you say" - the unqualified Range("A1") call is, according to the error message, calling [_Globals].Range("A1")... which is weird because it implies that you're referencing the Excel object model library, which means your late-binding and Object variables could just as well be early-bound: why deal with Object variables and lack of IntelliSense when you're already referencing the library you're late-binding to?