Method to export a unbound listboxs' current displayed records to excel via vba?

[mamradzelvy]

Hi,
I'm looking for the best way to go about this. I have got a unbound form (which i use to input data into one table) that has a listbox on it which displays currently pending items to be invoiced for any given month (meant to replace a physical board we use in our office) and we'd like to be able to print out the selected records to excel at the end of the month.
What is the best way to go about this? To make a query and then just manually export the result? can it be done via VBA aka a "magic do-it-all" button?

 

[theDBguy]

Hi. To export a query to Excel, you can use the TransferSpreadsheet method.

 

[mamradzelvy]

Hi. To export a query to Excel, you can use the TransferSpreadsheet method.

is it possible to skip making the query and just have it export based on a SQL syntax right on the form?

 

[Minty]

Not directly, but you could use a QueryDef to alter a query to your desired SQL and then use that in the TransferSpreadsheet command.

 

[The_Doc_Man]

is it possible to skip making the query and just have it export based on a SQL syntax right on the form?

If there is a .Rowsource and it was created via a combo-box / list-box wizard, there actually IS a stored query. Wizard-generated queries have names beginning with the tilde character (~). You could just export that query if you could find it. If worst comes to worst, open MSysObjects and look for the name of the query. DO NOT EVER UNDER ANY CIRCUMSTANCES EDIT MSysObjects... but it is OK to just look. Our member Isladogs (a.k.a. Colin) has published some threads here on the subject of MSysObjects and when it is OK to "diddle" MSysObjects. (Answer: Not very often.)

The wizard-generated names look like this: ~sq_cXXXX~sq_cYYYY where XXXX is the form's name and YYYY is the name of the field for which the combo box exists. If you have a form for which a wizard generated the .Recordsource, you would find ~sq_fXXXX where XXXX is the form name. And for reports, the .Recordsource would be ~sq_rXXXX where XXXX is the report name.

While you won't see them even as hidden objects under the Queries list, they exist. That leading tilde characters is what hides them so thoroughly.

 

[Uncle Gizmo]

we'd like to be able to print out the selected records to excel at the end of the month.
What is the best way to go about this?

Not exactly what you were looking for but I thought it might be of interest.

Show/Hide & Export to Excel

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

All my download Show/Hide & Export to Excel HERE:- https://gum.co/ExportToExcel

 

[theDBguy]

is it possible to skip making the query and just have it export based on a SQL syntax right on the form?

Hi. I haven't read any of the other posts, so this may have already been answered; but yes, that is possible by using Excel Automation's CopyFromRecordset method. I'll check back later if more details is needed. Cheers!

 

[Cronk]

#1 ... to print out the selected records to excel ....

Loop through the selected records and export each to Excel using automation. Alternatively, generate a string containing the selected records separated by commas and create a query using a SQL statement with using the string to select records.

But why not create the invoices from withing Access?

 

[mamradzelvy]

Hi,
I wasn't able to get the desired effect with any of these tips.
I now made a new form that would just have adjustable parameters for a query, however i'm unable to get this working properly. I tried methods described on youtube, however that didnt work for me either.
I tried my magic SQL code i use on most of my listbox forms for the query, but that does nothing:

SELECT Tabule1.TabKlient, Tabule1.TabOsoba, Tabule1.TabItem, Tabule1.TabQty, Tabule1.TabNote, Tabule1.TabAddedBy, Tabule1.TabDate
FROM Tabule1
WHERE TabKlient LIKE '" & Forms.formExportExcel.xKlientSelection & "';

xKlientSelection being a combo box with the client names.

 

[Cronk]

What do you do with the SQL statement? Show the rest of the code in the procedure.