Query that runs several times and outputs to Excel

[DanAuber]

I Have a query something like this:

Select * from [Table1} where [Field1] = Manager_Name

(All of the Manager_Names are stored in a Table [Managers])

I want to design a function which -

i)Runs this query and exports it to an Excel file called Manager_Name
ii) Then runs the code again for the next Manager_Name in [Managers]
iii) Repeats this process for all Manager_Names in [Managers]

I'm struggling with writing the code for i) and then getting it to loop

Any help gratefully recieved

Thanks

Dan

 

[ZmrAbdulla]

This is a sample to do this. All the managers name will be in a listbox.

Private Sub Command2_Click()
    Dim x As Integer
    For x = 0 To Me.MyListBox.ListCount - 1
        Me.MyListBox.Selected(0) = True
        MsgBox (Me.MyListBox.Column(0))
       [COLOR=green] 
        'Your procedure to export to excel here
        'Make sure to name the excel files with
        'selected manager[/color]
        
    Next
End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[DanAuber]

Thanks for this ZmrAbdulla. How would I get my managers into the listbox ? and also do you know the VBA code to export a query to Excel ?

thanks for any help

 

[ZmrAbdulla]

Simply drop a listbox on your form (keeping wizards on) and configure it.

To export to excel see "OutputTo Method" in the help file.

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[JerryKlmns]

Dan

If you are to export all of them regardless if there is any data to export, then you need

Public Sub ExportManagers()
Dim rstManagers As ADODB.Recordset
Dim qry As DAO.Querydef

Set rstManagers = New ADODB.Recordset
With rstManagers
   .CursorLocation = adUseServer
   .CorsorType = adOpenForwardOnly
   .LockType = adReadOnly
   .Source = "SELECT DISTINCT Manager_Names FROM Managers;"
   .Open
   Do While Not .EOF
      Set qry = Currentdb.Querydefs("ExportQuery")
      qry.SQL = "SELECT * FROM Table1 WHERE Field1='" & .Fields(0) & "';"
      Set qry = nothing
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "C:\Managers\" & .Fields(0) & ".xls", True, ""   
      .MoveNext
    Loop
    .Close
End With
Set rstManagers = Nothing

I think there is a lot for you to start digging in help. Post if you have a question


I borrowed some code from lameid from thread705-1434937

 

[DanAuber]

Thanks Jerry:

My code is below - it falls over on the .OPen bit with the message: The connection cannot be used to peform this operation - it is either closed or invalid in this context.

Public Sub ExportManagers()
Dim rstManagers As ADODB.Recordset
Dim qry As DAO.Querydef

Set rstManagers = New ADODB.Recordset
With rstManagers
   .CursorLocation = adUseServer
   .CursorType = adOpenForwardOnly
   .Source = "SELECT DISTINCT [Asset Manager] FROM Managers;"
   .Open
   Do While Not .EOF
      Set qry = CurrentDb.Querydefs("ExportQuery")
      qry.SQL = "SELECT * FROM Ass_Test WHERE [Asset Manager]='" & .Fields(0) & "';"
      Set qry = Nothing
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "C:\Temp\" & .Fields(0) & ".xls", True, ""
      .MoveNext
    Loop
    .Close
End With
Set rstManagers = Nothing
End Sub

 

[JerryKlmns]

Oooops!

...
With rstManagers
   [b].ActiveConnection = CurrentProject.Connection[/b]
   .CursorLocation = adUseServer
...

Sorry about that