英文:
Why Excel ADO cannot connect to a file
问题
我正在使用Windows-11上的Excel 2010。
我的问题是；下面的简单ADO代码返回一个错误，说：
"对象'_Connection'的方法'Properties'失败"
在这一行上："adoCN.Properties("Data Source") = myFile"
该文件位于C:\TestFolder。
Sub Test()
'Haluk - 19/07/2023
Dim adoCN As Object, RS As Object
Dim myFile As String, strSQL As String
myFile = ThisWorkbook.FullName
Set adoCN = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
adoCN.Properties("Data Source") = myFile
adoCN.Properties("Extended Properties") = "Excel 12.0 Macro; HDR=Yes; IMEX=1"
adoCN.Open
strSQL = "Select * From [Sheet1$]"
RS.Open strSQL, adoCN
Sheets("Sheet2").Range("A2").CopyFromRecordset RS
Set RS = Nothing
Set adoCN = Nothing
End Sub
此外，所有与ADO相关的VBA代码都无法正常工作，而这些代码在Excel 2010 / Windows-7上可以正常工作。
我猜测在Win11上发生了一些问题。
英文:
I am using Excel 2010 on Windows-11.
My problem is; the below simple ADO code returns an error saying that;
"Method 'Properties' of object '_Connection' failed
on line "adoCN.Properties("Data Source") = myFile"
The file is located in C:\TestFolder.
Sub Test()
'Haluk - 19/07/2023
Dim adoCN As Object, RS As Object
Dim myFile As String, strSQL As String
myFile = ThisWorkbook.FullName
Set adoCN = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
adoCN.Properties("Data Source") = myFile
adoCN.Properties("Extended Properties") = "Excel 12.0 Macro; HDR=Yes; IMEX=1"
adoCN.Open
strSQL = "Select * From [Sheet1$]"
RS.Open strSQL, adoCN
Sheets("Sheet2").Range("A2").CopyFromRecordset RS
Set RS = Nothing
Set adoCN = Nothing
End Sub
Also none of the ADO related VBA codes are not working which were working on Excel 2010 / Windows-7
I guess something is going wrong with Win11
答案1
得分: 1
问题在卸载并重新安装Office 2010后已解决。
英文:
Problem is solved after uninstalling and reinstalling Office 2010.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论