Results 1 to 11 of 11
  1. #1
    Amine is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2021
    Posts
    8

    Import multiple excel files with a dialog box.

    Hello,
    I want to import multiple excel files (only selected) from a specific folder using a button in a form.
    1- I open the form and I click the button “Import”, the dialog box appears showing me all the excel files in the folder “ C:\\…………\desktop\documents\” .
    2- I select/choose the desired excel files (multi select) to import to my access table.
    *all the excel files have a similar name “Delivery note-xxxx” with “xxxx” is a four digits number.
    *all the excel files have the same structure:
    a) 2 sheets : title and lines.
    The first sheet called “title” contains in the cell “B2” the same four digits number “xxxx” of the name of file.


    The second sheet called “lines” contains the data to import to my access table.
    b) the second sheet “lines” in all the excel files contains 5 columns ( Reference, name of product, quantity, price, Total).
    3- after importing the excel files, i should obtain a table called “deliveries data” that contains 6 columns ( Reference, name of product, quantity, price, Total, Number) with :
    a) the first 5 columns contains the data imported from all the second sheets.
    b) the 6th column contains the same four digits number “xxxx” of each excel file.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    an example sql query might look like this to get the data from excel - easily converted to an append query

    SELECT *
    FROM (SELECT * FROM [sheet1$a1:a2] as A, [sheet2$a:e] AS xlData IN 'D:\path\filename.XLSX'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes]) AS XL;
    Last edited by CJ_London; 11-20-2022 at 09:56 AM.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Processing multiple files?

    https://bettersolutions.com/vba/file...iple-files.htm

    though I got the impression you expected someone to write it all for you?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Amine is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2021
    Posts
    8
    If there is “someone”, why not ?
    Before posting the thread, i was able to :
    - showing the dialog box with multi select option set to true.
    - when i click on “open” in the dialog box, only the first selected file is imported to my table (not all files)
    - my code failed to import all the files in one table.
    - my code failed to write the “xxxx” number in the sixth column of the table.
    - I tried to import each selected file of the dialog box in a separate table in ms access, but failed too.
    Conclusion: I’m looking for a good code (coherent and complete) better than mine.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Use the code provided by Welshgasman to loop through the selected files and use Docmd.TransfetSpreadsheet to link the Lines sheets (using the range parameter of the transferspreadsheet method). You don't need the first one as you can get it from the file name using Replace(Right(varFile,9),".xlsx",""). Finally build a SQL string in VBA to append your newly linking table and the number to your final table.

    Post your attempt if you get stuck!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Amine View Post
    If there is “someone”, why not ?
    Before posting the thread, i was able to :
    - showing the dialog box with multi select option set to true.
    - when i click on “open” in the dialog box, only the first selected file is imported to my table (not all files)
    - my code failed to import all the files in one table.
    - my code failed to write the “xxxx” number in the sixth column of the table.
    - I tried to import each selected file of the dialog box in a separate table in ms access, but failed too.
    Conclusion: I’m looking for a good code (coherent and complete) better than mine.
    So you did not loop through the files selected? That link I posted should show you how.
    The number you want is on the tail end of your filename, or at least you said it was?

    You could use CJ_London's code (amended to be an append query) with the filename changing each time using concatenation?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Amine is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2021
    Posts
    8
    Hello,
    I used the code provided by Welshgasman (big thanks) and looped through selected files using "Docmd.TransfetSpreadsheet" and i got :
    1 - successful importation of all the lines from the selected excel files.
    2 - failed to add the four digits number "xxxx" to the last column of my table.
    if possible, can you make some changes on Weshgasman's Code and share it ?

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Not my code, just my link.
    Please show your code (within code tags and indented).
    Likely your concatenation is incorrect?
    Put the sql string into a string variable the you can debug.print it until you get it right, then execute that stringas the variable.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Depending on how you run the appends you could incorporate the number in the INSERT statement itself or do it immediately afterwards using an UPDATE statement as they will be empty for the last appended set of records:
    Code:
    Dim f As Object 'FileDialog  
    Dim lNumber as long
    Set f = Application.FileDialog(3) 'msoFileDialogFilePicker  
    With f 
      .AllowMultiSelect = True 'default
       .InitialFileName = "C:\Temp\" 
    '   Specify filters
      .Filters.Clear
      .Filters.Add "Excel files", "*.xlsx"
       .Show 
       Dim varFile As Variant 
       For Each varFile In .SelectedItems 
          'MsgBox Trim(varFile) 
    	  lNumber=Replace(Right(varFile,9),".xlsx","")
    	  Docmd.TransferSpreadsheet ........'
    	  'run your append from the new linked table to your final one
    	  CurrentDb.Execute "qryAppendNewDelivery",dbFailOnError
    	  'now update the number field which should be null for the newly added records
    	  CurrentDb.Execute "UPDATE tblYourFinalDeleveriesTable SET YourNumberField = " & lNUmber & " WHERE YourNumberField Is NULL;",dbFailOnError
       Next 
       
       msgbox "Done"   
    End With
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Amine is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2021
    Posts
    8
    Thanks a lot Gicu and Welshgasman.
    This is exactly what i did :
    After adding the new lines to my table, i replace the empty cells ( las column) by the number extracted from the name of the file using the functin right.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-10-2019, 11:39 AM
  2. Import multiple Excel Files into 1 access table
    By jurbin in forum Import/Export Data
    Replies: 1
    Last Post: 05-15-2015, 01:45 PM
  3. Import Multiple Excel Files with SAME layout to Access
    By torontoraptor in forum Programming
    Replies: 2
    Last Post: 05-12-2014, 10:29 AM
  4. Replies: 1
    Last Post: 03-25-2014, 08:54 PM
  5. Replies: 9
    Last Post: 10-31-2013, 06:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums