Access routine to modify excel spreadsheet can only be run once

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

Code:

---

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

---

You could try
sht.Select instead of or as well as sht.Activate
and
sht.cells(1,1).select
or
Activesheet.
cells(1,1).select

Thanks OBP, but unfortunately this didn't help.
Adding sht.Active or sht.Cells(1,1).select made no difference.
Adding Activesheet.cells(1,1).select resulted in a new error message '91 Object variable or With block variable not set'.

I must admit I have never tried this kind of manipulation of many sheets from Access.
I do note that when you start the loop that you use
With sht
which means that you should be able to refer to the sht with a .
so instead of sht.activate it would just be .activate.
So the question is, as you are using sht. more than once within the with sht do you actually need the with sht at all?

As you can probably tell I am clutching at straws here and just looking for anomalies that might confuse the VBA code.

The above didn't fix the issue, but you certainly sent me in the right direction.

First I removed the unnecessary use of 'sht' inside the 'With sht', which didn't change anything.
I then removed the 'With sht' and then re-added the 'sht' where necessary, which also didn't work, but made me wonder if i needed to qualify the 'sht' worksheet for the multiple 'Range' methods called in the 'Set rng...' line?

This threw another error, so I then split out the 'Set rng...' line into two lines as follows:

Code:

---

Dim endAdd As String

endAdd = sht.Range("A1").SpecialCells(xlCellTypeLastCell).Address
Set rng = sht.Range("A1", endAdd)

---

And this stopped the error occurring on the 'Set rng...' line. :yes

I did then get a "Object variable or With block variable not set" error on the next line ('set xlTbl...'), but replacing 'ActiveSheet.ListObjects' with 'sht.ListObjects' seemed to do the trick.

Thank you so much for your help, I have no idea how long it would have taken me to get this sorted without your help :thumb