You will need to replace “YourPathToFolder” and "TitleOfYourDatabase" with text appropriate to your process.
Dim strDate as string, strFolderNa as string, fso As Object, objFiles As Object strFolderNa = “YourPathToFolder” strDate = InputBox("Enter date of data to be imported, using MMDDYY format:", "TitleOfYourDatabase") 'loop through files in folder until one matches Set fso = CreateObject("Scripting.FileSystemObject") Set objFiles = fso.GetFolder(strFolderNa).Files For Each obj In objFiles If InStr(obj.Name, “*” & strDate & “b.csv”) > 0 Then strFileNa = obj.Name Set objFiles = Nothing Set fso = Nothing Set obj = Nothing GoTo LinkCSVFile End If Next obj 'If execution does not GoTo LinkSpreadsheet, no file was found strMsg = "No file matching the name pattern '" & strDate & "' was found in " & strFolderNa & ". " & _ "Confirm the date of the data to be imported and restart this process." MsgBox strMsg, vbOKOnly, "TitleOfYourDatabase" Exit Sub LinkCSVFile : ‘your code to import the data goes here ExitSub: Set objFiles = Nothing Set fso = Nothing Set obj = Nothing Exit Sub
If the data imported belongs to the previous work day, you can tweak the Date() to return a previous date.
strDate = InputBox("Enter date of data to be imported, using MMDDYY format:", "TitleOfYourDatabase", Format(Date(), "MMDDYY")
debug.print "strFileNa: " & strFileNa
1. import one
2. modify data structure to add field AT THE END for the file path and name (ie: fPathName, text, 255)
3. Import data to existing table
4. run update query to populate fPathName for all records that don't have it filled
once you do this process manually, you can automate it with VBA.
to improve: the file names you import will be in their own table that VBA would populate with AutoNumber FileID and fPathName (or just fName if path is always the same) -- and then store a numeric FileID in the related table instead of the storing path\filename onto each record.
what kind of data is this?