Results 1 to 10 of 10
  1. #1
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34

    Exporting to Excel with formatting gives an error every other time

    I am working on exporting a query to an Excel spreadsheet and then formatting it. It works well the first time, but then if I try to run it again I get the error "method 'range' of object '_Global' failed".



    I don't really understand VBA but there's several posts here by others who had this problem, and I think I sort of understand why it's happening - there's an explanation here: https://btabdevelopment.com/why-exce...ode-in-access/
    I've tried to modify my code accordingly, but it still keeps happening. Can anyone explain why?

    Some code, with the line that gives the error in red:

    Code:
    'Export to Excel
        
    Dim stDocName As String
    Dim PathFile As String
    Dim Exc As Excel.Application
    Dim wb As Object
    Dim sh As Object
    Dim TableRange As String
     
    PathFile = "Timesheet" & Format(Me.txtTimesheetID, "00000") & ".xlsx"     'Path where I want the spreadsheet created
    stDocName = "Timesheet" 'The query I want to trasfer to a spreadsheet
     
    'Have Access create the spreadsheet
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
        stDocName, PathFile, True
     
    'Set Exc to an Excel application
    'Set wb to the workbook that was created by TransferSpreadsheet above
    'and set sh to the sheet
            Set Exc = CreateObject("Excel.Application")
            Set wb = Exc.Workbooks.Open(PathFile, True, False) 'Open the xlsx file created by Access
            Set sh = wb.Worksheets("Timesheet")
    
    
    'Apply table style
        TableRange = "$A$1:$J$50"
        sh.ListObjects.Add(1, Range(TableRange), , xlYes).Name = "Table1"
        sh.Range("Table1[#All]").Select
        sh.ListObjects("Table1").TableStyle = "TableStyleMedium2"
        sh.Range("Table1[#Headers]").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With
    
    
    'More formatting
    
    
    Exc.Visible = True 'I want to see it
      
    sh.Range("A2").Select
    wb.Save 'Save the workbook
    
    
    'Set variables to nothing
    Set wb = Nothing
    Set Exc = Nothing
    Gushing thanks in advance for any help.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The linked topic (I only took a quick look) is stating that you need to reference the parent object(s) when you refer to a child object (when using this type of Automation). So if you declare and set a workbook and a sheet, you should use a reference like (substitute your variable names): WorkbookVariableName.SheetVariableName
    You are only using sh. which will likely open another but hidden instance of Excel. Try this

    Close Excel and check Task Manager for open Excel apps and terminate any if found
    run your code and it might be ok (no Excel was open)
    run it again and when it fails, stop the code and look at TM again - you will likely find you have 2 instances of Excel running.
    If that is all true, then the solution is to fix the code. You can include the parent variable on every line, or you could use a With block.
    Last edited by Micron; 11-13-2020 at 01:39 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    I tried to fix the code along the lines you suggested, but everything I tried either didn't make a difference, or gave errors.
    How do I 'include the parent variable on every line'? You meant put a wb. in front of sh? I changed the problematic line (above) to

    Code:
    wb.sh.ListObjects.Add(1, Range(TableRange), , xlYes).Name = "Table1"
    This gives the error, "Object doesn't support this property or method". I get similar problems using a With block.
    Sorry if I've got the wrong end of the stick, I'm afraid I don't really know what I'm doing.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My first thought was that your issue was here:

    sh.Range("Table1[#Headers]").Select
    With Selection.Interior

    Selection is not referencing a parent object. Here's a snippet of code that works for me:

    Code:
        xl.Workbooks.Open (strFile)
        With xl    'header information
            .Cells(2, 6) = rs("CustomerAccount")
            .Cells(2, 6).Select
            .Selection.NumberFormat = "General"
            .Cells(3, 6) = rs("CustomerName")
            ...
    Inside the With block, mine is the equivalent of

    xl.Selection...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    Thank you for suggestions.
    I didn't figure out how to do the With block so I just included the parent variable on every line, but it didn't seem to make any difference.
    But I happened to find something on this thread that worked:

    Code:
    'This deals with Excel already being open or not
    On Error Resume Next
    Set Exc = GetObject(, "Excel.Application")
    On Error GoTo 0
    If Exc Is Nothing Then
      Set Exc = CreateObject("Excel.Application")
    End If
    Seems OK now! Until the next thing rears it's ugly head...

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    The PathFile does not include a directory path, so the directory path to the file created by Access is the current directory, which can be any directory. Specify the desired directory to make sure that you work with the desired file.

    As you have set a reference to the Excel object library (late binding), you can use the "New" keyword to create an instance of Excel application.
    Code:
    Set Exc = New Excel.Application
    As you have create properly a pointer to the desired worksheet, you have to use it in any time you want to work with its members, because, a general reference for a range (e.g. Range(A1)), refers to the range A1 of active sheet of active workbook, which can be other than you want work with.

    At the next time you try to add a table that already exists, you get an error, so, have to call the Unlist method of the table before add it again.

    Finally, try to avoid the selections of Excel objects as you can. Has to do with the UI and the active workbook and worksheet, therefor slows down your code and in many cases causes problems and errors. Do not need to select an object to work with it via VBA.

    Below is my suggestions for your code:
    Code:
    Dim Exc As Excel.Application
        Dim wb As Excel.Workbook
        Dim sh As Excel.Worksheet
        Dim stDocName As String
        Dim strPath As String
        Dim PathFile As String
        
        strPath = CurrentProject.Path & "\Timesheets\" '<--Specify an existing path
        PathFile = strPath & "Timesheet" & Format(Me.txtTimesheetID, "00000") & ".xlsx" 'Path where I want the spreadsheet created
        stDocName = "Timesheet" 'The query I want to transfer to a spreadsheet
    
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, stDocName, PathFile, True
        
        Set Exc = New Excel.Application
        Set wb = Exc.Workbooks.Open(PathFile)
        Set sh = wb.Worksheets(stDocName)
    
        With sh.ListObjects
            On Error Resume Next
            .Item("Table1").Unlist 'Remove the "Table1" table if exists
            On Error GoTo 0
            
            With .Add(xlSrcRange, sh.Range("$A$1:$J$50"), , xlYes, , "TableStyleMedium2")
                .Name = "Table1"
                With .HeaderRowRange.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent4
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
            End With
        End With
        '[...]
        Exc.Visible = True
        Set sh = Nothing
        Set wb = Nothing
        Set Exc = Nothing
        '[...]

  7. #7
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    Wow, that's ace, so much neater than my effort. Brilliant.

    I deliberately ommitted the directory path because I want people to be able to use local copies of the db for generating Excel spreadsheets. At the moment the spreadsheets just get saved to the Office default location, which works OK. (I'd really like the user to be able to browse for a save location, but that's a problem for another day).

    As for the rest of your suggestions, I'll take them on board and see what I can do with them as soon as I get the chance. It's true that with my code I get an error if I make try to make 2 spreadsheets with the same name on top of each other.

    When I paste in your code I get the error "Sorry, we couldn't find Timesheet00007.xlsx. Is it possible it was moved, renamed or deleted?" at the line
    Code:
    Set wb = Exc.Workbooks.Open(PathFile)
    Will investigate further when I get the chance. For now, thanks.
    Last edited by RasterImage; 11-17-2020 at 03:39 AM. Reason: Correct a typo

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by RasterImage View Post
    Wow, that's ace, so much neater than my effort. Brilliant.
    Thank you! That makes me happy!

    Quote Originally Posted by RasterImage View Post
    At the moment the spreadsheets just get saved to the Office default location, which works OK.
    Note that the "default location" maybe is not the same with "current directory".
    I recommends you to specify always the path of the desired location, even if this is not necessary. You can use the CurrentProject.Path or the Environ("APPDATA") to get a secured path in the user's system.

    Quote Originally Posted by RasterImage View Post
    When I paste in your code I get the error "Sorry, we couldn't find Timesheet00007.xlsx. Is it possible it was moved, renamed or deleted?" at the line
    Code:
    Set wb = Exc.Workbooks.Open(PathFile)
    I think that you faced this problem because of lack of explicitly specified directories where I am talking about. I believe that will run properly if you add the CurDir before the PathFile as seems below:
    Code:
    Set wb = Exc.Workbooks.Open(CurDir & "\" & PathFile)
    Quote Originally Posted by RasterImage View Post
    I'd really like the user to be able to browse for a save location, but that's a problem for another day
    I think that today is your lucky day:
    Code:
    Sub ExportTimesheet()
        Dim Exc As Excel.Application
        Dim wb As Excel.Workbook
        Dim sh As Excel.Worksheet
        Dim FD As FileDialog    '<--Need a reference to Miscrosoft Office ##.0 Object Library
        Dim strPath As String
        Dim PathFile As String
        Const strcDocName As String = "Timesheet"   'The query I want to trasfer to a spreadsheet
        Const strcTableName As String = "Table1"    'The name of the table in spreadsheet
    
        'Browse for the save location
        Set FD = Application.FileDialog(msoFileDialogFolderPicker)
        With FD
            .AllowMultiSelect = False
            .Title = "Select a folder for this timesheet"
            If .Show Then
                'Get the user's choise
                strPath = .SelectedItems(1)
            Else
                'User did not select any folder
            End If
        End With
        Set FD = Nothing
    
        If Len(strPath) Then
            'Build the file name of the spreadsheet
            PathFile = strcDocName & Format(Me.txtTimesheetID, "00000") & ".xlsx"
            'Build the full file name of the spreadsheet
            PathFile = Replace(strPath & "\" & PathFile, "\\", "\")
            'Export to the spreadsheet
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strcDocName, PathFile, True
            'Start Excel automation
            Set Exc = New Excel.Application
            On Error Resume Next
            'Try to open the spreadsheet created
            Set wb = Exc.Workbooks.Open(PathFile)
            If Err = 0 Then
                On Error Resume Next
                'Try to make a pointer to the worksheet "Timesheet"
                Set sh = wb.Worksheets(strcDocName)
                If Err = 0 Then
                    With sh.ListObjects
                        On Error Resume Next
                        .Item("Table1").Unlist  'Remove the "Table1" table if exists
                        On Error GoTo ShowExcel    'In any case, we have a workbook open
                        'Try to add the table "Table1" and format it
                        With .Add(xlSrcRange, sh.Range("$A$1:$J$50"), , xlYes, , "TableStyleMedium2")
                            .Name = "Table1"
                            With .HeaderRowRange.Interior
                                .Pattern = xlSolid
                                .PatternColorIndex = xlAutomatic
                                .ThemeColor = xlThemeColorAccent4
                                .TintAndShade = 0.399975585192419
                                .PatternTintAndShade = 0
                            End With
                        End With
                    End With
    ShowExcel:
                    If Err <> 0 Then
                        'Just inform the user for the error
                        MsgBox Err.Description, vbExclamation, "Range formatting error(" & Err & ")"
                    End If
                    'Show spreadsheet created
                    Exc.Visible = True
                Else
                    'Spreadsheet "Timesheet" not found!
                    MsgBox Err.Description, vbExclamation, "Pick spreadsheet error(" & Err & ")"
                End If
            Else
                'Unable to open the workbook!
                MsgBox Err.Description, vbExclamation, "Open workbook error(" & Err & ")"
            End If
        End If
        Set sh = Nothing
        Set wb = Nothing
        Set Exc = Nothing
    End Sub
    Hope this helps.

    Cheers,
    John

  9. #9
    RasterImage is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jun 2011
    Location
    UK
    Posts
    34
    John

    That is a more thorough response then I ever expected to get, I feel blessed. I plugged in all of the above and it just - works. Amazing. No errors, no bugs, just exactly what I want. I'm so impressed.

    I have loads more formatting that I didn't bother posting here. That didn't work with your code right away, but following all your suggestions and advice I got it all working, and doubtless more robust then it was before. You've taught me some useful things.

    This means that I'm nearly ready to distribute my thing to all the colleagues, it's going to save everyone a lot of time, just make office life a little bit easier and nicer for plenty of people. Thank you.
    Last edited by RasterImage; 11-19-2020 at 02:16 PM. Reason: Add a comment.

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    That's why I love technology and automation, because makes the life a little bit easier and nicer for plenty of people.
    A few lines are enough for someone to realize that you are very kind person.

    You are very welcome!

    Cheers,
    John

Please reply to this thread with any new information or opinions.

Similar Threads

  1. proplem exporting a query to excel with formatting
    By sdel_nevo in forum Programming
    Replies: 7
    Last Post: 03-01-2018, 03:21 AM
  2. Replies: 8
    Last Post: 02-15-2018, 12:10 AM
  3. Exporting Query to Excel Conditional Formatting
    By RobotronX in forum Programming
    Replies: 5
    Last Post: 09-07-2016, 04:38 PM
  4. Replies: 1
    Last Post: 02-19-2014, 11:26 AM
  5. Exporting To Excel And Formatting, Office 2010
    By DazJWood in forum Programming
    Replies: 2
    Last Post: 11-23-2011, 08:35 AM

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
  •  
Other Forums: Microsoft Office Forums