locked
Excel 2007 macros called from Access do not work correctly in 2013 RRS feed

  • Question

  • Developed project in Access 2002-03. Access VBA creates two spreadsheets, cleans up spreadsheets' formatting by calling Excel macros, which also moves the sheet of one into the other. Afterward, Access VBA moves/renames the merged file and kills the extra file. This all worked rather smoothly in earlier versions of Office products; however, upon bringing the files into Office 2013, the calls to the Excel macros apparently gets through, but some of the code fails, preventing the temporary interim spreadsheet cleanups.

    An example failed Excel macro (note: VBA not on the list of languages for insertion):

    Sub CleanUpBasicWorkshopParticipantsReport()
        
    On Error GoTo Err_CleanUpBasicWorkshopParticipantsReport
    
        ' CTRL + T (includes DougPath_SaveBasicWorkshopParticipantsRptAs below)
        
        Rows("1:1").Select
        Range("B1").Activate
        Selection.Delete Shift:=xlUp
        
        Dim C, R, CR, ThisWksp As String
        Dim UserReply As Integer
        Dim ToRow, LastRow As Long
        Dim MyLeft, MyTop, MyHeight, MyWidth As Double
        Dim i As Integer
        
        LastRow = Range("D65536").End(xlUp).Row
        
        Range("1:1").Font.Size = 10
        ActiveSheet.Range("1:1").Font.Bold = True
        Range("1:1").Font.ColorIndex = 5
        Cells.Select
        Cells.EntireColumn.AutoFit
        
        ' REMOVE ADDED APOSTROPHE FROM DATE FIELDS (added in Access re Export bug)
        i = 1
        Do Until (i = ActiveSheet.UsedRange.Rows.Count)
            i = i + 1
            C = "E"
            R = Format(i, "0")
            CR = C & R
            ThisWksp = Range(CR).Value
            If (Len(ThisWksp) = 8) Then
                Range(CR).Select
                ActiveCell.FormulaR1C1 = Left(ThisWksp, 7)
            End If
            If (ThisWksp = "'") Then
                ThisWksp = ""
                Range(CR).Select
                ActiveCell.FormulaR1C1 = ThisWksp
            End If
        Loop
        importfacilitators
        Sheets("Participants").Select
        ActiveWorkbook.Save ' save without prompt or saveas
    
    
    Exit_CleanUpBasicWorkshopParticipantsReport:
        Exit Sub
    
    Err_CleanUpBasicWorkshopParticipantsReport:
        MsgBox Err.Description
        Resume Exit_CleanUpBasicWorkshopParticipantsReport
        
    End Sub

    The above cleanup routine fails. Tests using a simple message box indicates execution does not occur inside the Do Until loop.  The following macro, called by the above macro, moves the sheet from spreadsheet two to spreadsheet one, indicating the macro above is actually executing. Since these both worked before, apparently there is some syntax change I'm unaware of?

    Sub importfacilitators()
    '
    ' importfacilitators Macro
    '
    ' Keyboard Shortcut: Ctrl+k
    '
    
    ' Move Facilitators sheet to Participants workbook
    ' see also access version...may use that instead
        Windows("CurrentWorkshopsFacilitators.xls").Activate
        Sheets("Facilitators").Select
        Sheets("Facilitators").Move After:=Workbooks( _
            "CurrentWorkshopsParticipants.xls").Sheets(1)
        Sheets("Participants").Select
    End Sub
    I'm attempting to complete this on an unexpected deadline (done by end of tomorrow, Monday, March 21, 2016)...so any quick assistance would be much appreciated. The project is (again, unexpectedly) to be demo'ed at a statewide annual conference/gathering up-state (Calif) for a nonprofit organization I'm part of around Wednesday-ish, with travel in between.

    Any ideas or suggestions?

    Thanks, Doug


    Doug


    • Edited by Doug Couch Monday, March 21, 2016 12:12 AM typos
    Monday, March 21, 2016 12:09 AM

Answers

  • Yes, George...that's what's happening...on both machines. It has to be something about these upgrade Office 2013 installations and their interaction with Windows 10 (which is a native Windows on one machine, and an upgrade from Windows 7 on the other). Your advice is very sound. I will do that as I find time.

    On a more positive note: Without the aid of Access's debugger, I was finally able to narrow the non-functionality down to certain fragments of code. These had primarily to do with variations of Activate to bring Excel windows to the top for macro execution (earlier, before I posted), and the Environ("USERPROFILE") code used for establishing destination paths for rename/move of spreadsheets. The latter didn't simply fail to do its job, but would cause File Not Found errors. Odd, because that code is about destination, not finding a file. It was used in conjunction with the Name command. Anyway, I found the new workable code solution less than an hour ago. Now I get to go through the project and use the newer coding in the various instances where it is needed (as there are a number of setups doing a similar process for each workshop type). Later, I would do well to try to consolidate these into a single routine. For now, I just needed to get it up and running again. Then onward into whatever lies ahead in moving from .mdb to .accdb (so far, there doesn't seem to be much difference for the type of coding I've been using).

    Thanks for looking over my shoulder awhile.


    Doug

    • Marked as answer by Doug Couch Tuesday, March 22, 2016 6:26 AM
    Tuesday, March 22, 2016 6:26 AM

