Change Path "Const as String" to Variable

Question

I want to generate a list of files - including properties - in a folder. The Excel file with the macro will be in the same folder as the files it works with.

Problem is the Excel file and all other files will be synced between different Windows computers, so the folder path of the attached macro must be relative because it's different on every machine.

Const STRFOLDER As String = "D:\GIS-Projekte_Sync\"

Tried ideas (like "\" or "..\" etc.), searched forums.

Complete script:

Public Sub Auto_Open()

    Const STRFOLDER As String = "D:\GIS-Projekte_Sync\"
    Dim objShell As Object, objFolder As Object
    Dim bytIndex As Byte, intColumn As Integer, lngRow As Long
    Dim varName, arrHeaders(37)
    If Dir(STRFOLDER, 16) = "" Then
        MsgBox "Der Ordner " & STRFOLDER & " wurde nicht gefunden!", 64, "Hinweis"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(STRFOLDER)
    intColumn = 1
    For bytIndex = 0 To 37
        arrHeaders(bytIndex) = objFolder.GetDetailsOf(varName, bytIndex)
        Cells(1, intColumn + bytIndex) = arrHeaders(bytIndex)
    Next
    Rows(1).Font.Bold = True
    lngRow = 2
    For Each varName In objFolder.Items
        For bytIndex = 0 To 37
            Cells(lngRow, intColumn + bytIndex) = objFolder.GetDetailsOf(varName, bytIndex)
        Next
        lngRow = lngRow + 1
    Next
    Columns.AutoFit
    Set objShell = Nothing
    Set objFolder = Nothing
    Application.ScreenUpdating = True
End Sub

Answer 1

To answer my own question: You have to define STRFOLDER as Variant. Then you can use "ThisWorkbook.path" to get the folder location.

Here the complete macro:

Public Sub Auto_Open()
Dim STRFOLDER As Variant
Dim objShell As Object, objFolder As Object
Dim bytIndex As Byte, intColumn As Integer, lngRow As Long
Dim varName, arrHeaders(37)
STRFOLDER = ThisWorkbook.path & "\"
If Dir(STRFOLDER, 16) = "" Then
    MsgBox "Der Ordner " & STRFOLDER & " wurde nicht gefunden!", 64, "Hinweis"
    Exit Sub
End If
Application.ScreenUpdating = False
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(STRFOLDER)
intColumn = 1
For bytIndex = 0 To 37
    arrHeaders(bytIndex) = objFolder.GetDetailsOf(varName, bytIndex)
    Cells(1, intColumn + bytIndex) = arrHeaders(bytIndex)
Next
Rows(1).Font.Bold = True
lngRow = 2
For Each varName In objFolder.Items
    For bytIndex = 0 To 37
        Cells(lngRow, intColumn + bytIndex) = objFolder.GetDetailsOf(varName, bytIndex)
    Next
    lngRow = lngRow + 1
Next
Columns.AutoFit
Set objShell = Nothing
Set objFolder = Nothing
Application.ScreenUpdating = True

End Sub

Answer 2

ThisWorkbook.Path gives you the path for the current file so you can use that if relative paths are not working for you. You cannot however use CONST with the path, you will have to use a standard variable.

The other thing you need to remember is that paths in VBA may now be URI's and this doesn't always play nicely with the older parts of the code such as DIR. For example, if the file comes from Office 365 (e.g. OneDrive for Business), the path will be a URL and DIR will fail.