Solved

Populate Access Table with names of documents in a folder

Posted on 2011-09-08
6
262 Views
Last Modified: 2012-05-12
I have written Access VBA Code that opens a folder and displays all the documents in that folder.

QUESTION:

How do I use the FileSystemObject to populate a table with the document names?

I would greatly appreciate some help

Biggles1
0
Comment
Question by:Biggles1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 12

Assisted Solution

by:danishani
danishani earned 100 total points
ID: 36503477
Check this nice Function out of Allen Browne:
http://www.everythingaccess.com/tutorials.asp?ID=List-files-to-a-table

Hope this helps,
Daniel
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 36503484


Sub gFiles()

Dim initPath As String, strFile As String
Dim objFSO, objFdr
   initPath = "C:\ Folder1\Folder2\"
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFdr = objFSO.GetFolder(initPath)
        strFile = Dir(initPath & "\*.*")
        Do Until strFile = ""
           
            'your code to add the filenames to the table

            strFile = Dir
        Loop

End Sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36503532
capricorn1,

Have not use the FSO for a couple of years, but when I did, I generally found that it was quite a bit slower than just using DIR() to loop through the various folders and files.  Do you know if this is still the case?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36503569

@fyed- yes, i think that is one of the reason why i use Dir() in my recursive search code.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36503581
the link that danishani posted above http:#a36503477 have the recursive search codes
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36504294
It'd help if you posted the code you have already.
I assume you have a FileDialog object on the go.
<opens a folder and displays all the documents in that folder>
And I suspect that the .Show is opening Windows Explorer window.

It's not that hard to take the vrtSelectedItem from that, and then do a GetFolder, GetFiles, and loop once through the Files collection, writing records to a table

The structure and name of the table would be nice to know too because the loop will be something like

dim rs as recordset
set rs = currentdb.openrecordset("select * from SomeTable where 1=2;",dbopendynaset)
for each myFile in myFiles
    with rs
       .addnew
       !PathName = vrtItemSelected
       !FileName = myfile.name
       .update
    end with
loop

But I can't do the syntax up without REALLY knowing what you have on the go.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 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