使用 VBA 将 Excel 中的数据插入 Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4226004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Data from Excel into Access using VBA
提问by user319940
I've made some code to insert data from an excel table in to an access database - my code is as follow:
我已经编写了一些代码来将数据从 excel 表插入到访问数据库中 - 我的代码如下：
Sub AddData()
Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
'lets connect to the workbook first, I tested this, it works for me
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.xls;Extended Properties=Excel 8.0;" _
& "Persist Security Info=False"
' Append data from Sheet1 of workbook to Table1 of mydb.mdb:
Cn.Execute "INSERT INTO tblSales IN 'C:\Users\User\Documents\access.mdb' SELECT * FROM [datasheet]"
Cn.Close
Set Cn = Nothing
End Sub
My problem is when executing this I get the error "Microsoft Jet Engine could not find the path to object "datasheet" . Datasheet is just the name of the sheet where the data is located in my workbook. Any help is much appreciated.
我的问题是在执行此操作时出现错误“Microsoft Jet Engine 找不到对象“数据表”的路径。数据表只是数据在我的工作簿中所在的工作表的名称。非常感谢任何帮助。
采纳答案by dimitarie
What happens if you put a $ sign after the sheet name like this [datasheet$] ?
如果像这样 [datasheet$] 那样在工作表名称后面放一个 $ 符号会发生什么？
回答by Patrick Honorez
I think you cannot execute a query on just any open workbook. It MUST be run against a file, that means you have to provide a full path to you sheet, including the filename. If your workbook is "dirty", you need to save it first. I would rather
我认为您不能只对任何打开的工作簿执行查询。它必须针对文件运行，这意味着您必须提供工作表的完整路径，包括文件名。如果您的工作簿“脏”，则需要先保存它。我宁愿
- loop the worksheet lines and add the records one by one
- or use the code you just wrote from Access (if it is suitable)
- 循环工作表行并一一添加记录
- 或使用您刚从 Access 编写的代码（如果合适）
回答by Fionnuala
All that is missing, as far as I can see, is the path to your data source and a string on datasheet:
据我所知，缺少的只是数据源的路径和数据表上的字符串：
Data Source=sample.xls;
Should read, for example:
应该读，例如：
Data Source=c:\docs\sample.xls;
And:
和：
SELECT * FROM [datasheet$]
回答by MikeD
The SELECT statement runs on the database itself, but you want to send values from EXCEL. So you must use
SELECT 语句在数据库本身上运行，但您希望从 EXCEL 发送值。所以你必须使用
cn.Execute "INSERT .... VALUES (" & excelcell_or_variable & ");"
eventually in a loop to proces all rows/columns etc.
最终在一个循环中处理所有行/列等。
Hope that helps
希望有帮助
good luck
祝你好运
EDIT ... don't forget quotation marks surrounding CHAR's and interpunctations; I use
编辑...不要忘记围绕 CHAR 和插点的引号；我用
' ....
' .... "...VALUES (" & T(Q(MyStringCell)) & T(MyNumCell) & Q(MyLastTextCell) & ");"
' ....
' surrounds a string by single quotes
Private Function Q(Arg as String) As String
Q = "'" & Arg & "'"
Return
' appens a comma to a string
Private Function T(Arg as String) As String
T = Arg & ","
Return
EDIT 2 I asume that in EXCEL the values you want to insert into the DB are all in 1 row ...
编辑 2 我假设在 EXCEL 中，您要插入数据库的值都在 1 行中...
Suppose you have a source_range which contains more than 1 row, you must fire the INSERT statement for each row in that range. You use the .Rowsproperty to return a single row from a range. And you send multiple columns to the INSERT statement within the same row by using .Cells(1, 1), .Cells(1,2).... and so on
假设您有一个包含多于 1 行的 source_range，您必须为该范围内的每一行触发 INSERT 语句。您可以使用该.Rows属性从范围中返回单行。并且您使用.Cells(1, 1), .Cells(1,2).... 等将多列发送到同一行内的 INSERT 语句
example:
例子：
Sub Test()
Dim MyRange As Range, MyRow As Range
Set MyRange = Range([B4], [C8]) ' source range
For Each MyRow In MyRange.Rows ' get one row at the time from source range
Debug.Print MyRow.Cells(1, 1), MyRow.Cells(1, 2)
' replace the above by your INSERT statement
Next MyRow
End Sub
