使用 ADODB 将数据从一个 Excel 文件插入到另一个 Excel 文件

Question

我正在尝试使用adodb将数据从一个 excel 文件插入到另一个 excel 文件，但遇到了插入方法的问题。它给了我如下所示的错误消息：

插入语句中的语法错误

我尝试了不同的日期格式，带引号和不带引号，我也尝试了不同的记录集方法，但这个 sql 语句仍然遇到同样的问题。 有人可以帮助我找出问题所在吗？

Public Sub InsertIntoTable() 
Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:UsersUser1DocumentsExcel filestracker.xls; Extended Properties=""Excel 8.0;HDR=Yes"""

mySQL = "INSERT INTO ResourceTasks (Name, Task, Date, Load) VALUES ('Name11', 'task2'," & CDate("24 / 5 / 2017") & ", 0.50)"
Debug.Print mySQL
objConnection.Execute mySQL
End Sub

我用这段代码从 ResourceTasks 中获取 Name 并将其加载到母版页中;使用非常相似的函数，我检索任务、加载和日期 唯一的区别是屏幕上的格式

我与下一个参数建立了连接：adOpenStatic，adLockReadOnly

Public Sub SearchNames()
Dim oResources As ADODB.Recordset
Dim oDal As New clsDAL
Worksheets("Master").Activate
Set oResources = oDal.GetReadonlyRS("SELECT DISTINCT [Name] FROM [ResourceTasks$]") 'where Date >= **Drop date from selection here**  and Date < **Drop date from selection here**  'where Active like TRUE ")
oResources.MoveFirst
'get all record from record set one by one to 2nd table
Do Until oResources.EOF
RowNumber = 30 + oResources.AbsolutePosition
Range("A" & RowNumber).Value = oResources.Fields("Name").Value 
oResources.MoveNext
Loop
'! get all record from record set one by one to 2nd table
'Close connection and file
oResources.Close
Set oResources = Nothing
oDal.closeFile
Set oDal = Nothing
'! close connection and file
End Sub

谢谢

Answer 1

我认为查询中的Date字段存在语法问题。

&

CDate("24/5/2017"( &...

日期应用#括起来，或者特定于 Excel 可以作为数字传递。

第一个选项：

... VALUES ('Name11', 'task2', #24/5/2017#, 0.50)"
'                              ^^^^^^^^^^^

第二种选择：

... VALUES ('Name11', 'task2'," & CDbl(DateSerial(2017, 5, 24)) & ", 0.50)"
'                                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

此外，如果ResourceTasks是工作表，则应在查询中将其引用为[ResourceTasks$]

INSERT INTO [ResourceTasks$] VALUES...

Answer 2

您可以针对记录集运行查询，然后将其复制到目标范围，从而实现此目的。代码如下所示：

Public Sub InsertIntoTable()
Dim objConnection As Object
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:UsersUser1DocumentsExcel filestracker.xls; Extended Properties=""Excel 8.0;HDR=Yes"""
Dim mySQL As String
mySQL = "INSERT INTO ResourceTasks (Name, Task, Date, Load) VALUES ('Name11', 'task2'," & CDate("24 / 5 / 2017") & ", 0.50)"
Debug.Print mySQL
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Open mySQL, objConnection
ThisWorkbook.Sheets("1").Cells(1, 1).CopyFromRecordset Data:=rs
End Sub

问候

*编辑*从OC更新后。 请注意，我不熟悉命令clsDAL所以我只编辑了将记录集复制到范围。如果要将所有行拉入记录集中，并且只想将其输出到工作表中，则可以使用以下方法：

Public Sub SearchNames()
Dim oResources As ADODB.Recordset
Dim oDal As New clsDAL
Set oResources = oDal.GetReadonlyRS("SELECT DISTINCT [Name] FROM [ResourceTasks$]") 'where Date >= **Drop date from selection here**  and Date < **Drop date from selection here**  'where Active like TRUE ")
ThisWorkbook.Sheets("Master").Range("A31").CopyFromRecordset Data:=oResources
oResources.Close
Set oResources = Nothing
oDal.closeFile
Set oDal = Nothing
'! close connection and file
End Sub

Answer 3

所以我发现由于某种原因，添加新比插入更容易 它看起来像这样;希望它能帮助某人

Public Function InsertNewRecord() As Boolean
Dim oTasks As ADODB.Recordset
Dim oDal As New clsDAL
Set oTasks = oDal.GetUpdateableRS("SELECT * FROM [ResourceTasks$] WHERE Task=''")
With oTasks
.AddNew
.Fields("Name").Value = Sheets("Master").ComboBox24.Value 
.Fields("Task").Value = Sheets("Master").ComboBox25.Value 
.Fields("Date").Value = Sheets("Master").ComboBox23.Value
.Fields("Load").Value = Sheets("Master").TextBox24.Value 
.Update
.Close
End With
Set oTasks = oDal.GetUpdateableRS("SELECT Task FROM [Tasks$] WHERE Task=''")
With oTasks
.AddNew
.Fields("Task").Value = sTask
.Update
.Close
End With
Set oDal = Nothing
Set oTasks = Nothing
End Function