透过ADO存取xls档案的方法

 

    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=D:\BACKUP\28F95.xls;Extended Properties=Excel 8.0;"
        .Open
    End With
    
    Dim SQLTXT As String

    'AAA代表工作表名稱
    SQLTXT = "SELECT * FROM [AAA$] WHERE ([廠商代碼]=B0006)"
    
    Dim rs As Object
    Set rs = CreateObject("ADODB.RecordSet")
    With rs
        .CursorLocation = adUseClientBatch
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .ActiveConnection = cn
    End With