VBA for file names!

[Daghs]

Hi,
I'm at beginning level I don't know if it's possible or not with the help of vba macro, I got a folder named "D:\WORK" which usually has couple of thousand ".csv" files and I got a file named "D:\Job.xlsm" what I need is whatever file is in the folder "work" macro prints the name of the file in column A of Job.xlsm now if the folder contains 5 files macro would print 5 files names in column A, if it contains 5000 macro would print all 5000 names of the files in column A. I'm using excel 2010 if anybody could assist it'd be a great help thanks!

 

[PeteWright]

It can be done with VBA, follow below steps to get the desired result:

1. In MS Excel press [Alt]+[F11] to open the VBA Editor.
2. In the VBA Editor menu navigate to Tools > References...
3. In the References window navigate to Microsoft Scripting Runtime, mark the checkbox on the left and confirm with [OK]
4. Back in the VBA Editor again create a new module and paste the following code:

Public Sub ListAllFiles()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.ActiveSheet
    Dim lastRow As Long
    Dim maxRows As Long
    maxRows = Application.Rows.Count
  
    Dim fso As New FileSystemObject
    Dim fol As Folder
    Dim fil As File
    Dim fStr As String
  
    fStr = "D:\WORK"
    Set fol = fso.getFolder(fStr)
  
    ws.Range(cells(1, 1), cells(maxRows, 1)).Clear
  
    For Each fil In fol.Files
        lastRow = ws.Range("A" & maxRows).End(xlUp).Row + 1
        ws.Range("A" & lastRow).Value = fil.Name
    Next
  
    Range("A1").Value2 = fStr
End Sub

Now you can run the macro via menu or even create a button to start it.

If you need any further assistance, just ask.

 

[Fluff]

Another option

Sub Daghs()
   Dim Pth As String, Fname As String
   Dim i As Long
   
   i = 1
   Pth = "D:\Works\"
   Fname = Dir(Pth & "*")
   Do While Fname <> ""
      i = i + 1
      Range("A" & i).Value = Fname
      Fname = Dir
   Loop
End Sub

 

[Daghs]

Thanks for the help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Daghs]

Glad we could help & thanks for the feedback.

Hi,
I wanna ask a little more about it!
what if I already have a list of files in column "A" assuming "Job.xlsm" & I got a folder named "D:\WORK" which contains thousands of files, I have one more FOLDER named "D:\ADDITIONAL-WORK" now I want to move (not copy) all the files which are in the Job.xlsm (as list) from "WORK" folder TO "ADDITIONAL-WORK" (by leaving rest of the files) so the same file wouldn't exist twice if you could suggest something more.
I think it is similar logic but different task and title of the thread fits so I'm posting the question here!
Thanks,

 

[Fluff]

As this is a totally different question it needs a new thread. Thanks