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