Use ListBox Rowsource to export .xls

[wazza]

Hi

I have a list box which displays sales figures - the data has been transformed into a more user-friendly layout using sql in a complicated vb process.

On the same form i wish to create a button which the user can press to export the listbox data/layout onto a spreadsheet.

Im hoping for the button to grab the rowsource of the listbox and use this sql to specify the query in the export statement - is this possible? (This will prevent me having to change the already complicated vb process)


ie. i tried this but cannot get it working!

getRowSource = me.salesbox.rowsource

DoCmd.TransferSpreadsheet asExport, asSpreadsheetTypeExcel9, getRowSource, "C:\myfolder\exports

Many Thanks

 

[Mile-O]

Is your RowSource a pre-defined QueryDef or a SELECT statement? If it is the latter then save the statement as an QueryDef.

 

[wazza]

the select statement is not saved as an actual query...
its a statement that is compiled according to many user specifications

The problem is - i will need to create so many different types of queries - to manage all the possible outcomes..

this is why i would like to somehow use the rowsource statement - to export. Alternativly- is it possible to automate the creation of a query object using the copiled sql and temporarily save. I could then refer to the query object name on export.

rgrds

 

[wazza]

jus to clarify that;

- constructed an sql transform-select statement, using a number of varibles according to what the user has selected, this is assigned to a list box

- I want to export the listbox contents onto a spreadsheet

- the query is not a saved/predefined object

- the sql select-transform statement is produced according to user requirements - the query may be very different each time so thats why im taking the approach of getting the row source of the listbox

rgrds

 

[wazza]

- when exporting to a spreadsheet - can u only specify a saved query or table?

- is it possible to export using a query select statement?

- if not - is it possible to automate the creation of a query object - temporarily save the query object for refferal on export

??

 

[IfButOnly]

Wazza,

Access help states- 'In Microsoft Access 2000, you can't use an SQL statement to specify data to export when you are using the TransferSpreadsheet action. Instead of using an SQL statement, you must first create a query and then specify the name of the query in the Table Name argument.'

Try something along these lines which was supplied to me couresty of Jack Cowley...

Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim where As Variant

Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

...All your SQL stuff here...

Set QD = db.CreateQueryDef("Dynamic_Query", YourSQLStringVariableHere)

'Set the List RowSource to Dynamic_Query
'execute your transferspreadsheet action here using the file name (eg Dynamic_Query).

Hope it helps

Peter

 

[wazza]

ive tried working this... but cannot get it working:

getRowSource = me.salesbox.rowsource
Set qryEx = currentDb.CreateQueryDef("", getRowSource)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, qryEx, "C:\myfolder\exports\report.xls", true

 

[IfButOnly]

I not too confident on how and when the Set and CreateQueryDef actions.

I suggest that you try my method where you are specifying the name of an actual query and see if that works.

Luck... peter

 

[fpendino]

Dim qryEX as Querydef



getRowSource = me.salesbox.rowsource
'You must specify the name that you want to save the query as. The querydef will create an actual Query. This will save the query as tempQuery.
Set qryEx = currentDb.CreateQueryDef("tempQuery", getRowSource)

'You can't transfer the actual querydef, you must use the query name that the querydef creates.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "tempQuery", "C:\myfolder\exports\report.xls", true

set qryEX = nothing

'You might want to delete the query once done
DoCmd.DeleteObject acQuery, "tempQuery"


Post what errors you might be getting as well.

 

[wazza]

I have tried the example provided - however i get a compile msg...

"User-defined type not defined"

reffering to
Dim db As DAO.Database

?? thanks

 

[fpendino]

OK.. This should fix that as I just tested it and got the same error. Make sure you have the Code Page up, go to Tools--References and check the Microsoft DAO 3.51 Object Library.

Your library version may be different depending on your version of Access, but just check the latest version you have available in your list. It may actually be listed first, because it is trying to call that library anyways.

 

[wazza]

Excellant - its working!!

Thats helped so much - was planning on creating all the possible queries.. so thats saved so much time!!!

rgrds

 

[fpendino]

Congrads. Glad to help. Hope all else goes well.

 

[fpendino]

Hey, and for future thought, I've been workin with some other threads that have had needs to export to Excel.

I posted a Sample Database a few months ago that you can set Excel as an Object and upon doing so you can access/export/format and do whatever in Excel by doing so.

You could use me.salesbox.column(Index/Column,Row-Optional) to get the values in every row and column in the listbox and send it to a particular cell in an Excel Workbook. If interested just check out a couple of these links.


Sample Database I posted.
http://www.access-programmers.co.uk/forums/showthread.php?t=63291

Other threads I've helped.
http://www.access-programmers.co.uk/forums/showthread.php?t=76212