2

From MS Access I am generating several MS Access Workbooks. Via the following code I am getting the desired save location for all of the workbooks. The following code was working without issues a few days ago. Now it abruptly fails with no error number. MS Access crashes and I get a prompt to restart MS Access and a backup file is automatically created of the MS Access project I am working on.

Strangely the code works fine if I step through it with the debugger. It simply is not working at full speed.

UPDATE 1: If I do the falling the save_location call works.

Private Sub make_report()
' TODO#: Change to late binding when working
    Dim strSaveLocation as string
    Dim xl as Excel.Application
    dim wb as Excel.Workbook

    strSaveLocation = save_location("G:\Group2\Dev\z_report")

    Set xl=New Excel.Application
        ' do workbook stuff
        With xl
            strSaveLocation = strSaveLocation & "\report_name.xlsx"
            wb.SaveAs strSavelLocation, xlOpenXMLWorkbook
        End With    ' xl
    Set xl=nothing
End Sub

If I call the save_location function like this it abruptly crashes MS Access. It doesn't throw an error or anything. It just crashes.

Private Sub make_report()
' TODO#: Change to late binding when working
    Dim strSaveLocation as string
    Dim xl as Excel.Application
    dim wb as Excel.Workbook

    Set xl=New Excel.Application
        ' do workbook stuff
        With xl
            ' the call to save_location is inside of the xl procedure
            strSaveLocation = save_location("G:\Group2\Dev\z_report")
            strSaveLocation = strSaveLocation & "\report_name.xlsx"

            wb.SaveAs strSavelLocation, xlOpenXMLWorkbook
        End With    ' xl
    Set xl=nothing
End Sub

By moving the save_location call inside the Excel.Application work string it fails. I don't understand why.

Private Function save_location(Optional ByVal initialDir As String) As String
On Error GoTo err_trap
    Dim fDialog As Object
    Dim blMatchIniDir As Boolean
    Set fDialog = Application.FileDialog(4)  ' msoFileDialogFolderPicker
        With fDialog
            .Title = "Select Save Location"
            If NOT (initialDir=vbnullstring) then
                .InitialFileName = initialDir
            End If
            If .Show = -1 Then
                ' item selected
                save_location = .SelectedItems(1)
            End If
        End With
    Set fDialog = Nothing
exit_function:
    Exit Function
err_trap:
    Select Case Err.Number
        Case Else
            Debug.Print Err.Number, Err.Description
            Stop
            Resume
    End Select
End Function
  • Actions tried:
    • Decompile project and recompile
    • Create new MS Access project and import all objects
    • Compact and repair
    • Reset all reference
  • Notes:
    • I am using the client's system and
    • I don't know of any system updates
    • Client's system is a virtual desktop via VMWare
    • Office 2013
    • Windows 7 Pro
acr_scout
  • 569
  • 1
  • 4
  • 24
  • Seems like you're using late bindings, but are using the `MsoFileDialogType` enum. Try replacing `msoFileDialogFolderPicker` with `4` (the literal value). That will make the code not depend on the proper references. – Erik A Sep 12 '17 at 14:16
  • @ErikvonAsmuth I have replaced `msoFileDialogFolderPicker` with `4` and it is still abruptly failing. This is a great catch though and I appreciate it. – acr_scout Sep 12 '17 at 14:22
  • can you print `initaldir` and verify it's valid? – Erik A Sep 12 '17 at 14:23
  • If all else fails and the client gets really impatient, you could switch to an API based method: http://www.cpearson.com/excel/browsefolder.aspx (yes, it's a bit more code than your existing function...) – Andre Sep 12 '17 at 14:36
  • @ErikvonAsmuth the `initalDir` is a `const` set to G:\Group4\Dev\z_reports for testing and the code works if I step through it with the debugger but fails in full speed. – acr_scout Sep 12 '17 at 14:55
  • @Andre I am a little leery about the API stuff because the client is working on transitioning to Win 10 in the VM environment and I am not sure how that will impact the project. Could be my ignorance of how APIs work. – acr_scout Sep 12 '17 at 14:58
  • But... You're passing `initialDir` to the function. Could you print it using a `msgbox` before the `Application.FileDialog`? By inserting `MsgBox 1` after each line, you can find out which line causes the crash. – Erik A Sep 12 '17 at 15:06
  • It (API) sure is a last resort. Win 10 won't affect it, only if 64bit Access is used, some changes will be needed, see https://stackoverflow.com/questions/3072356/what-are-the-differences-between-vba-6-0-and-vba-7-0 – Andre Sep 12 '17 at 15:07
  • @ErikvonAsmuth the calling procedure passes in the `G:\Group4\Dev\z_reports` like this `strSaveLocation=save_location(INITIAL_DIR)` as soon as it hits that line in the calling procedure MS Access quits. If I step through the code from the calling procedure everything works fine. The `INITIAL_DIR` value is passed, the dialog opens fine, it retrieves the save location fine. But if you let it go full speed it crashes. I tried to put a `MsgBox` as the first line and it does not even execute that when it is running full speed. – acr_scout Sep 12 '17 at 15:31
  • That's really strange. Try setting that dir directly in the function, instead of passing it as a parameter. You could also go for Andre's solution. And try just using the C-drive as the location. – Erik A Sep 12 '17 at 15:36
  • I added more information of what I found. Basically the `save_location` call will not work inside the xl.workbook procedure string. I don't know why not. I suppose it could have something to do with the xl.application being hidden as it generates workbooks. – acr_scout Sep 12 '17 at 19:21

1 Answers1

0

while i am not sure if this is the specific problem - but if it is the case, it messes with anything VBA. Check the folder names and file names for any apostrophes. While windows allows this, an apostrophe will be seen in VBA as a comment, and will crash it. Have the client walk you through the exact file that he selects to confirm there is no apostrophe character in the filename or folder name.

Destin Consulting
  • 86
  • 5