iT邦幫忙

0

80040e10 ODBC EXCEL DRIVER

您好如下圖:
出現 80040e10 ODBC EXCEL DRIVER
最後 .Open 時卡住
這有辦法解嗎?
謝謝!

---->目前發現,是原有XLS內有設定小計

https://ithelp.ithome.com.tw/upload/images/20211023/20104095u9VGy5WVmX.png

Sub A_Click()

   

   ' ActiveSheet.UsedRange.AdvancedFilter _

    Action:=xlFilterCopy, Unique:=True, _

    CopyToRange:=ActiveSheet.Range("H1")

   

    

 

 

 

 

    Dim myCon     As New ADODB.Connection

    Dim myRst     As New ADODB.Recordset

    Dim myCnc     As String

    Dim myCmd     As String

    Dim myFileName As String

 

   AutoFilter_4B3_ERROR_Count = False

   

    

    

    Filename = ThisWorkbook.Name

    shName = "Sheet1"

   

    

    myCnc = "Driver={Microsoft Excel Driver (*.xls)};" & _

            "DBQ=" & ThisWorkbook.Path & "\" & Filename & ";"

    '以SQL來指定讀入資料

   

        'myCmd = " select ERROR,count(*) from [" & shName & "$]  where ERROR in ('Y','Q','P')  group by ERROR  "

        myCmd = " select distinct [排除],[部門別] from [" & shName & "$B2:C100]  where [排除] <>''    "

   

 

   

    'Debug.Print myCmd

   

        

    myCon.Open "Provider=MSDASQL;" & myCnc

   

    

 

  

    With myRst

        .ActiveConnection = myCon

 

        .Source = myCmd

       

        .CursorType = adOpenStatic  '必須用adOpenStatic 才能得到RCount

        .Open

    End With

  

   Debug.Print myRst.RecordCount '有幾筆

  

    

    

End Sub

尚未有邦友回答

立即登入回答