Access VBA Macros

Home » Uncategorized » Type SQL Query in text box in Access form and export output to Excel

Type SQL Query in text box in Access form and export output to Excel

If you have  created a huge database in MS Access and you are frequently running queries and copy & pasting output to a new excel manually .  Try below given solution / approach to automate the task.

I have created a user form in Access and added text box to it. Using macro we are exporting the output of SQL query written in that text box to a new Excel file. Snapshot below –

 

 

Download Sample file https://www.box.com/s/y7r8rkzs4e2hy2c7nvep

Add below code to command button added to the form 

Option Compare Database

Private Sub Command2_Click()

‘Text0 is the name of text box added to the form
Call export_to_excel(Me.Text0.Value)
End Sub

Add below code to module 1

Option Compare Database
Sub export_to_excel(qry As String)
‘In references add
‘Microsoft ActiveX Data object
‘Microsoft Excel
On Error GoTo errdec:

Dim xla As Excel.Application
Dim wk As Excel.Workbook
Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset
Dim j As Long

Set cn = CurrentProject.Connection
rs.Open qry, cn, adOpenDynamic, adLockOptimistic

If rs.EOF = True Then
MsgBox “No Records found”, vbInformation, “Note:”
rs.Close
cn.Close
Exit Sub
End If

‘************************ copy the data from access to excel
Set xla = New Excel.Application
Set wk = xla.Workbooks.Add

‘ paste fields
For i = 0 To rs.Fields.Count – 1
wk.Sheets(1).Cells(1, i + 1).Value = rs.Fields(i).Name
Next

‘ paste the data
wk.Sheets(1).Range(“A2”).CopyFromRecordset rs
wk.Sheets(1).UsedRange.EntireColumn.AutoFit
xla.Visible = True

Exit Sub
errdec:
MsgBox Err.Description, vbInformation, “Error Description”

End Sub

Try some queries like

select * from customers

select * from customers where [Job Title] =’owner’

customer table is having sample data

Download Sample file https://www.box.com/s/y7r8rkzs4e2hy2c7nvep

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: