vba code to export a query to excel

[joeythelips]

Hi,

I have a crosstab query that i want to export to excel.
I would use an 'output to excel macro' except it overwrites the existing file (which contains some charts)

What i would like to do is click a button on a form and export the query to excel.

Does anyone have the code for this?

Thanks

Joe

 

[GHolden]

This code will open an existing Excel Spreadsheet and write data to specified cells.
You need to include a reference to the Microsoft Excel Object Library.

Private Sub mWriteToExcel()
Dim ExcelApp As New Excel.Application
Dim intColIndex As Integer
Dim intRowIndex As Integer
Dim intCounter As Integer
Dim db As Database
Dim rst As Recordset

'Open query as recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("QQuestionAnswer", dbOpenDynaset)

With ExcelApp
'open excel with file D:Test
.Workbooks.Open "D:Test"
.Visible = True

'Set start column and row
intColIndex = 0
intRowIndex = 5

'loop through recordset and write fields to cells
Do While Not rst.EOF
.Cells(intRowIndex, intColIndex + 1) = rst!QuestionCode
.Cells(intRowIndex, intColIndex + 3) = rst!Question
.Cells(intRowIndex, intColIndex + 3) = rst!AnswerCode
.Cells(intRowIndex, intColIndex + 4) = rst!Answer
intRowIndex = intRowIndex + 1
rst.MoveNext
Loop
'Close Excel
.ActiveWorkbook.Close True
.Quit

End With
rst.Close
Set rst = Nothing
Set ExcelApp = Nothing

End Sub

 

[BlackKnight]

Hi,

You can try this but it will overwrite if you use the same file. I am not sure you can export to a selected group of cells?

Private Sub cmdExportQuery_Click()

Dim NewFile As String

NewFile = InputBox$("Enter a new path and filename for the spreadsheet", "Export to Excel", "export.xls&quot

DoCmd.TransferSpreadsheet acExport, _ acSpreadsheetTypeExcel9, "qryGetResults", NewFile

End Sub

Have a good one!
BK

 

[joeythelips]

Hi,

Thank you both for your time and help.

I am trying GHOLDENS code at the moment, but i get an error
'user defined type not defined' and the Dim DB AS Database is highlighted.

any ideas?

Joe