Type your SQL Query in Text Box and Export the output to Excel Worksheet in Access VBA

Suppose you want to type Sql Query in Textbox and export the output of Query In Excel.



Here is the code-

Option Compare Database

Private Sub Command0_Click()
Dim abc As New Excel.Application
Dim wbk As Excel.Workbook
Dim WKS As Excel.Worksheet
Dim S As String
Dim rst As DAO.Recordset
Dim fld As Field
Dim i As Integer
i = 1
abc.Visible = False
abc.ScreenUpdating = False
abc.DisplayAlerts = False


Set wbk = abc.Workbooks.Add
Set WKS = wbk.Worksheets.Add
WKS.Name = "Data"
'**************** delete empty sheets"
wbk.Sheets("Sheet1").Delete
wbk.Sheets("Sheet2").Delete
wbk.Sheets("Sheet3").Delete
'*********************** copy header of table**************************
Set rst = CurrentDb.OpenRecordset(Me.Text15.Value)
abc.Visible = True
abc.ScreenUpdating = False
abc.DisplayAlerts = False
'******************** adding header to excel worksheet

For Each fld In rst.Fields
WKS.Cells(1, i).Value = fld.Name
WKS.Cells(1, i).Interior.Color = vbCyan
WKS.Cells(1, i).Font.Bold = True
i = i + 1
Next fld
rst.MoveFirst
'************************ copy the data from access to excel
WKS.Range("A2").CopyFromRecordset rst

abc.ScreenUpdating = True
abc.DisplayAlerts = True

Set abc = Nothing
Set wbk = Nothing
Set rst = Nothing

End Sub


Access Macro File http://www.filefactory.com/file/cc98618/n/Type_SQL_Query_In_Text_Box_and_export_the_output_to_Excel_1.accdb