Avatar of Andreas Hermle
Andreas Hermle
Flag for Germany

asked on 

hardcoded range is to be replaced by inputbox prompt that allows a different selection of a range (column)

Dear Experts:

below macro, courtesy of Martin Liss retrieves the full path of a file name. The macro works great.

I need to tweak it a little bit, especially the hardcoded ranges (it was my initial requirement) should be replaced by inputboxes, where the user can choose which column the file name is in.

So the tweaking should occur on line 14: The user should be prompted to enter a column number (A To Z) where the filenames reside. Now it is hardcoded to Column A
The next tweaking should occur on line 28: It could be any column number (A-Z) actually.
The next tweaking is to occur on line 30 and 32: this is always the immediate column to the right of the initial column chosen (input box prompt)

I hope I could make myself clear.

Thank you very much for your great and professional help in advance.

Regards, Andreas

Sub GetFullPath()
'the best one
Dim lngRow As Long
Dim lngLastRow As Long
Dim strFile As String
Dim strRoot As String
Dim strCurrPath As String

Application.ScreenUpdating = False



With ThisWorkbook.ActiveSheet
    lngLastRow = .Range("A1048576").End(xlUp).Row
    strRoot = "G:\KLS_Allgemein\DMD\"
    
    ' Collect the subdirectories of the root
    strCurrPath = Dir(strRoot, vbDirectory)
    Do Until strCurrPath = vbNullString
        If Left(strCurrPath, 1) <> "." And _
            (GetAttr(strRoot & strCurrPath) And vbDirectory) = vbDirectory Then
            mcolDirectories.Add strCurrPath
        End If
        strCurrPath = Dir()
    Loop
    
    For lngRow = 2 To lngLastRow
        strFile = LoopThroughFiles(strRoot, .Cells(lngRow, "A"))
        If Len(strFile) > 0 Then
            .Cells(lngRow, "B") = strFile
        Else
            .Cells(lngRow, "B") = "Not found"
        End If
    Next
End With

Application.ScreenUpdating = True
End Sub

Open in new window

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Andreas Hermle

8/22/2022 - Mon