Export Form Data in Excel Spreadsheet 1

[grgimpy]

I've created a form that displays two Pivotcharts at a time. I want to create a command button that exports the data in both charts to the same Excel file in separate worksheets.

So far I've come up with the following code:

Dim objExcelApp As Excel.Application
    Set objExcelApp = CreateObject("Excel.Application")

Dim strFrmName As String
    strFrmName = Me.fsubIndividualChart.SourceObject
    
    DoCmd.OutputTo acOutputForm, strFrmName, _
         acFormatXLS, strExcelFileName, True, , False
    
    objExcelApp.Cells.Columns.AutoFit

Which works fine when I export the first chart. Is there a way to add the data from my second Pivotchart (a form) to a different sheet in my already created Excel file.

I'd really like to use the DoCmd.OutputTo routine because it keeps the formatting of the data and such with just the one line of code.

 

[grgimpy]

Here's what I came up with after a little research. I probably could have just copied one workbook into another, but I wanted to create a new workbook that was not automatically saved. This works at least.

'Create Excel Application object
Dim objExcelApp As Excel.Application
    Set objExcelApp = CreateObject("Excel.Application")
'Create New Excel Workbook
'All data will be copied into
'this Workbook later
Dim objExcelWrkBk1 As Excel.Workbook
    Set objExcelWrkBk1 = objExcelApp.Workbooks.Add()
    objExcelApp.Visible = True

'Variables to be used to store
'names of files and forms
Dim strExcelFileName As String
Dim strFrmName As String

'Now we must determine the username of
'the person that is logged into
'the computer in order to save the image
'to the correct "Desktop" address.
'This line of code calls the function above.
Dim strUser As String
    strUser = fWin2KUserName

'Set Filepath name (Desktop) for temporarily
'created Excel workbooks
Dim strFilePath As String
    strFilePath = "C:\Documents and Settings\" & strUser & "\Desktop\"

'Range Chart
    strFrmName = Me.fsubRangeChart.SourceObject
    strExcelFileName = "Chart2.xls"
    DoCmd.OutputTo acOutputForm, strFrmName, _
        acFormatXLS, strFilePath & strExcelFileName, False, , False

'Create another Excel Workbook variable
'where data will be exported to and stored
'temporarily
Dim objExcelWrkBk2 As Excel.Workbook
    Set objExcelWrkBk2 = objExcelApp.Workbooks.Open(strFilePath & strExcelFileName)

'Copy data from Workbook2 into
'master workbook (Workbook1)
    objExcelWrkBk2.Sheets.Copy objExcelWrkBk1.Sheets(1)
'Close temp workbook
    objExcelWrkBk2.Close
'Adjust columns of master Workbook
    objExcelApp.Cells.Columns.AutoFit
'Delete temp Workbook
    Kill (strFilePath & strExcelFileName)

'Individual Chart
    strExcelFileName = "Chart1.xls"
    strFrmName = Me.fsubIndividualChart.SourceObject
    DoCmd.OutputTo acOutputForm, strFrmName, _
        acFormatXLS, strFilePath & strExcelFileName, False, , False

'Create another Excel Workbook variable
'where data will be exported to and stored
'temporarily
    Set objExcelWrkBk2 = objExcelApp.Workbooks.Open(strFilePath & strExcelFileName)
    
'Copy data from Workbook2 into
'master workbook (Workbook1)
    objExcelWrkBk2.Sheets.Copy objExcelWrkBk1.Sheets(1)
'Close temp workbook
    objExcelWrkBk2.Close
'Adjust columns of master Workbook
    objExcelApp.Cells.Columns.AutoFit
'Delete temp Workbook
    Kill (strFilePath & strExcelFileName)