0

I Have a file on D:\SampleData.xlsm containing many entries with columns and headers. I need to query just a specific columns from MyData worksheet within this file. When I am using the approach suggested by @Parfait here:

Excel manufacturing dashboard with vba

I get the error as in attached screenshot. My goal is to pull up from this file columns with headers named Nazwa, Detaliczna, Specjalna, where Stan value in a cell is >= 1.

Please can anyone suggest a solution?

Sub RunSQL()
On Error GoTo ErrHandle
    Dim conn As Object, rst As Object
    Dim strConnection As String, strSQL As String
    Dim i As Integer, fld As Object

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' Hard code database location and name
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "Data Source='D:\SampleData.xlsm';" _
                       & "Extended Properties=""Excel 8.0;HDR=YES;"";"

    strSQL = " SELECT [MyData$].[Nazwa], [MyData$].[Detaliczna]," _
                & " [MyData$].[Specialna]" _
                & " FROM [MyData$]" _
                & " WHERE [MyData$].[Stan] = 1;"

    ' Open the db connection
    conn.Open strConnection
    rst.Open strSQL, conn

    ' column headers
    i = 0
    Worksheets("Results").Range("A1").Activate
    For Each fld In rst.Fields
        ActiveCell.Offset(0, i) = fld.Name
        i = i + 1
    Next fld

    ' data rows
    Worksheets("Results").Range("A2").CopyFromRecordset rst

    rst.Close
    conn.Close

    MsgBox "Successfully ran SQL query!", vbInformation
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " = " & Err.Description, vbCritical
    Exit Sub
End Sub

Error when running the query

Community
  • 1
  • 1
Bartek Malysz
  • 922
  • 5
  • 14
  • 37

1 Answers1

0

Try replacing 'Specialna' with 'Specjalna' in line:

 & " [MyData$].[Specialna]" _
EEM
  • 6,601
  • 2
  • 18
  • 33