Export A Dynamic Query TO Excel

[mrgulic]

I have 2 tables. One contains exchange distribution list names and the other contains the members.

I have been trying to export each list to its own excel spreadsheet but I dont want to create a query for each one. I have been trying to do it with a dynamic query but I can't get the following to accept it.

DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, strLocalPath & objName & ".xls"

It works fine if the strSQL variable is the name of a real query and not a SELECT string (which is what I need it to be).

I hope that I have explained this well enough to convey what I am trying to accomplish. Thanks

 

[rjoubert]

So you want to export each table to it's own Excel spreadsheet? Try this...

DoCmd.OutputTo acOutputTable, "YourTable", acFormatXLS, "C:\ExcelFileName.xls"

 

[CautionMP]

TheAccessHack,
Here is the beginings of a custom function that may work for you. I called it like:
[tt]Call AdHocToExcel("Select * FROM tblProcedures;", "C:\AdhocTest.xls", True, True, "TestTab")[/tt]

Sub AdHocToExcel(SQLString As String, WorkbookName As String, IncludeFieldNames As Boolean, AutoStart As Boolean, Optional WorksheetName As String)
On Error Resume Next
Dim appExcel As Object, objWorkbook As Object, objWorksheet As Object
Dim dbsCurrent As DAO.Database
Dim rstOutput As DAO.Recordset
Dim blnSpawnedExcel As Boolean
Dim lngRow As Long, lngColumn As Long
Dim strWorkbookPathName As String

Set dbsCurrent = CurrentDb
Set rstOutput = dbsCurrent.OpenRecordset(SQLString)
If rstOutput.RecordCount = 0 Then
  GoTo Clean_up
End If

'Check if Excel is open and grab or create
Set appExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then
  Set appExcel = CreateObject("Excel.Application")
  blnSpawnedExcel = True
  Err.Clear
ElseIf Err.Number <> 0 Then
  Err.Raise Err.Number, Err.Description
  GoTo Clean_up
End If

Set objWorkbook = appExcel.Workbooks.Add
Set objWorksheet = objWorkbook.worksheets.Add
'So the user does not see the data dumping to the workbook
objWorksheet.Visible = False
If IsNull(WorksheetName) Then
  objWorksheet.Name = "Ad Hoc"
Else
  objWorksheet.Name = WorksheetName
End If

'Write the data by cycling through each record, and each column
Do
  lngRow = lngRow + 1
  For lngColumn = 0 To rstOutput.Fields.Count - 1
    If IncludeFieldNames And lngRow = 1 Then
      objWorksheet.Cells(lngRow, lngColumn + 1) = rstOutput.Fields(lngColumn).Name
    Else
      objWorksheet.Cells(lngRow, lngColumn + 1) = rstOutput.Fields(lngColumn)
    End If
  Next lngColumn
  rstOutput.MoveNext
Loop Until rstOutput.EOF

objWorkbook.SaveAs WorkbookName
If AutoStart Then
  objWorksheet.Visible = True
  objWorksheet.Activate
  blnSpawned = False
Else
  objWorkbook.Close
End If

Set objWorksheet = Nothing
Set objWorkbook = Nothing
If blnSpawnedExcel Then
  appExcel.Quit
End If

Clean_up:
rstOutput.Close
Set rstOutput = Nothing
Set dbsCurrent = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

CMP, why not use the Excel.Range.CopyFromRecordset method ?

 

[CautionMP]

PHV,
Never used it before but it seems like a valid solution.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mrgulic]

Sorry, I have been out of the office.

I want to export for example, all records from table2 that are associated with a record from table1, exported as an excel files named the same as the value of the table1 record. Make sense? I am probably not explaining it well.

Perhaps I can explain it better.

Export all data FROM table2 that is related to a single record in table1. Export as the value of one of the cells from table1.

Table 1 Example Data
--------------------
DID   dlName
--------------------
01    .dl employees
02    .dl contractors
03    .dl Building 02
04    .dl Corporate
05    .dl HR

Table 2 Example Data
-------------------------------------
ID   FName          LName         DID
-------------------------------------
01   Gracy Lu       Freebush      02
02   John           Doe           01
03   Bob            Richard       02 
04   Jimmy          Johns         02
05   Jane           Doe           01 
06   Ricky          Bobby         02 
07   Sub            Way           02  
08   Gracy Lu       Freebush      04
09   John           Doe           05
10   Bob            Richard       03

I realize that a table in between (to hold the multiple membership info {gracey in dl 02 and 04)) would be best to reduce data duplication but I am not concerned at this point.

Sample Export of Data (for excel file dl_contractors.xls)
-------------------------------------
ID   FName          LName         DID
-------------------------------------
01   Gracy Lu       Freebush      02
03   Bob            Richard       02 
04   Jimmy          Johns         02
06   Ricky          Bobby         02 
07   Sub            Way           02

Now this export would be named as the associated DID from 'table 1' which is '.dl contractors'. The exported file would be named something like dl_contractors.xls.

 

[CautionMP]

TheAccessHack,
Did you try the function I posted? It emulates the built in [tt]OutputTo[/tt]/[tt]TransferSpreadsheet[/tt] functions but allows you to feed it a SQL string instead of a table/query name.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mrgulic]

Not yet, I wanted to ensure that I explained what I needed better before spending hours trying to implement something that was never meant to work that way in the forst place. From the sounds of it, it should work splendidly.