locked
How to Select Columns in Excel Using ADO and How to Read a Password-Protected Excel Using ADO? RRS feed

  • Question

  • How can I select in Excel using ADO 1) All records in some columns and 2) Specific records in some columns? I have this code:

    Public cnn As New ADODB.Connection
    Public rs As New ADODB.Recordset

    Private Sub CopyData()

    Dim strFile as String, strSheet as string, FacilityCode As String, i As Integer

    strFile = "" & ActiveWorkbook.Path & Application.PathSeparator & "StorageTanksCalibration.xls"

    strSheet = "Calibration"

    With cnn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & strFile & ";" & _
                "Extended Properties=""Excel 8.0;HDR=No;ReadOnly=True;IMEX=1;"";"
            .Open
    End With

    strSQL = "SELECT * FROM [" & strSheet & "$]"

    rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

    Range("A1").CopyFromRecordset rs

    rs.Close

    cnn.Close

    End Sub

    The above code is working but it copies the entire content of the sheet, hence processing takes too long (70 seconds for 14,000 records and 150 columns).

    Now, I wish to select only some columns where cell value in row 1 begins with "PS" for example using an SQL statement or any other means for as long as it will speed up the processing.

    My alternative is below which I am not happy because this too takes long to process.

    FacilityCode = "PS"  '(example only but this is actually an extracted user's facility code)

    For i = 2 to Range("A1").End(xlToRight).Column

    If Cells(1, i).Value <> FacilityCode And Cells(1, i).Value <> Empty Then
            Cells(1, i).EntireColumn.Delete
            i = i - 1
        End If

    next

    My other question is How to Read a Password-Protected Excel Using ADO?

    I'm a beginner in Excel Macro. Thanks God and to you for helping us.

    With Prayers,

    Dante Valenzuela

    • Moved by Val Mazur Friday, January 4, 2013 2:16 PM Old ADO technology. Not related to ADO.NET (From:ADO.NET Managed Providers)
    Thursday, December 20, 2012 7:23 AM

Answers

  • If the connection to the Excel Worksheet does not specify column headers (HDR=No) then the column names will be F1, F2, F3, etc.

    SELECT F1, F2, F7, F9 FROM [SHEET1$]

    You can also refer to Excel data by a Range value:

    SELECT * FROM [Sheet1$A1:B10]

    If you only want to retrieve a certain number of rows I would try using the TOP keyword:

    SELECT TOP 3 MusicianName AS Musician, Instrument 
    FROM Musicians 

    Unfortunately, Excel implements a database password mechanism that is not compatible with OLEDB.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Alexander Sun Friday, January 4, 2013 8:23 AM
    Thursday, December 20, 2012 12:48 PM