Import File into Table with Import filename and date

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
111
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
Can you post the code you have for importing the file?
 

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
111
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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

Board Regular
Joined
May 10, 2013
Messages
111
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

Board Regular
Joined
May 10, 2013
Messages
111
And after i created that query, i will append it to table, so that this will serve as a record of changes.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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
 

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
111
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

Board Regular
Joined
May 10, 2013
Messages
111
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...
 

Forum statistics

Threads
1,077,823
Messages
5,336,570
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top