Hi all,

I have the following code in MS Access (Office 2013), which exports data from a variety of tables into a new Excel spreadsheet, then it opens the spreadsheet and cycles through all of the worksheets applying table styles.

However, I can only run this routine successfully once, if I try to run it again, it keeps failing with the error: 1004 Method 'Range' of object'_Global' failed.

The Set rng... line in bold below is where the error occurs.

I then have to close the database and re-open it to get it to work again...but again only the once.

I'm at a complete loss, and very far from being an expert on this, so any advice would be great.

Thanks

Public Sub ExpAISO(OutName As String)
Dim xlx As Excel.Application
Dim sht As Worksheet
Dim Wkb As Excel.Workbook
Dim rng As Range
Dim xlTbl As ListObject
On Error GoTo EH


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblFil1", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblHead1", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblHead2", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblHl1", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblTrans", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblSumm", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblEnd1", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblEnd2", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblUl1", OutName
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblUnall", OutName


    Set xlx = New Excel.Application
    Set Wkb = xlx.Workbooks.Open(OutName)
    xlx.Visible = True
    
For Each sht In Wkb.Worksheets
     With sht
           sht.Activate
           Set rng = sht.Range(Range("A1"), Range("A1").SpecialCells(xlCellTypeLastCell))
           Set xlTbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
           xlTbl.TableStyle = "TableStyleMedium7"
     End With
     
Next sht
    
    Wkb.Save
    Wkb.Close
    Set Wkb = Nothing
    Set xlx = Nothing
    Set xlTbl = Nothing
    Set rng = Nothing
Exit Sub




EH:
    Wkb.Save
    Wkb.Close
    Set Wkb = Nothing
    Set xlx = Nothing
    Set xlTbl = Nothing
    Set rng = Nothing
    MsgBox "Error: " & Err.Number & " " & Err.Description
End Sub