Export from a form to excel

[juschuma]

What's the best way to export data to excel from a form...

Here's my situation:

Main form user presses a button. This button should export the results of a query to excel. However, the query uses parameters built in the main form (from listbox, textboxes, etc.)

 

[Beeps]

This is the way that I have done it:

Public Sub exceltransfer()

Dim rs as new ADODB.Recordset
Dim Xcel as Object
Set Xcel = New Excel.Application
Dim Counter as Integer 'Counter for loop
Dim rows as Integer 'Counter for rows

rs.Open "InsertQueryName", CurrentProject.connection, _ adOpenStatic, adLockReadOnly

rs.MoveFirst
Counter = rs.RecordCount
rows = 1
Xcel.Workbooks.Open Application.CurrentProject.Path & "InsertNameofWorkbook"

'Loop through Recordset an insert into Excel
For X = 1 to Counter
Xcel.Application.Worksheets("Sheet1&quot.Cells(rows, 1).Value = rs!Field1
Xcel.Application.Worksheets("Sheet1&quot.Cells(rows, 2).Value = rs!Field2
Xcel.Application.Worksheets("Sheet1&quot.Cells(rows, 3).Value = rs!Field3
Xcel.Application.Worksheets("Sheet1&quot.Cells(rows, 4).Value = rs!Field4

'etc Just change Field1 and so on to your field names of _ your queries

rows = rows + 1

Next X

Set RS = nothing

End Sub

Remember to update your References to include Excel and ADO and change the above Field references and connection string to your situation

Hope it works!

 

[Beeps]

Sorry,

Put the code in a module and have the OnUpdate() event of your button:

Private Sub Button1_Click()

exceltransfer

End Sub

Guess I shouuld proof read my post before submitting

 

[juschuma]

Thanks for the advice.

I ended up doing something different. I build a form that returned results from a query. This way when I opened the form I could pass in a where clause that I built from the main form. On the subform open event I inserted:

DoCmd.OutputTo acOutputForm, , acFormatXLS
DoCmd.Close acForm, "MyFormName", acSaveNo


This worked like a charm. The user never sees the form open and it's easy to reformat if I need to.