VBA code to export to Excel a dynamic list from a form

[mfaqueiroz]

Hello,
I'm searching for ways to create a button that export one list from a form to Excel ( the list is dynamic, i.e appears after the user choose through combo boxes the condictions).

But i didn't find a code that works fine....do you know how can i do that?


Thanks for your help!

 

[sneuberg]

What do you mean by list? If you can form this list into a query or report I suggest trying the DoCmd.OutputTo Method.

 

[mfaqueiroz]

I mean a list box that changes according the user choice in the combobox, this listbox is connected to a query and the combo box works as filters...

 

[sneuberg]

Then I think you should be able to create a query that's filtered the same way the list box row source is and use DoCmd.OutputTo to export it.

 

[mfaqueiroz]

Sneuberg, thanks for your help.
Sorry for my little knowledge. But how can i do this query that is filtered in the same way the list box row source is?
An example of an issue I've posted here:
-I have two combo boxes where the user choose the month and the year and i fill the list box with the follow code:

Private Sub LoadTable_Click()

DoCmd.SetWarnings False

Dim strsql As String

Month11 = Me.Month1
Year11 = Me.Year1


strsql = "SELECT * FROM Historic WHERE year([MachineDate])< "& year11 &" or (year(MachineDate))="& year11 &" and month(MachineDate)< "& Month11 &" );"

Me!List.RowSource = strsql

Me.Refresh

End Sub

Thanks for your help!

 

[sneuberg]

I suggest creating a querydef from your SQL and then use it in the DoCmd.OutputTo statement. You will need to delete this querydef each time so you'll need a function to determine if it already exists. Copy and paste the follow function into your form module

Private Function QueryExists(qryName As String) As Boolean

Dim qdf As DAO.QueryDef
QueryExists = False
For Each qdf In CurrentDb.QueryDefs
  If qdf.Name = qryName Then
     QueryExists = True
     Exit For
  End If
Next

End Function

Then after the line

strsql = "SELECT * FROM Historic WHERE year([MachineDate])< "& year11 &" or (year(MachineDate))="& year11 &" and month(MachineDate)< "& Month11 &" );"

You can add this code

Dim qdef As DAO.QueryDef
Dim strQueryName As String
strQueryName = "qryHistoryTemp"
If QueryExists(strQueryName) Then
    CurrentDb.QueryDefs.Delete strQueryName
End If
Set qdef = CurrentDb.CreateQueryDef(strQueryName, strsql)
DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, "[COLOR="RoyalBlue"]C:\Users\sneuberg\Desktop\History.xls[/COLOR]"

noting that this path need to be change to what you want.

Of course the line

DoCmd.OutputTo acOutputQuery, "qryHistoryTemp", acFormatXLS, "[COLOR="RoyalBlue"]C:\Users\sneuberg\Desktop\History.xls[/COLOR]"

could be move somewhere else.

 

[mfaqueiroz]

Thanks Sneuberg for your help and time.
But I still dont understand...

1.
-This code i should put in Form->Event->On Current->[Event Procedure]?
-the query Name, i should create one physic query? if yes how can i change the values through the combobox?

Private Function QueryExists(qryName As String) As Boolean

Dim qdf As DAO.QueryDef
QueryExists = False
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = qryName Then
QueryExists = True
Exit For
End If
Next

End Function

2. I leave this code in same place as before?( associated to the button that load the tables)

strsql = "SELECT * FROM Historic WHERE year([MachineDate])< "& year11 &" or (year(MachineDate))="& year11 &" and month(MachineDate)< "& Month11 &" );"

Thanks in advance for your time, help and share!

 

[sneuberg]

Thanks Sneuberg for your help and time.
But I still dont understand...

1.
-This code i should put in Form->Event->On Current->[Event Procedure]?

No, put the QueryExists function in the form module with the LoadTable_Click code.

-the query Name, i should create one physic query? if yes how can i change the values through the combobox?

You don't need to create one physical query. This creates a query named qryHistoryTemp from the listbox Row Source SQL that controls what's seen in the listbox. This query is recreate each time the code in the LoadTable_Click is run. You should see this query in the navigation pane after you run the LoadTable_Click code. The query will alway produce what is shown in the listbox.

2. I leave this code in same place as before?( associated to the button that load the tables)

The LoadTable_Click should look like:

Private Sub LoadTable_Click() 

Dim qdef As DAO.QueryDef
Dim strQueryName As String

DoCmd.SetWarnings False 

Dim strsql As String 

Month11 = Me.Month1 
Year11 = Me.Year1 


strsql = "SELECT * FROM Historic WHERE year([MachineDate])< "& year11 &" or (year(MachineDate))="& year11 &" and month(MachineDate)< "& Month11 &" );" 

Me!List.RowSource = strsql 

Me.Refresh 
strQueryName = "qryHistoryTemp"
If QueryExists(strQueryName) Then
    CurrentDb.QueryDefs.Delete strQueryName
End If
Set qdef = CurrentDb.CreateQueryDef(strQueryName, strsql)

End Sub


Private Function QueryExists(qryName As String) As Boolean

Dim qdf As DAO.QueryDef
QueryExists = False
For Each qdf In CurrentDb.QueryDefs
  If qdf.Name = qryName Then
     QueryExists = True
     Exit For
  End If
Next

End Function

To create the Excel output create a button and put the following code in it.

DoCmd.OutputTo acOutputQuery, "qryHistoryTemp", acFormatXLS, "[COLOR="Blue"]C:\Users\sneuberg\Desktop\History.xls[/COLOR]"

Change the file path to what you want

 

[mfaqueiroz]

it is solved!!
thanks Sneberg for share your knowledge