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
Microsoft OfficeMicrosoft ExcelVBA
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()
    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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
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