Capture File Name

[nick243]

Hi,

I am looking for some help to capture file names in 1 directory and insert them in a table so that I can find duplicates.



any help would be appreciated

Thanks,
Nick

[SodaPop]

Hi,

I am looking for some help to capture file names in 1 directory and insert them in a table so that I can find duplicates.

any help would be appreciated

Thanks,
Nick

Make it a collection and cycle through. Set each file as a variable for an sql query.

Dim strcolsubfolder as new collection
Dim strfolder as string
Dim strsql as string
Subfolder = "filelocatiinyouwanttoinspect"

Strsubfoler = dir(strfolder & "*", vbdirectory)
Do while not strsubfolder = ""
Select case ".", ".."
Colsubfolder.add item:=strsubfolder, key:=strsubfolder
And select
Strsubfolder = dir

Strsql = " Sql to append record based on strsubfolder"

Docmd.runsql strsql
Loop



Sent from my SM-G950U using Tapatalk

[June7]

Review http://allenbrowne.com/func-02.html

[nick243]

Thank you for the responses

I put in the directory location but I am not sure how to create the sql statement. What information do I need to create the sql correctly.

Thanks,
Nick

Make it a collection and cycle through. Set each file as a variable for an sql query.

Dim strcolsubfolder as new collection
Dim strfolder as string
Dim strsql as string
Subfolder = "filelocatiinyouwanttoinspect"

Strsubfoler = dir(strfolder & "*", vbdirectory)
Do while not strsubfolder = ""
Select case ".", ".."
Colsubfolder.add item:=strsubfolder, key:=strsubfolder
And select
Strsubfolder = dir

Strsql = " Sql to append record based on strsubfolder"

Docmd.runsql strsql
Loop

[June7]

I am not sure about SodaPop's code but following the Allen Browne example, like:

CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES('" & StrFileName & "')"

SodaPop is building a Collection and Allen is building an array. If you want to write record to table, neither collection nor array are needed.

[nick243]

I am not sure about SodaPop's code but following the Allen Browne example, like:

CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES('" & StrFileName & "')"

SodaPop is building a Collection and Allen is building an array. If you want to write record to table, neither collection nor array are needed.

Yes I would just like to write to a table.

Thanks for your help.

[June7]

Here is another Allen Brown link that shows how to loop through folder http://allenbrowne.com/ser-59.html (Allen uses Collection).

Instead of building a collection and setting a combo or list box list, write records to table. Simple version of code:

Public Sub GetFiles()
    Dim strFile As String
    strFile = Dir("C:\yourfolderpath\*.*")
    Do While strFile <> ""
        If (strFile <> ".") And (strFile <> "..") Then
            CurrentDb.Execute "INSERT INTO Files(FileName) VALUES('" & strFile & "')"
        End If
        strFile = Dir
    Loop
End Sub

[nick243]

Here is another Allen Brown link that shows how to loop through folder http://allenbrowne.com/ser-59.html (Allen uses Collection).

Instead of building a collection and setting a combo or list box list, write records to table. Simple version of code:

Public Sub GetFiles()
    Dim strFile As String
    strFile = Dir("C:\yourfolderpath\*.*")
    Do While strFile <> ""
        If (strFile <> ".") And (strFile <> "..") Then
            CurrentDb.Execute "INSERT INTO Files(FileName) VALUES('" & strFile & "')"
        End If
        strFile = Dir
    Loop
End Sub

This work great thank you