All replies

  • Supplemental info:

    • This is a new upgrade installation of Office 2013 in native Windows 10 Home 64-bit.
    • The debugger in Access stopped working. It will not step into code.
    • The debugger in Excel works just fine.
    • I did a repair on Office 2013 since the original post, with no change in the Excel VBA code operation or debugger functionality.
    • Interestingly, if instead of accessing the Excel macros from Access, I just run the cleanup macro portion using a hot-key, it runs okay and does the sheet cleanup. But when I call it from Access, MsgBox testing proves the macro does run, but then doesn't do the cleanup process it used to do in earlier versions of Access and Excel.
    • Ah the joys of being a wanna be programmer.
     

    Doug

    Monday, March 21, 2016 9:03 AM
  • What happens if you comment out this line and run?

    On Error GoTo Err_CleanUpBasicWorkshopParticipantsReport


    Best regards George

    Monday, March 21, 2016 10:50 AM
  • Thanks, George...I'd forgotten that trick. >> Commenting the On Error... line resulted in identical behavior. I took the pertinent files to my other PC and tried them there...with the same results when these macros are operated via call from Access.

    Since Excel's VBA editor and debugger works, I stepped through the macros set for the first spreadsheet (CurrentWorkshopsFacilitators.xls), and all lines executed normally, resulting in a cleaned up and saved file. (This file has the sheet which later gets moved to the second spreadsheet.) This process uses the first spreadsheet and its corresponding macro file (which is also an .xls file).

    Leaving the first spreadsheet open, the first macro file is closed, and the second spreadsheet and its macro file opened. I stepped through the macros set for the second spreadsheet (CurrentWorkshopsParticipants.xls), and all lines executed normally (including those not working during normal Access-call processing), resulting in a cleaned up file. The last macro in the set executes normally with one exception: After moving the Facilitators sheet from the first spreadsheet into the second spreadsheet, per code instructions it selects the second sheet and highlights the last date in that sheet (text formatted to represent a date as 2020-12 [year-mo]) and throws a "Subscript out of range" error. I know from using this routine in previous versions that the subscript should be in range. So something has changed other than the code.

    Although this error is noted, during normal processing via call from Access, this error is not popping up. Further, at this point, both spreadsheets' sheets have been cleaned up, with the second's sheet moved into the first. The first spreadsheet should already be saved with its cleaned up appearance (via ActiveWorkbook.Save, at the end of its macro...not shown in the original post as it has repetitive blocks of code and is loooong)...and in fact, if I run that macro manually, the first spreadsheet is indeed saved. So if the "Subscript out of range" error stopped the remainder of processing from happening, and the second spreadsheet didn't get saved with its cleaned up appearance...I would expect at least the first spreadsheet to have been cleaned up. Problem is, it is still sitting there in its pre-processed state just as it existed when created by Access VBA in the first place...whether the second spreadsheet with its copy of the first's sheet got saved or not.

    My guess is that there is something wrong with the Office 2013 installations on both computers (and not necessarily the same, as one was upgraded over 2002 and the other over 2003). The Access debugger not working is possible only one anomaly among several. I created a new .mdb and imported the objects, but there was no change. As previously noted, repairing Office 2013 did not resolve anything. At various points, Access's Compact & Repair was used, also not affecting anything.

    Hey...sometimes I just don't get to meet deadlines and make everyone happy. LOL

    I was hoping to resolve this before shifting over to using a version with .accdb and .xlsx files, but it appears it isn't going to go down that way.

    Thanks for giving it a look. I do realize it's very difficult to ferret out what's going on when two applications and several files are involved...and not in front of you to peek at. I never thought this project was ready for a demo anyway. It needs a revamp by someone who knows what they're doing (and there's no budget to do such a thing). The primary things this project needs to accomplish are getting done. This cleanup, move and rename stuff is only the tail end of some cobbled together automation to make the whole process easier to follow and not miss things.


    Doug

    Monday, March 21, 2016 9:27 PM
  • Do you mean that debugging in Access fails on two machines?

    You really need to fix that first of all. Whatever it is causing this can affect a lot of other things that is normally unrelated.

    Create a new small file with a Messagebox in Excel VBA and try to fire it from Access VBA. If that won't work then you find the reason there and the big Picture might change.

    IF, on the other hand, the small test Project work (Access debug) you add code from your large cleanup Project until it stops ... you get the picture. I really think that's the way to handle this.
    Every part in your Office must work ok before searching for bugs in your own code.


    Best regards George

    Tuesday, March 22, 2016 6:06 AM
  • Yes, George...that's what's happening...on both machines. It has to be something about these upgrade Office 2013 installations and their interaction with Windows 10 (which is a native Windows on one machine, and an upgrade from Windows 7 on the other). Your advice is very sound. I will do that as I find time.

    On a more positive note: Without the aid of Access's debugger, I was finally able to narrow the non-functionality down to certain fragments of code. These had primarily to do with variations of Activate to bring Excel windows to the top for macro execution (earlier, before I posted), and the Environ("USERPROFILE") code used for establishing destination paths for rename/move of spreadsheets. The latter didn't simply fail to do its job, but would cause File Not Found errors. Odd, because that code is about destination, not finding a file. It was used in conjunction with the Name command. Anyway, I found the new workable code solution less than an hour ago. Now I get to go through the project and use the newer coding in the various instances where it is needed (as there are a number of setups doing a similar process for each workshop type). Later, I would do well to try to consolidate these into a single routine. For now, I just needed to get it up and running again. Then onward into whatever lies ahead in moving from .mdb to .accdb (so far, there doesn't seem to be much difference for the type of coding I've been using).

    Thanks for looking over my shoulder awhile.


    Doug

    • Marked as answer by Doug Couch Tuesday, March 22, 2016 6:26 AM
    Tuesday, March 22, 2016 6:26 AM