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 Excel Dat Module - Import File Name, Access 2010    
post Dec 7 2017, 07:18 PM

Posts: 96
Joined: 10-October 12

I'm using the following code to successfully import mulitple excel spreadsheet file data.

Function Import_All()

Dim myfile
Dim mypath
mypath = "\\folderpath\folder"
myfile = Dir(mypath & "*.xlsm")


'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 8, "tblUploadImport", mypath & myfile, 1, "Upload!A1:H100000"
myfile = Dir
Loop Until myfile = ""

End Function

Is there any way to grab the file name and attach it to each row?
This post has been edited by ryangus: Dec 7 2017, 07:19 PM
Go to the top of the page
post Dec 7 2017, 07:39 PM

Posts: 2,366
Joined: 27-February 09

Grab the filename, assign it to a string variable, then do something like

"UPDATE MyTable SET SourceFile ='" & strFileName & "' WHERE SourceFile IS NULL;"

Then execute against the destination table after each file is imported.

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    22nd February 2018 - 11:22 AM