共享工作簿中的ADODB连接和记录集
问题描述:
可以在共享的woorkbook中使用ADODB.Connections和ADODB.Recordset。我尝试运行一个宏,但我有运行时错误:外部表格不是预期的格式。部分代码有效。代码:共享工作簿中的ADODB连接和记录集
Option Explicit
Sub proc()
Dim CnExcel As ADODB.Connection
Dim RstExcel As ADODB.Recordset
Dim user As String
Application.EnableEvents = False
user = Environ("USERNAME") 'that's working
Range("A2").Select 'that's working
Range(Selection, Selection.End(xlToRight)).Select 'that's working
Range(Selection, Selection.End(xlDown)).Select 'that's working
Selection.ClearContents 'that's working
Set CnExcel = New ADODB.Connection
With CnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeRead
.CursorLocation = adUseClient
.Properties("Data Source") = "C:\MB_Form.xlsm"
.Properties("Extended Properties") = ("Excel 8.0;HDR=Yes;IMEX=1")
.Open
End With
Set RstExcel = New ADODB.Recordset
RstExcel.Open "SELECT * FROM [Arkusz1$]", CnExcel
Dim i As Integer
i = 2
Do Until RstExcel.EOF
If Not IsNull(RstExcel.Fields("PESEL").Value) Then
Cells(i, 1).Value = RstExcel.Fields("ID").Value
Cells(i, 2).Value = RstExcel.Fields("Name").Value
Cells(i, 3).Value = RstExcel.Fields("City").Value
i = i + 1
End If
RstExcel.MoveNext
Loop
CnExcel.Close
End Sub
谢谢!
答
你应该使用ACE OLEDB驱动程序和指定的Excel宏12.0的类型:
With CnExcel
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeRead
.CursorLocation = adUseClient
.Properties("Data Source") = "C:\MB_Form.xlsm"
.Properties("Extended Properties") = "Excel 12.0 Macro;HDR=Yes;IMEX=1"
.Open
End With
谢谢!现在它在共享工作簿中工作了! – caruzo 2014-09-23 12:07:55