Exporting Query to XLS

[Jusenkyo]

Hello all

As the title suggests, I want to export a query I have in an ADP, to an Excel Spreadsheet.
I dont just want to data to appear in its plain form, I want it to go into a template ive already created.
It there a way that I can export a query to a specific Cell/Group of cells in a spreadsheet?

Cheers in advance!
J

 

[PHV]

Why not pull the data from within Excel instead of push ?
When in Excel take a look at menu Data -> External data -> ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jusenkyo]

I am trying to automate this as much as possible for the users, So they can just push a button from within the database, and export the data.

Im not sure if they would know how to import data into excel!

 

[PHV]

You can automate the process from access, using OLE Automation over Excel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jusenkyo]

Would you care to explain?

 

[pbrodsky]

If the users are going to be using the data in Excel, it might be easier to automate it from the Excel side.

 

[bubba100]

You can use this as a start for your needs. We use this to drop the results of a query onto an excel spreadsheet.

Dim Xfile As Excel.Application
Dim strQuery As String
Dim qdf As QueryDef
Dim PRM As Parameter
Dim Message
Dim qryRST As Recordset
Dim DB As Database
Dim ExcelWasRunning As Boolean
Dim bRunning As Boolean
Dim i As Integer
Dim j As Integer
Dim CurrentField As Variant
Dim CurrentValue As Variant
Dim ReportDate As Variant
Dim Sheet As Object
Dim CurrentDate As String
CurrentDate = Now()
Set DB = CurrentDb
ExcelWasRunning = False
strQuery = "qryCountDoltotals"
Set qdf = DB.QueryDefs(strQuery)
For Each PRM In qdf.Parameters
PRM.Value = Eval(PRM.Name)
Next PRM
Set qryRST = qdf.OpenRecordset(dbOpenSnapshot)

Message = MsgBox("Please Wait While The Excel Report Is Created", vbInformation)
FileCopy "C:\OOSIFTAaudits_DB\JurisdictionalAuditSummaries.xls", _
"C:\My Documents\JurisAuditSummary.xls"
On Error Resume Next
Set Xfile = GetObject(, "Excel.Application")
If Err.Number = 0 Then
ExcelWasRunning = True
Xfile.ActiveWorkbook.Save
Xfile.ActiveWorkbook.Close
Message = MsgBox("You Have An Excel File Open, Click OK, The File Will Be Saved And Closed.", vbOKOnly)
End If
Err.Clear
If Xfile Is Nothing Then
Set Xfile = CreateObject("Excel.Application")
bRunning = False
End If

Xfile.Workbooks.Open ("C:\My Documents\JurisAuditSummary.xls")
Xfile.Visible = True
Set Sheet = Xfile.ActiveWorkbook.Sheets(1)
j = 100
Do Until qryRST.EOF
For i = 0 To qryRST.Fields.Count - 1
CurrentField = qryRST(i)
Sheet.Cells(j, i + 1).Value = CurrentField
Next i
qryRST.MoveNext
j = j + 1
Loop
j = 2
i = 4
Sheet.Cells(j, i).Value = [Forms]![frmReports]![txtBgnDate] & " - " & [Forms]![frmReports]![txtEndDate]
Sheet.Cells(7, 9).select

qryRST.Close
Set qryRST = Nothing
Set DB = Nothing
Set qdf = Nothing
End If