I'm a begginer in Access and VBA coding and I would love to list all files names I have in a folder into my access table (ID (automatic), File Name) ?
Asked
Active
Viewed 3,200 times
-2
-
Look at `DIR` look at `FileSystemObject` I think looping files in VBA should give you a google answer, also searching on here should. http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba and also `SQL Insert` – Nathan_Sav Jul 20 '16 at 11:50
-
I did, I used DIR but it shows nothing to me, because all I can find is excel examples with "Cells" function, I wanna insert the names in my table (called table1) – Tarik Kaoukab Jul 20 '16 at 11:55
2 Answers
0
Try this:
Dim F as Variant, FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Docmd.SetWarnings False
For each F in FSO.getFolder("Path to your folder").Files
Docmd.RunSQL "INSERT INTO table1 ([FieldName]) VALUES ('" & F & "');"
Next
Docmd.SetWarnings True
June7
- 19,874
- 8
- 24
- 34
Julian Kuchlbauer
- 895
- 1
- 8
- 17
-
Thank you ! it is working !! but how can I show only the file name ? – Tarik Kaoukab Jul 20 '16 at 12:13
-
-
F.Name gives me the name with extension, but just for knowing, how to show only the extension ? – Tarik Kaoukab Jul 20 '16 at 12:15
-
You can use the FSO functions: FSO.getBaseName(F) is without the extension. FSO.getExtensionName(F) is just the extension. Here's the reference: https://msdn.microsoft.com/en-us/library/6tkce7xa(v=vs.84).aspx – Julian Kuchlbauer Jul 20 '16 at 12:18
0
So, all files into ONE table?
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpFolderFiles
Or, all files into SEPARATE tables?
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpAllWkshtsFilesSepTbls
ASH
- 20,759
- 19
- 87
- 200