Solved

List Files of a given directory in current worksheet using VBA

Posted on 2013-06-12
3
405 Views
Last Modified: 2013-06-12
Dear Experts:

the following macro lists all files from a given directory in Column A of the current excel worksheet.

Just the file names are listed. How is the code to be re-written if I would like to have the whole path listed?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Sub List_Files()
'List Files from a given directory in Column A of current excel worksheet
Dim MyFolder As String
Dim MyFile As String
Dim a As Integer
MyFolder = "C:\Test\" ' <-- Change to your folder
MyFile = Dir(MyFolder & "*.*")
a = 0
Do While MyFile <> ""
    a = a + 1
    Cells(a, 1).Value = MyFile
    MyFile = Dir
Loop
MsgBox "Done."
End Sub

Open in new window

0
Comment
Question by:AndreasHermle
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 480 total points
ID: 39242633
Sub List_Files()
'List Files from a given directory in Column A of current excel worksheet
Dim MyFolder As String
Dim MyFile As String
Dim a As Integer
MyFolder = "C:\Test\" ' <-- Change to your folder
MyFile = Dir(MyFolder & "*.*")
a = 0
Do While MyFile <> ""
    a = a + 1
    Cells(a, 1).Value = MyFolder  & MyFile
    MyFile = Dir
Loop
MsgBox "Done."
End Sub

Kevin
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 20 total points
ID: 39242665
Andreas,

No points, please :)

You may want to check this out as well:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25063189.html#a26340696

Patrick
0
 

Author Closing Comment

by:AndreasHermle
ID: 39242704
Dear both, thank you very much for your great help.

Regards, Andreas

Patrick: 20 points are almost none!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now