title: VBA Run-time error 1004: Method Range of object _Global failed when trying to create tables in Excel 2013
body: <p>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:</p>
  
  <blockquote>
    <p>Run-time error 1004: Method Range of object _Global failed</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>'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) &lt;&gt; "" Then
      openCheck = IsFileLocked(path)
      If openCheck = True Then
          MsgBox "Please close the file in " &amp; path &amp; " 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 &amp; Format(Date, "yyyy-mm-dd") &amp; ".xlsx"
  
  checkFile (path)
  dumpQueries (path)
  formatFile (path)
  
  'Error Handling
  PROC_ERR:
  
  If Err.Number = 2001 Then
      MsgBox "A file may have been sent to " &amp; path
      Exit Sub
  ElseIf Err.Number = 2501 Then
      MsgBox "A file may have been sent to " &amp; path
      Exit Sub
  ElseIf Err.Number = 3021 Then
      MsgBox "A file may have been sent to " &amp; path
      Exit Sub
  ElseIf Err.Number = 2302 Then
      MsgBox "A file may have been sent to " &amp; path
      Exit Sub
  ElseIf Err.Number = 0 Then
      MsgBox "Your file has been stored in " &amp; pathLabel4.Caption
      Exit Sub
  Else
      MsgBox Err.Number &amp; ": " &amp; Err.Description &amp; vbCrLf &amp; vbCrLf &amp; "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 &lt;&gt; 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
  </code></pre>
  
  <p>The error occurs near the bottom of the code. It's the line:</p>
  
  <blockquote>
    <p>.Worksheets(i).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).name = "myTable1"   </p>
  </blockquote>
  
  <p>There may be a couple functions I left out but they work fine and shouldn't be needed for answering the question. </p>