在Access中导入Excel数据 [英] Import Excel data in Access

问题描述

我的Access应用程序中有一个表，该表需要用一堆Excel文件填充数据.我尝试了这段代码:

I have a table in my Access application which needs to be filled with data in a bunch of Excel files. I tried this code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, strTable, strExcelpath, True

但是它每次都会覆盖我的访问表中的数据，而不是附加数据，因此它绝对无法控制正在发送的内容.

But it overwrite the data in my access table each time instead of appending it and it gives absolutely no control over what is being sent.

我需要找到一种方法，将Excel文件中的数据简单地附加到Access表中，这两个文件具有相同的结构，所以我想知道是否存在一种无需指定列即可逐行导入数据的方法.但是，出于个人知识和对用户使用的担心，我还想知道如何在考虑行和列的情况下将其导入.

I need to find a way to simply append the data from an Excel file to my Access table, both files have the same structure so I would like to know if there is a way to import it line by line without specifying the columns. However, for personal knowledge and fear of user uses, I would also like to know how to import it considering the lines and columns.

谢谢！

在Excel文件上选择以下内容进行编码:

Code with the select on the Excel file:

Dim cn As ADODB.Connection
Dim strQuery As String

Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & Application.CurrentProject.Path & "\Excel\test.xls;" & _
                        "Extended Properties=Excel 8.0;"
    .Open
End With

strQuery = "INSERT INTO tblClients " & _
        "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE =" & Application.CurrentProject.Path & "\Excel\test.xls].[tblImport$]"
DoCmd.RunSQL strQuery

推荐答案

您还可以在查询中引用Excel工作表或范围:

You can also refer to an Excel sheet or range in a query:

INSERT INTO Table1 ( ADate ) 
SELECT SomeDate FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$a1:a4]

或

INSERT INTO Table1
SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$]

在一个过程中:

Sub RunThisQuery()
    strQuery = "INSERT INTO tblClients " & _
       "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" _
       & Application.CurrentProject.Path & "\Excel\test.xls].[tblImport$]"

    CurrentDB.Execute strQuery, dbFailOnError
End Sub

请注意，在DATABASE之后的代码中有一个空格.它必须读取DATABASE=，没有空格.

Note that you had a space in your code after DATABASE. It must read DATABASE=, no space.

这篇关于在Access中导入Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！