Use listbox to choos Query to Export?? (1 Viewer)

[firestorm998]

OK so I can figure out the basic export to Excel macro in access and have found some examples of transferspreadsheet macro's but none where the table/query souce is not pre-determined.

I have ListboxA showing a list of reports, 'Report A', 'Report B' etc. I have a button which I am trying to set up to export the report selected in ListboxA.

Select Report B in list box, click button, Report B exported to Excel.

I'd appreciate it if anyone could let me know how to include this variable record source into the transferspredsheet function??

Many thanks !

 

[Kiwiman]

Howzit

Something like this...

Private Sub cmdReport_Click()

' Dimension the variables
Dim strDocName As String
Dim strFileName As String

' Depending on which list option is selected, export the relevant query
Select Case Me.lstRerportList
    Case Is = "Report A"
       strDocName = "qryMake"
    Case Is = "Report B"
        strDocName = "qryTrue"
    Case Is = "ReportC"
        strDocName = "qryFalse"
    Case Else
        strDocName = "qryMake"
End Select

' Build the file name
strFileName = "C:\Sample Database\"             ' the default location
strFileName = strFileName & strDocName          ' add the query name
strFileName = strFileName & ".xls"              ' add the file extension

' Transfer query to location
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, strFileName, True


End Sub

 

[firestorm998]

Thanks for the quick comeback!

my example was a bit simplistic, I actually have many reports in the listbox which can grow each day. Looking at this code, if i understand it corrrectly I'd have to write a new 'Case IS' for each and every option which would be a bit of a drag.
I was hoping there was something as simple as the DoCmd.... 'Me.List' expression to choose the query?

 

[Kiwiman]

Howzit

If your listbox values are exactly the same as the queries you want to run then you could use the following...This is untested, as I haven't done this before.

Private Sub cmdReport_Click()

' Dimension the variables
Dim strDocName As String
Dim strFileName As String

' Depending on which list option is selected, export the relevant query

strDocName = Me.lstRerportList

' Build the file name
strFileName = "C:\Sample Database\"             ' the default location
strFileName = strFileName & strDocName          ' add the query name
strFileName = strFileName & ".xls"              ' add the file extension

' Transfer query to location
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, strFileName, True


End Sub

 

[datAdrenaline]

You can definately avoid the use of Select..Case.

What is the structure of your list box? and how it is populated? ..

Basically, you probably want the list box source to have two columns (one for the Report Name, the other for the Query Name). Note that you can set the column width to 0 so the users can not see it. Then in your code you can refer to the column that has the query name in it using the .Column property...

Me.ListBox.Column(0)

Note that to the .Column property is Zero based, so asking for the value in the 1st column (whether hidden or not) would be .Column(0), the 2nd column would be .Column(1).

...

Hope that helps!

 

[Kiwiman]

Howzit

Sterling reply Brent - i best have a closer look at list boxes. Haven't really used them a great deal upto now.

 

[firestorm998]

Thanks guys that works great, just need to get the file to open automatically once the export has finished...??

 

[datAdrenaline]

You can use the Shell() function to open Excel from Access/VBA