使用 VBA/ADO 更新 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12002805/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-11 17:20:54 来源:igfitidea点击:
Update an excel sheet using VBA/ADO
提问by vkrams
When I execute this function I get a run time error, "Operation must use an updateable query". What is causing it?
当我执行这个函数时，我得到一个运行时错误，“操作必须使用一个可更新的查询”。是什么原因造成的？
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
constConfigFile = "MyWorkbookName"
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
strQuery = "update [test$] Set [test]='Hello' WHERE [Test]='h'"
objMyCmd.CommandType = adCmdText
objMyCmd.CommandText = strQuery
objMyCmd.ActiveConnection = cnn
objMyCmd.Execute
Set objMyCmd = Nothing
Set cnn = Nothing
End Function
回答by Fionnuala
Get rid of IMEX=1 from your connection string. That works for me.
从您的连接字符串中删除 IMEX=1。这对我行得通。
.ConnectionString = "Data Source=" & ActiveWorkbook.Path & constConfigFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
