本文介绍了使用VBA / ADO更新excel表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

$ _ 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表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-13 21:35