filmov
tv
Excel VBA Create SQL Query on Worksheet
Показать описание
This video shows how to setup an SQL Query with VBA Code on an Excel Worksheet. SQL Queries allow you to analyze datasets to answer all sorts of questions for end users.
Learn More about Excel VBA & Macros with this Book on Amazon:
Get a Microsoft Office 365 Personal Subscription with this Amazon link:
DISCLAIMER: As an Amazon Affiliate I get a commission for every purchase via these links.
To get a copy of the Workbook used in this video click the link below and search for the File that has the same title as this video.
#ExcelVBACreateSQLQuery
#ExcelVBACreateQuery
The Code used in this video is below:
Sub myQuery()
Dim connection As New ADODB.connection
connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 12.0;HDR=YES;"";"
Dim SQLQuery As String
SQLQuery = InputBox("Enter Select Statement")
Dim rs As New ADODB.Recordset
rs.Open SQLQuery, connection
Dim rSht As Worksheet
Set rSht = ThisWorkbook.Worksheets("Results")
With rSht
.Cells.ClearContents
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset rs
End With
connection.Close
End Sub
Learn More about Excel VBA & Macros with this Book on Amazon:
Get a Microsoft Office 365 Personal Subscription with this Amazon link:
DISCLAIMER: As an Amazon Affiliate I get a commission for every purchase via these links.
To get a copy of the Workbook used in this video click the link below and search for the File that has the same title as this video.
#ExcelVBACreateSQLQuery
#ExcelVBACreateQuery
The Code used in this video is below:
Sub myQuery()
Dim connection As New ADODB.connection
connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 12.0;HDR=YES;"";"
Dim SQLQuery As String
SQLQuery = InputBox("Enter Select Statement")
Dim rs As New ADODB.Recordset
rs.Open SQLQuery, connection
Dim rSht As Worksheet
Set rSht = ThisWorkbook.Worksheets("Results")
With rSht
.Cells.ClearContents
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset rs
End With
connection.Close
End Sub