List of Files in a folder?

Wondering if anyone has done this?  Set a folder path as a variable,  Then I would like to get a list of all files in that folder, and send them to an excel file and save it to a different folder (like C:\temp           Would like output to include the full path.  Need to use them to import into another file/program.
RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Check this...i think it will help you accomplish what you need
Saurabh Singh TeotiaCommented:
You can use this to do what you are looking for..It will populate your names starting from a1..till the time it find last name.....
Sub getfiles()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim wb As Workbook
    Dim i As Long
    Dim xpath As String
    Dim fname As String
    xpath = "C:\Users\steotia\Desktop"
    If Right(ypath, 1) <> "\" Then xpath = xpath & "\"
    fname = Dir(xpath & "*.*")
    i = 1
    Do Until fname = ""

        Cells(i, 1).Value = (xpath & fname)
        i = i + 1
        fname = Dir()
    Loop
End Sub

Open in new window


right now it searching for only excel files..if you need all the files..then change this line..

fname = Dir(xpath & "*.xls*")

Open in new window


to this..
fname = Dir(xpath & "*.*")

Open in new window


Saurabh...
RWayneHAuthor Commented:
It looks like it, but I was not seeing where to set the target folder?  Would it be possible to use what is set in a file that I am using?  See attached.  We can set the folder path with the green button at the top of the page.  After that is set..  Would like this script to use the same folder.  Is this possible?
SIF_Creator.xlsm
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Saurabh Singh TeotiaCommented:
Their you go the code will be.,..

Sub getfiles()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim wb As Workbook
    Dim i As Long
    Dim xpath As String
    Dim fname As String
    xpath = "C:\_SAP\LynxFiles\"
    If Right(ypath, 1) <> "\" Then xpath = xpath & "\"
    fname = Dir(xpath & "*.*")
    i = 1
    Do Until fname = ""

        Cells(i, 1).Value = (xpath & fname)
        i = i + 1
        fname = Dir()
    Loop
End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
The issue is that it can chg.  Click the green button and the folder path chg's.  Also I would have to direct where the output goes..  like to itself own sheet.  I am adding a sheet called:  "GetFiles"  If we could direct the output to that sheet tab, I think we are golden... thanks for the help.
Saurabh Singh TeotiaCommented:
You can code in the path here from where the file is getting picked up???

and to go to get files..


     
 sheets("Getfiles").Cells(i, 1).Value = (xpath & fname)

Open in new window


This will go to get files...

Saurabh...
RWayneHAuthor Commented:
So what happens when I press the green button in the sample file?  and the storage location chg's to says C:\Temp  ??  How is the code going to know what files to grab because it has chg'ed?  Press the green button to determine the destination folder, which is one of the first step of the sample file.  Then after the destination file location is determined, it will then start creating files to that location.  After all files are created, we run getfiles and capture the complete list so we can loop through them in a different system.  HP-UFT (Unified Functional Testing).  I am going to import this excel sheet to loop through them and use them in a different procedure.

Not following the:
sheets("Getfiles").Cells(i, 1).Value = (xpath & fname)
in your last post.  However I do not believe I need too.  I am going to open the xlsm that has the getfile in it and run it from UFT...  then import that sheet tab, so am thinking that I do not care about where the getfiles populates because I will just grab it from that.
Saurabh Singh TeotiaCommented:
You need to use this...

xpath = GetNewFolder

Open in new window


This will take the file from where you are taking it...
RWayneHAuthor Commented:
Is it possible to fold these last two lines into the suggested solution?  I was a bit confused how they will fit into it?  If a file is opened, then I chg the destination folder of where I want the output to go, I am not seeing how it knows where to look for files in that given folder.  Sorry there is something I am not following.  Perhaps brief comments in the code?

Scenario: Open sample file from C:\Folder1, after it is opened I point to a destination location where files created with the sample will go. (two different locations).  The new destination location is the folder that I would like to check.
Saurabh Singh TeotiaCommented:
RWayneH,

I'm not sure what you are looking for here to me to do.You want me to write the code for you as in completely??

Saurabh...
RWayneHAuthor Commented:
thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.