Import File into Table with Import filename and date

dhen21dx

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Joe4

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

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
121
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
52,071
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
121
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
121
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
52,071
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
52,071
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
121
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
121
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,089,395
Messages
5,408,000
Members
403,176
Latest member
mehtavish1

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top