Solved

List Files of a given directory in current worksheet using VBA

Posted on 2013-06-12
3
407 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

861 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