Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Import File into Table with Import filename and date

  1. #1
    Board Regular
    Join Date
    May 2013
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Import File into Table with Import filename and date

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,731
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Import File into Table with Import filename and date

    Can you post the code you have for importing the file?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import File into Table with Import filename and date

    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

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,731
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Import File into Table with Import filename and date

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import File into Table with Import filename and date

    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.

  6. #6
    Board Regular
    Join Date
    May 2013
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import File into Table with Import filename and date

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

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,731
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Import File into Table with Import filename and date

    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?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,731
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Import File into Table with Import filename and date

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    May 2013
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import File into Table with Import filename and date

    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.

  10. #10
    Board Regular
    Join Date
    May 2013
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import File into Table with Import filename and date

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •