Import File into Table with Import filename and date

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
166
Hello,

Can this be possible to add the filename and date when importing excel file. I have a table of import file and i need to add the filename and date to the table for the records purposes. Is also possible to select the file. Any help is much appreciated. Thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Can you post the code you have for importing the file?
 
Upvote 0
Hi Joe,

I use the standard importing of files, what my idea is to select the file to be uploaded and have the filename and date on the fields of my table Create_Ops_BoR_Res.

Code:
Dim Filepath As String




Filepath = CurrentProject.Path & "\Import File\MDM I2 Ops Template Form_Create.xlsx"




If FileExist(Filepath) Then


DoCmd.TransferSpreadsheet acImport, , "Create_Ops_BoR_Res", Filepath, True, "Create_Ops_BoR_Res!"
    MsgBox "Request successfully uploaded", vbInformation, "Done"
Else
    MsgBox "File not found.", vbCritical, "Error"
End If
 
Upvote 0
So, are you importing this to an existing table, or a new table?
Do you already have fields in this table to store file name and date?
If so, I would use an Update Query after the import to populate those two fields with those values.

If you need help in writing that, please provide the table field names for the file name and date fields.
 
Upvote 0
Yes i am importing it to existing table, but it does have fields for filename and date, i am think if it not possible, i will create a query and manually input the filename and automatically have the date. but i dont know how i can do it that i will update the filename of new uploaded in 1 input box or something. that when i click the button, it will ask input filename and it will be populated to filename fields and automatic current date.
 
Upvote 0
And after i created that query, i will append it to table, so that this will serve as a record of changes.
 
Upvote 0
My thinking is to do the following:

1. Have the VBA code prompt your to browse for your file
2. Once the file has been selected, capture the file name and them import your file into the existing table
3. In VBA, dynamically create and run the Update Query to take the filename we captured and current date and populate those values for all records in your table missing these values (which, should only be the ones you just imported)

Does that sound like it should do what you want?
 
Upvote 0
Here is VBA code that will do all of that. You may need to edit the FileName and ImportDate fields in my SQL string to reflect the actual name for these fields in your table:
Code:
Private Sub cmdFileBrowse_Click()

    Const msoFileDialogFilePicker As Long = 3
    Dim objDialog As Object
    Dim fileName As String
    Dim fullName As String
    Dim strSQL As String

'   Browse for file
    Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
    With objDialog
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "No file selected."
        Else
'           Get file name
            fileName = Dir(.SelectedItems(1))
'           Get full name and path
            fullName = .SelectedItems(1)
        End If
    End With
    
'   Import file into table
    DoCmd.TransferSpreadsheet acImport, , "Create_Ops_BoR_Res", fullName, True, "Create_Ops_BoR_Res!"
    
'   Build update query
    strSQL = "UPDATE Create_Ops_BoR_Res " & _
            "SET Create_Ops_BoR_Res.FileName = " & Chr(34) & fileName & Chr(34) & ", Create_Ops_BoR_Res.ImportDate = Date() " & _
            "WHERE ((Create_Ops_BoR_Res.FileName Is Null) AND (Create_Ops_BoR_Res.ImportDate Is Null));"
    
'   Run update query
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    MsgBox "Import Done!"
  
End Sub
 
Upvote 0
Hi Joe,

There is a Bug on Build Update Query, "Operation Must use an updateable query", when running the VBA, it prompted me to input the Filename and Import Date which i suppose it will captured the name and the current date base on the codes. I am trying also to learn and understand of every codes.
 
Upvote 0
Hi Joe,

Just figured out, and its now working, Thank you so much... i created the Filename and ImportDate from the table and its work.. Thank you thank you...
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top