Find application directory path in VBA workable for both MS Access and MS Excel

user_8552345 0
Viewed 325

I have a VBA code, that may be run either from MS Access or Excel application.
In a part of the code (shown below), I have to find the application file directory as default, to save a CSV file.
The code runs in Excel without any error, but in MS Access, the compiler complains about <Application.ThisWorkbook.Path> and stops there.
what is wrong with the code? I there any other way to find the file path workable for both MS Access and Excel??

If CSVpath = vbNullString Then
    Select Case Application.Name  '' system in which the code is running from
        Case Is = "Microsoft Excel"  '' it raises an error in MS Access
            CSVpath = Application.ThisWorkbook.Path + "\DataSet_" + CStr(Format(Now(), "YYYY-MM-DD.HH.MM.SS")) + ".csv"
        Case Is = "Microsoft Access"
            CSVpath = Application.CurrentProject.Path + "\DataSet_" + CStr(Format(Now(), "YYYY-MM-DD.HH.MM.SS")) + ".csv"
        Case Else
            CSVpath = ""
    End Select
End If
ms-access vba excel
2 Answers

I would try it like this, casting the Application's main document object using a string variable.

This way, if you change your mind later about the filename, you will only have to change it in one place.

Also, I added a check for the possibility that the file has not yet been saved (it wouldn't have a path then)

Sub Test()
    Dim DocObjName As String
    Dim DocPath As String
    Dim CSVFullFilename As String

    Select Case Application.Name
        Case "Microsoft Excel"
            DocObjName = "ThisWorkbook"
        Case "Microsoft Access"
            DocObjName = "CurrentProject"
        Case "Microsoft Word"
            DocObjName = "ActiveDocument"
        Case "Microsoft PowerPoint"
            DocObjName = "ActivePresentation"
        Case Else
            ' if it stops here, you know you have made a programming error to fix
            ' add another case based on the application you are using?
            ' do not just set it to an empty string and let it continue
            Stop
            Exit Sub
    End Select
    
    DocPath = CallByName(Application, DocObjName, VbGet).Path
    If Len(DocPath) = 0 Then
        MsgBox "Error: File has not yet been saved, so there is no path to use", vbCritical
        Exit Sub
    Else
        CSVFullFilename = DocPath & "\DataSet_" & Format(Now(), "YYYY-MM-DD.HH.MM.SS") & ".csv"
    End If
    
    Debug.Print CSVFullFilename
End Sub

And just for the sake of good variable naming, I would use something like CSVFullFilename instead of CSVpath since it's more than just a path - But that's just personal preference.

user_84229530

For MS-Access try:

Application.StartupPath & "\your resource  database name"
  1. Application.StartupPath will give you the location of your executable(.exe) file.
  2. & "\your resource database name " is the name of your file. Make sure you place it in the same folder as .exe
user_59445700
Related