Import File into Table with Import filename and date

[dhen21dx]

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

 

[Joe4]

Can you post the code you have for importing the file?

 

[dhen21dx]

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.

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

 

[Joe4]

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.

 

[dhen21dx]

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.

 

[dhen21dx]

And after i created that query, i will append it to table, so that this will serve as a record of changes.

 

[Joe4]

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?

 

[Joe4]

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:

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

 

[dhen21dx]

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.

 

[dhen21dx]

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...