如何判断Excel用ACE.OLEDB.12.0,wps用JET.OLEDB.4.0

ning84

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName



Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName

1. Function SqlRetuRs1(Str)
   
2.    Dim Cn As ADODB.Connection
   
3.        Set Cn = New ADODB.Connection
   
4.    Dim Rs As ADODB.Recordset
   
5.        Set Rs = New ADODB.Recordset
   
6.         'Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
   
7.         Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
   
8.         Rs.Open Str, Cn, adOpenKeyset, adLockOptimistic
   
9.       
   
10.        Set SqlRetuRs1 = Rs
    
11. End Function
    
12. 
    
13. 
    
14. 
    
15. 
    
16. 
    
17. Sub DELDELDEL()
    
18.    Dim Str, Rs As ADODB.Recordset
    
19. 
    
20.       Str = "Select format(oDate,'yyyy/mm/dd'),BB From [Sheet2$] Where BB is not Null"
    
21.       Str = "Select oDate,Name,Size From [Sheet5$D1:F300]"
    
22.       Str = "Select oDate,Name,oSize From [Sheet5$D1:F30]"
    
23.       Debug.Print Str
    
24.       Set Rs = SqlRetuRs1(Str)
    
25.       Debug.Print Rs.RecordCount, Rs.Fields.Count
    
26.       Rs.MoveFirst
    
27.       For ii = 0 To Rs.RecordCount - 1
    
28.            For jj = 0 To Rs.Fields.Count - 1
    
29.                Debug.Print Rs.Fields(jj),
    
30.            Next jj
    
31.            Debug.Print
    
32.            Rs.MoveNext
    
33.       Next ii
    
34. End Sub
    

复制代码

Select oDate,Name,oSize From [Sheet5$D1:F30]
29            3
2020/9/27 15:15:08          IMG_20200927_151508.jpg      .2           
2020/9/29 17:36:28          IMG_20200929_173628.jpg      .2           
2020/9/29 21:06:51          IMG_20200929_210651.jpg      .3           
2020/9/29 21:06:57          IMG_20200929_210657.jpg      .1           
2020/9/29 21:07:04          IMG_20200929_210704.jpg      .1           
2020/9/29 21:07:09          IMG_20200929_210709.jpg      .2           

牛掰

安装下ACCESS引擎就行了

baofa2

ning84

baofa2 发表于 2024-5-9 14:40

谢谢回复

Debug.Print Application.Version, Application.Path
12.0          C:\Program Files\Kingsoft\WPS Office\11.8.2.12085\office6
**********************************************

1. Function SqlRetuRs(Str)
   
2.    Dim Cn As ADODB.Connection
   
3.        Set Cn = New ADODB.Connection
   
4.    Dim Rs As ADODB.Recordset
   
5.        Set Rs = New ADODB.Recordset
   
6.        Debug.Print Application.Version, Application.Path
   
7.        If Application.Version < 12 Or InStr(Application.Path, "WPS") > 0 Then
   
8.             Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
   
9.        Else
   
10.             Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
    
11.        End If
    
12.        Rs.Open Str, Cn, adOpenKeyset, adLockOptimistic
    
13.        Set SqlRetuRs = Rs
    
14. End Function
    
15. 
    
16. Sub DELDELDEL()
    
17.    Dim Str, Rs As ADODB.Recordset
    
18. 
    
19.       Str = "Select format(oDate,'yyyy/mm/dd'),BB From [Sheet2$] Where BB is not Null"
    
20.       Debug.Print Str
    
21.       Set Rs = SqlRetuRs(Str)
    
22.       Debug.Print Rs.RecordCount, Rs.Fields.Count
    
23.       Rs.MoveFirst
    
24.       For ii = 0 To Rs.RecordCount - 1
    
25.            Debug.Print Rs.Fields(0), Rs.Fields(1)
    
26.            Rs.MoveNext
    
27.       Next ii
    
28. End Sub
    

复制代码