add file name to a table after import (1 Viewer)

sspreyer · Aug 15, 2018

hi all

i have some code that i use to import data from excel

Code:

Dim SelectedFile    As String
Dim FilePicker      As FileDialog
Dim SQLdelete       As String

Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
FilePicker.AllowMultiSelect = False
FilePicker.Filters.Add "Excel", "*.xls*", 1
FilePicker.InitialFileName = "C:\Users\"
FilePicker.Title = "Please Select the Excel Data..."
FilePicker.Show

If FilePicker.SelectedItems.Count <> 0 Then
    SelectedFile = FilePicker.SelectedItems(1)
    
 



        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblExcelImport", SelectedFile, True, "A4:E"


    MsgBox ("The data has been successfully loaded")
End If
End Sub

but want to be able to get the selected filename add it to another table called tblexcelfilename once the import is done

any help much appreciated

shane

Ranman256 · Aug 15, 2018

Can you run an update after the Import?

Docmd.Transferspreadsheet...
SSql= "update tblExcelImport set [filename]='" & SelectedFile & "' where [filename] is null"
Docmd.runSql sSql

sspreyer · Aug 15, 2018

Ranman256 said:
Can you run an update after the Import?

Docmd.Transferspreadsheet...
SSql= "update tblExcelImport set [filename]='" & SelectedFile & "' where [filename] is null"
Docmd.runSql sSql

thanks
sorry that won't work because i'm importing in to tblexcelimport but really wanted the file name to go in tblexcelfilename

Gasman · Aug 15, 2018

Assuming you want a date it was imported as well?

Code:

sSQL="INSERT INTO tblexcelfilename ( ImportDate, Fname )"
sSQL = sSQL & " SELECT Date() AS ImportDate, '" & SelectedFile & "' AS FName"
Docmd.runSql sSql

Frothingslosh · Aug 15, 2018

Here's a silly question: Do you want just the file name, or the whole path?

add file name to a table after import (1 Viewer)

sspreyer

Registered User.

Ranman256

Well-known member

sspreyer

Registered User.

Gasman

Enthusiastic Amateur

Frothingslosh

Premier Pale Stale Ale

Similar threads

Users who are viewing this thread