如何通过VBA将包含多个工作表的一个Excel文件导入到访问表中 [英] How to import one excel file that contain multiple worksheets into an access table by vba

问题描述

我必须通过vba将一个包含多个工作表的excel文件导入到访问表中，但是下面列出的当前代码将仅复制excel的第一个工作表记录并导入到访问表中，所有工作表的格式相同和布局.如何使我的代码能够复制所有工作表的记录并导入到Access中的表中. 请随时回答问题，并感谢您的回答.

I have to import one excel file that contain multiple worksheets into an access table by vba, but my current code listed below will only copy the first worksheet record of the excel and import into an access table, all the worksheets got same format and layout. how to enable my code to copy all the worksheets' records and import into a table in access. Please feel free to answer the question and thanks for any answer.

 Private Sub Command9_Click()


       ' Requires reference to Microsoft Office 11.0 Object Library.

   Dim fDialog As FileDialog
   Dim varFile As Variant

   ' Clear listbox contents.
   'Me.FileList.RowSource = ""

   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      .AllowMultiSelect = False


      .Filters.Add "Excel File", "*.xls"
      .Filters.Add "Excel File", "*.xlsx"

      If .Show = True Then

         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
         ' Label3.Caption = varFile

         Const acImport = 0
         Const acSpreadsheetTypeExcel9 = 8
                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Plymouth - Nominal Detail", varFile, True

         Next
         MsgBox ("Import data successful!")
         End If
End With


End Sub

推荐答案

您需要指定工作表，例如:

You need to specify the sheets, for example:

Private Sub Command9_Click()
   ' Requires reference to Microsoft Office 11.0 Object Library.
   Dim fDialog As FileDialog
   Dim varFile As Variant

   ' Clear listbox contents.
   'Me.FileList.RowSource = ""

   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      .AllowMultiSelect = False
      .Filters.Add "Excel File", "*.xls"
      .Filters.Add "Excel File", "*.xlsx"

      If .Show = True Then

         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
         ' Label3.Caption = varFile

         Const acImport = 0
         Const acSpreadsheetTypeExcel9 = 8

         ''This gets the sheets to new tables
         GetSheets varFile

         Next
         MsgBox ("Import data successful!")
         End If
End With
End Sub


Sub GetSheets(strFileName)
   'Requires reference to the Microsoft Excel x.x Object Library

   Dim objXL As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim wks As Object

   'objXL.Visible = True

   Set wkb = objXL.Workbooks.Open(strFileName)

   For Each wks In wkb.Worksheets
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            wks.Name, strFileName, True, wks.Name & "$"
   Next

   'Tidy up
   wkb.Close
   Set wkb = Nothing
   objXL.Quit
   Set objXL = Nothing

End Sub

这篇关于如何通过VBA将包含多个工作表的一个Excel文件导入到访问表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！