troubleshooting Question

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

Avatar of Andreas Hermle
Andreas HermleFlag for Germany asked on
VBAMicrosoft ExcelMicrosoft Office
7 Comments1 Solution86 ViewsLast Modified:
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()
    For lngRow = 2 To lngLastRow
        strFile = LoopThroughFiles(strRoot, .Cells(lngRow, "A"))
        If Len(strFile) > 0 Then
            .Cells(lngRow, "B") = strFile
            .Cells(lngRow, "B") = "Not found"
        End If
End With

Application.ScreenUpdating = True
End Sub

Open in new window

NorieSenior Associate
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros