List box list to open macros -export query to xls

[optionone]

Hi,

Not sure if this is the best way to do this - happy to hear other suggestions - but was wondering if its possible to create a list of reports in a listbox that the user could select and press a button to launch a macro specific to the selection which would output the query to an xls

Was orginally going to just stick a few buttons up linked to the macros but the list of reports wanted has grown considerably and i think a listbox or something similar would look far better

Thanks

 

[CBrighton]

Fairly easily. I have done something similar with a combobox.

The way I would do it was to create a table with 2 fields, one containing the report name (this can be PKif you want a PK) the second being what I want the display name as in the list box.

I then set the list box recordsource to both fields of the table and the column count to 2 with the report name in the bound column.

It's then as simple as using the .SelectedItem property of the combo box as the form to open with:

dim strReport as string
set strReport = lstListBox.selectedItem
docmd.openreport strReport, AcPreview

:edit:

After rereading the OP, replace the DoCmd.OpenReport with your TransferSpreadsheet / OutputTo function and put query names in the table instead of report names.

 

[optionone]

Many thanks

 

[optionone]

Just in case anyone else reads this post - I couldnt find selecteditem so instead did the following:

Dim strReport As Object
Set strReport = Me.<listbox>

Then outputted with outputto and acOutputQuery

 

[vbaInet]

It was just a little typo. He meant ItemsSelected.