使用VBA将数据从Excel插入Access [英] Insert Data from Excel into Access using VBA

问题描述

我已经做了一些代码将excel表中的数据插入访问数据库 - 我的代码如下：

  Sub AddData（）
 
 Dim Cn As ADODB.Connection 
 
 Set Cn = New ADODB.Connection 
 
'让我们先连接到工作簿，我测试了这个，它适用于我
 Cn.OpenProvider = Microsoft.Jet.OLEDB.4.0; Data Source = sample.xls;扩展属性= Excel 8.0; _ 
& Persist Security Info = False
 
'将工作簿Sheet1的数据附加到mydb.mdb的Table1：
 Cn.ExecuteINSERT INTO tblSales IN'C：\Users\User \Documents\access.mdb'SELECT * FROM [datasheet]
 
 Cn.Close 
 Set Cn = Nothing 
 
 End Sub 
  

我的问题是执行此时，我收到错误Microsoft Jet Engine找不到对象的路径数据表。只是数据位于我的工作簿中的工作表的名称，任何帮助都非常感激。

解决方案

你在这张[$ sheet $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $

I've made some code to insert data from an excel table in to an access database - my code is as follow:

    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.

解决方案

What happens if you put a $ sign after the sheet name like this [datasheet$] ?

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