Results 1 to 7 of 7
  1. #1
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82

    Import data from an Excel spreadsheet from within Access with VBA?

    Hi All,

    I've been on Google and there's a lot of information about exporting data from Excel to Access using VBA, but not the other way around.

    I'm building a form in Access where I want the user to be able to select a spreadsheet with data in it. With a simple push of a button and behind the scenes, the information will be imported into an already existing table. Important that the data NOT overwrite any of the data in the table. INSERT FROM Excel INTO ACCESS kind of thing.

    Thanks for the help,

    Scott

    (ps, I've got the file picker nailed!)

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use TransferSpreadsheet to import the data, or link to the file and then run an append query to pull it in.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    usage:
    save a generic file: c:\folder\file2Import.xls
    link it into the db as a linked table.
    user clicks SELECT FILE button to select file to import
    filepath is stored in textbox on the form
    then this file is copied to the generic name everytime and overwrites it, c:\folder\file2Import.xls
    runs the append query to collect data

    user picks the file
    then imports the data

    user picks the file
    Code:
    sub btnSelectFile_click()
       vFile = UserPick1File("c:\folder\")
       if vFile <> "" then 
          filecopy vFile, "c:\folder\file2Import.xls 
          docmd.openquery "qaImportData"
       endif
    
    end sub


    put this code into a MODULE for user to pick the file

    Code:
    Public Function UserPick1File(Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office xx.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    
    With Application.FileDialog(msoFileDialogFilePicker)     'msoFileDialogSaveAs
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
         '.Filters.Add "CSV Files", "*.csv"
         '.Filters.Add "Excel Files", "*.xls;*.xlsx"
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ken Snell's site has lots of good VBA examples http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

  5. #5
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    http://www.accessmvp.com/kdsnell/EXC...m#ImpAllWkshts

    This is great stuff right here. The only problem is, when I run it, it gives me an error:

    Code:
    Run-time error '2391':
    
    Field 'F1' doesn't exist in destination table 'tblIndividualLearning.'

    I have it set so it knows there are no field headers. The Excel worksheets are all setup to look like the destination table. The only thing that is missing is the 'ID' field which is an auto-number for the primary key. If I type in headers into the worksheets and set that option to 'True' in the code, it works fine. I don't want the user to have to do that with all the spreadsheets though. It will cause errors. Guaranteed.

    That link above was gold though. Thanks!

  6. #6
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Is there a way to use SQL to read from ALL sheets in the workbook? This seems like a really easy solution to this problem if possible. I've got this to get me the data from one sheet and it works great:

    Code:
    SELECT T1.*
     FROM [Excel 8.0;HDR=NO;IMEX=1;Database=C:\Training Data.xlsm].[Sheet1$A1:U65536] as T1;
    If I can let the user pick the file and write an SQL query to insert into my tblIndividualLearning, it would be perfect.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by scott0_1 View Post
    Is there a way to use SQL to read from ALL sheets in the workbook?
    <snip>
    If I can let the user pick the file and write an SQL query to insert into my tblIndividualLearning, it would be perfect.
    Don't see why not. You could use code to:
    - pick the file (you have that), then
    - open the file and read all of the sheet names or get the total number of sheets and (snippets of code from http://www.accessmvp.com/kdsnell/EXC...pAllWktsSepTbl)
    - create a loop to change the sheet name in the SQL and query the sheet
    - code to process the query and save the data to a table

    This is NOT the full code..... just spitballing here
    Code:
        Dim r As DAO.Recordset
        Dim sSQL As String
    
        Dim lngCount As Long
        Dim objExcel As Object, objWorkbook As Object
        Dim colWorksheets As Collection
        Dim strPathFile As String
        Dim strPassword As String
    
    
        'Pick Excel file code goes here
    
    
        ' Open the EXCEL file and read the worksheet names into a collection
        Set colWorksheets = New Collection
        Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , strPassword)
        For lngCount = 1 To objWorkbook.Worksheets.Count
            colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
        Next lngCount
    
        ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
        objWorkbook.Close False
        Set objWorkbook = Nothing
        If blnEXCEL = True Then objExcel.Quit
        Set objExcel = Nothing
    
    
        For lngCount = 1 To colWorksheets.Count
            sSQL = "SELECT T1.* FROM [Excel 8.0;HDR=NO;IMEX=1;Database=" & FilePathAndName & "].[" & colWorksheets(lngCount) & "$A1:U65536] as T1;"
    
            'open recordset
            Set r = CurrentDb.OpenRecordset(sSQL)
            'process recordset
            r.Close
        Next x
    
        'do clean up

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

Similar Threads

  1. Replies: 1
    Last Post: 08-20-2015, 04:46 AM
  2. Replies: 1
    Last Post: 11-26-2012, 12:35 PM
  3. How to import excel spreadsheet into an Access subform
    By upfish in forum Import/Export Data
    Replies: 1
    Last Post: 10-03-2012, 01:53 PM
  4. import excel spreadsheet though outlook
    By bopsgtir in forum Import/Export Data
    Replies: 0
    Last Post: 03-18-2011, 09:07 AM
  5. Replies: 1
    Last Post: 08-12-2010, 10:04 PM

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