使用VBA / ADO更新excel表 [英] Update an excel sheet using VBA/ADO

问题描述

$ _ code函数updateConfigFile（strQuery As String）

Dim cnn As ADODB.Connection
Dim objMyCmd As ADODB.Command

设置cnn =新建ADODB.Connection
设置objMyCmd =新建ADODB.Command

带有cnn

.Provider =Microsoft.Jet.OLEDB.4.0
.ConnectionString =Data Source =& ActiveWorkbook.Path& constConfigFile& ; &安培; _
扩展属性=Excel 8.0; HDR =是; IMEX = 1;
.Open

结束

objMyCmd.CommandType = adCmdText
objMyCmd.CommandText = strQuery
objMyCmd.ActiveConnection = cnn

objMyCmd.Execute


设置objMyCmd =没有
设置cnn =没有


结束函数

输入参数的值为
strQuery =update [test $] Set [test] ='你好'WHERE [Test] ='h'

当我执行这个函数时，我得到一个运行时错误，操作必须使用可更新的查询。

请指教。谢谢

解决方案

从连接字符串中删除IMEX = 1。这对我来说很有用。

  .ConnectionString =Data Source =& ActiveWorkbook.Path& constConfigFile& ; &安培; _ 
扩展属性=Excel 8.0; HDR =是;; 
  

Function updateConfigFile(strQuery As String)

Dim cnn As ADODB.Connection         
Dim objMyCmd As ADODB.Command

    Set cnn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command

    With cnn

    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ActiveWorkbook.Path & constConfigFile & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    .Open

    End With

objMyCmd.CommandType = adCmdText
objMyCmd.CommandText = strQuery
objMyCmd.ActiveConnection = cnn

objMyCmd.Execute


Set objMyCmd = Nothing
Set cnn = Nothing


End Function

And the input parameter has value - strQuery = "update [test$] Set [test]='Hello' WHERE [Test]='h'"

When I execute this function I get a run time error , "Operation must use an updateable query".

Please advise. Thank you

解决方案

Get rid of IMEX=1 from your connection string. That works for me.

.ConnectionString = "Data Source=" & ActiveWorkbook.Path & constConfigFile & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"";"

这篇关于使用VBA / ADO更新excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！