PDA

View Full Version : Solved: VBA to find path for multiple files



sljanbu
11-03-2011, 02:23 PM
Hello.

I hope someone can help me with the VBA-code that will solve my request. I've spent some time searhing the net to find the VBA-code for this, but have not succeeded findig it.

In Excel I have my filenames listed in column A starting in cell A1 and downwards. Example:
filename1.txt
filename2.txt
filename3.txt
etc.

These files are listed in one of many subfolders starting below path C:\MyFiles\2011, e.g.:

C:\Myfiles\2011\01
C:\Myfiles\2011\02
C:\Myfiles\2011\03
C:\Myfiles\2011\04
ect.

I need a VBA-code that searches through C:\MyFiles\2011 an all its subfolders to find the complete path for each file listed in cell A1 and downwards.

The result for each file I want to appear in cell B1 and downwards.
An example of the final result should look like this:

Column A ------- Column B
filename1.txt --- C:\Myfiles\2011\02
filename2.txt --- C:\Myfiles\2011\05
filename3.txt --- C:\Myfiles\2011\05
filename4.txt --- C:\Myfiles\2011\01
filename5.txt --- C:\Myfiles\2011\03
ect. ----------- etc.

I'm a complete novice in VBA so I have not started on building any VBA-code myselves to solve my problem.

Best regards,
Stig

Kenneth Hobs
11-03-2011, 04:01 PM
And what if filename1.txt is in more than one of the subfolders?

mohanvijay
11-03-2011, 08:19 PM
Try this




Dim O_FYS As Object
Dim O_Fol As Object
Dim O_SubFol As Object
Dim O_File As Object
Dim Main_Fol As String
Dim Temp_Str As String
Dim i As Long

i = 1
Main_Fol = "C:\Myfiles\2011"

Set O_FYS = CreateObject("Scripting.FileSystemObject")
Set O_Fol = O_FYS.getfolder(Main_Fol)

For Each O_SubFol In O_Fol.subfolders
Temp_Str = O_SubFol.Name
For Each O_File In O_SubFol.Files
Cells(i, 1).Value = O_File.Name
Cells(i, 2).Value = Temp_Str
i = i + 1
Next
Next

Set O_File = Nothing
Set O_SubFol = Nothing
Set O_Fol = Nothing
Set O_FYS = Nothing

sljanbu
11-04-2011, 12:20 AM
And what if filename1.txt is in more than one of the subfolders?

Hello Kenneth,

and thanks for taking interest in my problem.
Sorry for not thinking about this, but each file listed in
column A is unique.

Regards,
Stig

sljanbu
11-04-2011, 12:35 AM
Hello Mohanvijay,

and thanks for showing interest in my problem.
Your code is not the solution because it does not take
into consideration the filenames listed in column A.

Regards,
Stig

mohanvijay
11-04-2011, 01:31 AM
if i understand correctly this will help



Dim O_FYS As Object
Dim O_Fol As Object
Dim O_SubFol As Object
Dim Main_Fol As String
Dim Temp_Str As String
Dim i As Long
Dim Last_Row As Long
Dim T_Col As Integer
Last_Row = Cells(Rows.Count, 1).End(xlUp).Row
Main_Fol = "C:\Myfiles\2011"
Set O_FYS = CreateObject("Scripting.FileSystemObject")
Set O_Fol = O_FYS.getfolder(Main_Fol)
For i = 1 To Last_Row
For Each O_SubFol In O_Fol.subfolders
Temp_Str = O_SubFol.Path & "\" & Cells(i, 1).Value
If O_FYS.FileExists(Temp_Str) = True Then
T_Col = Cells(i, Columns.Count).End(xlToLeft).Column + 1
Cells(i, T_Col).Value = O_SubFol.Path
End If
Next
Next i
Set O_SubFol = Nothing
Set O_Fol = Nothing
Set O_FYS = Nothing

sljanbu
11-04-2011, 06:33 AM
Hello again, Mohanvijay!

The last code you posted works perfect!!
I'm grateful. Thank you very much.

Best regards,
Stig