Link to home
Start Free TrialLog in
Avatar of Billa7
Billa7

asked on

Copy Title

Hi Experts,

I need Experts help. I have 1000 over files in the External Drive (G:\) and I need to extract those files' name and copied into excel sheet (Column A). Hope Experts could help me create a macro which is able to do this.
Title.xlsx
SOLUTION
Avatar of Marc Z
Marc Z
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sub ListDirectory() 
     
    Dim oFSO As Object 
    Dim oFolder As Object 
    Dim oFile As Object 
    Dim ws As Worksheet 
     
    Set oFSO = CreateObject("Scripting.FileSystemObject") 
    Set ws = Worksheets.Add 
    Set oFolder = oFSO.GetFolder("G:\") 
        
    For Each oFile In oFolder.Files 
        ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = oFile.Name 
    Next 

    Set oFolder = Nothing 
    Set oFile = Nothing 
    Set oFSO = Nothing 
     
End Sub 

Open in new window

Avatar of Billa7
Billa7

ASKER

Hi Jornak,

I have tested your code, it's not populating the file titles at column A. It's just copied a single file name only.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Billa7

ASKER

Hi,

Thanks for the help.