Code for Exporting a pivot table into Excel

[lesley83]

I have a query, in which is shown in pivot table view and I want to know the code for this to exporting the table into Excel.

(The query is actually on a subform, so I can just add a button to the form to run a macro which will export the table)

Any suggestions?

 

[CautionMP]

lesley83,
Something like this may work for you, it could easily be fired from a button click on the form.

Sub ToExcel()
  Dim objExcel As Object
  Dim objWorkbook As Object
  Dim objWorksheet As Object
  
  Set objExcel = CreateObject("Excel.Application")
  
  'Create a new workbook to hold the data
  Set objWorkbook = objExcel.Workbooks.Add
  '# OR open an existing workbook
  '# Set objWorkbook = objExcel.Workbooks.Open("C:\OldBook.xls", False, False)
  
  'Grab the first worksheet in the workbook (Item 1)
  Set objWorksheet = objWorkbook.Worksheets(1)
  '# OR grab a specific worksheet
  '# Set objWorksheet = objWorkbook.Worksheets("Sheet1")
  
  'Drop the data staring at cell A1
  objWorksheet.Range("A1").CopyFromRecordset [b]Me.Subform.Recordset[/b]
  
  Set objWorksheet = Nothing
  'Close and name the new workbook
  objWorkbook.Close True, "[i]C:\TestOut.xls[/i]"
  '# OR save the existing
  '# objWorkbook.Close True
  Set objWorkbook = Nothing
  objExcel.Quit
  Set objExcel = 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)

 

[stevefutcher]

I have a similar problem, but rather than exporting the results of a query from a subform, I am amending data in a table using a subform, and need to update an excel spreadsheet with the results of a query based on the modified information in that table.

The code above looks almost ideal, except I can't figure out the syntax to refer to the query I want in place of the

[b]Me.Subform.Recordset[/b]

code

Any ideas anyone?