• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Import Multiple Spreadsheets into Access | File Dialog VBA

April 16, 2015 by Ryan McCormick 3 Comments

Sometimes it would be great to combine a bunch of spreadsheets into one table. When working with consolidated spreadsheet data, be sure to manage duplicates.

To import multiple spreadsheets into Access with VBA, there are a few housekeeping items that must be completed. First, you need to make sure that the data structure for all of your source files is the same. Field names, datatypes, everything should be a match.

Step 1: Use the Access spreadsheet query wizard to pull in a sample of your data. This will allow you to design your schema properly. Open design view and set up all of your data types how you need them.

Step 2: Nuke the data(not the table itself) in your new table

DELETE * FROM

Step 3: Open your VBA environment, select your tools menu and add a reference to the MS Office xx.x Object Library. The xx.x will be 15.0 if you have Access 2013 and down from there with older versions.

Step 4: Add a new module to your VBA project and paste this function in:

'--------------------------------------------------
' NEED TO ADD REFERENCE
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Public Sub multSpreadsheetImport(iTable As String)
Dim fd As FileDialog, vrtSelectedItem As Variant
 
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .AllowMultiSelect = True
    If fd.Show = True Then
        For Each vrtSelectedItem In fd.SelectedItems
            DoCmd.TransferSpreadsheet acImport, , iTable, vrtSelectedItem, True
        Next vrtSelectedItem
    Else
        'Exit code if no file is selected
        End
    End If
End With
 
Set fd = Nothing
 
End Sub

Step 5: Add a call to the imported function where you need it.

Call multSpreadsheetImport("")

Step 6: Test. If you get an error, make sure that you added a reference to the Microsoft Office Object Library. Without the reference it won’t work. After making the call, you should see a filedialog window pop open. Select all of you source files and click ‘OK’

As always comment if you get stuck.

Related

Filed Under: Microsoft Access, VBA Tagged With: Import multiple spreadsheets, join multiple spreadsheets, MS Access, vba

Reader Interactions

Comments

  1. Bob Snider says

    April 28, 2015 at 5:09 pm

    Ryan,
    Sincerely, thank you. The code worked great! I’m using it to select multiple spreadsheets, each appending to a single table.

    Reply
  2. Jason says

    November 1, 2015 at 5:05 am

    Brilliant, exactly what I was looking for. Thanks 🙂

    Reply
  3. andy says

    October 24, 2016 at 5:06 am

    same thing on everyone, single file upload or multiple

    run time error ‘3274’

    External table is not in the expected format

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in