更新使用VBA/ADO
问题描述:
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
和输入参数的excel工作表具有值 - strQuery = “更新[测试$]将[测试] = '你好' WHERE [测试] = 'H'”更新使用VBA/ADO
当我执行这个函数时,我得到一个运行时错误,“操作必须使用可更新的查询”。
请指教。谢谢
答
摆脱连接字符串中的IMEX = 1。这对我行得通。
.ConnectionString = "Data Source=" & ActiveWorkbook.Path & constConfigFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
什么是strQuery?什么是constConfigFile? – Fionnuala 2012-08-17 09:37:59
strQuery =“update [test $] Set [test] ='Hello'WHERE [Test] ='h'” – vkrams 2012-08-17 09:40:28
constConfigFile是一个具有已关闭工作簿名称的常量。 – vkrams 2012-08-17 09:41:31