Consulting

Results 1 to 5 of 5

Thread: Access routine to modify excel spreadsheet can only be run once

  1. #1
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location

    Post 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

    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

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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

  3. #3
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location
    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'.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  5. #5
    VBAX Regular
    Joined
    May 2019
    Location
    Brighton
    Posts
    9
    Location
    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:

    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.

    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



Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •