X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Import Directory Of Xlsx Files Into Table, Access 2016    
post Aug 8 2019, 03:44 PM

Posts: 48
Joined: 7-January 11

So I have the below code that currently works but it will import the names of files into a table from one directory but how do I modify it to import the files that are in subfolders?

Essentially it takes all XLSX files within a directory and grabs the path and the filename and puts them into a table named DirectoryListing and two field within the table called "FilePath" and "FileName"

Once I import this file I then manipulate it to find duplicates.

Function ImportDirListing(strPath As String, Optional strFilter As String)
' Author: CARDA Consultants Inc, 2007-01-19
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'strPath = "C:\pathtofile"
'strFilter = "*" 'if you want to return
' a complete listing of all the files enter a value of
' "*" as the strFilter
On Error GoTo Error_Handler

Dim MyFile As String
Dim db As Database
Dim sSQL As String

Set db = CurrentDb()

'Add the trailing if it was omitted
If Right(strPath, 1) <> "" Then strPath = strPath & ""
'Modify the strFilter to include all files if omitted in the function
If strFilter = "" Then strFilter = "*"

'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$(strPath & "*." & strFilter)
Do While MyFile <> ""
'Debug.Print MyFile
sSQL = "INSERT INTO DirectoryListing " _
& "(FilePath, FileName) VALUES " _
& "(""" & strPath & """, """ & MyFile & """)"
db.Execute sSQL, dbFailOnError
'dbs.RecordsAffected 'could be used to validate that the
'query actually worked
MyFile = Dir$

On Error Resume Next
Set db = Nothing
Exit Function

MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ImportDirListing" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit

End Function
Go to the top of the page
post Aug 8 2019, 03:56 PM

Posts: 795
Joined: 25-January 16

Review http://allenbrowne.com/ser-59.html

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post Aug 8 2019, 04:32 PM

Posts: 48
Joined: 7-January 11

thanks @June7, I'll take a look at that when i get back in the office tomorrow morning
Go to the top of the page
post Aug 9 2019, 02:07 PM

Posts: 48
Joined: 7-January 11

that did the trick; thanks!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    17th September 2019 - 04:21 AM