Exporting in Excel and Word

[Domino2]

I have a query by form that generates a SQL statement that fills a listbox which is then used for an Access report. That all works okay, but now I need to find an easy way to export the SQL or contents of the listbox into a spreadsheet, and later a word document. Concentrating on Excel first, is there any easy way to do this. I visualise lots of problems as the current Access report is grouped and sorted, don't know this issue can or if be dealt with. Any guidance greatly appreciated.

 

[PHV]

export the contents of the listbox into a spreadsheet
Have a look at the CopyFromRecordset method of the Excel.Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Thankyou PHV. I did a search and found some code that seems to start me off, however if I close the Excel Application after the first time it's been filled, when I try to make it fill Excel the second time, a blank Excel application opens and no data is entered. I thought maybe Excel stayed open, but it was not in the task manager or the task/status bar. Any ideas? Thenks

Dim oXLApp As Excel.Application 'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet

Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook
Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet

oXLSheet.Range("B2").CopyFromRecordset LP.Recordset 'oRecordset
oXLApp.Visible = True

Set oXLBook = Nothing
Set oXLApp = Nothing
Set oXLSheet = Nothing

 

[Domino2]

Good old Microsoft, full of great ideas. I have used the command button wizard in Access 2010, it's user path is Report Actions/Send Report to File. It produces a macro (seems as if VB and VBA wants to be hidden and not learnt, macros).

I have been round in circles trying how to convert the macro to VB, and when I found how was told in a dialogue box it could not be done!!

Does anybody have eqivelent code to whats in the macro please, so I can customize it. Thanks

 

[Domino2]

Still struggling with this. I have changed the coding to se if I get anywhere.

A listbox LP is filled in a query by form.
I am then trying to put the data into Excel. It works on the first pass, but produces a blank Excel sheet the second time. Now having tried to use an rs statement, it halts on the line rs.movefirst, with an error 3420 saying object invalid or no longer set. Anyone any ideas. Access 2010

Private Sub Command407_Click()

Dim rs As DAO.Recordset
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet

Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.ActiveSheet

Set rs = LP.Recordset

rs.MoveFirst

oXLSheet.Range("B2").CopyFromRecordset rs
oXLApp.Visible = True
oXLApp.UserControl = True

Set rs = Nothing
Set oXLBook = Nothing
Set oXLApp = Nothing
Set oXLSheet = Nothing

End Sub