Returning Path of Files

jaynorman46

New Member
Joined
Apr 5, 2011
Messages
23
Hi,

I have over 15,000 files on a file share. I am looking to try and extract the path for each individual one and paste it in a long list on a sheet with its associated folder. I'm having a hard time of where to start. the most difficult part is there are thousands of folders and different types of files as well. I have 35 master folders with sub folders within.



My eventual goal is to create an easy to access index on an excel sheet. Once I have all the Paths I can create Hyperlinks to the exact file for one click viewing.


Is this even possible?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Maybe a start?

Code:
Sub ListFiles2()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    MyFolder = .SelectedItems(1)
End With
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub
 

jaynorman46

New Member
Joined
Apr 5, 2011
Messages
23
Thanks for replying!

just for clarification I need to give myfolder a value? and can I do this with .pdf and .jpg?
 

jaynorman46

New Member
Joined
Apr 5, 2011
Messages
23
Ok, I see what the program you wrote does now. I dont want to be prompted for each folder. I have 1,463 folders to do this with is there anyway that this step could be automatic?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
You could create a list of folders on a sheet then loop through those values.

There is no 'magic' solution as far as I know.
 

Forum statistics

Threads
1,084,735
Messages
5,379,509
Members
401,608
Latest member
nanana2020

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top