none
How to import multiple sheets in Excel into an Access table? RRS feed

  • Spørgsmål

  • My Excel file contain several sheets and I need import all these sheets into an Access table, the table and the sheets have the same header.

    Is there a way that I can import all these sheets into the Access file automatically.

    22. december 2011 05:48

Svar

  • The Excel Analyst's Guide to Access written by Michael Alexander provides the following code to append a single sheet into Access table, if I have

    many sheets in the Excel file to append, can anybody help me modify the code?

     

    Sub GetData_From_Excel_Sheet()
    
    'Step 1: Declare your variables
    Dim MyConnect As String
    Dim MyRecordset As ADODB.Recordset
    Dim MyTable As ADODB.Recordset
    Dim MySQL As String
    
    'Step 2: Declare your connection string
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\OffTheGrid\Chapter15_SampleFile.xlsm;" & "Extended Properties=Excel 12.0"
    
    'Step 3: Build your SQL statement
    MySQL = "SELECT * FROM [SampleData$]" & "WHERE Region ='NORTH'"
    
    'Step 4: Instantiate and specify your recordset
    Set MyRecordset = New ADODB.Recordset
    MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
    
    'Step 5: Instantiate and specify your Access table
    Set MyTable = New ADODB.Recordset
    MyTable.Open "ExcelFeed", CurrentProject.Connection,adOpenDynamic, adLockOptimistic
    
    'Step 6: Loop through each record and add to the table
    Do Until MyRecordset.EOF
    MyTable.AddNew
    MyTable!ActiveRegion = MyRecordset!Region
    MyTable!ActiveMarket = MyRecordset!Market
    MyTable!Product = MyRecordset!Product_Description
    MyTable!Revenue = MyRecordset!Revenue
    MyTable!Units = MyRecordset!Transactions
    MyTable![Dollar Per Unit] = MyRecordset![Dollar Per Transaction]
    MyTable.Update
    MyRecordset.MoveNext
    Loop
    
    End Sub


    • Markeret som svar af alfred w 23. juli 2012 02:09
    23. december 2011 04:43