none
Format Exported Multiple Access (2010) Queries to Excel with Charts RRS feed

  • Question

  • Hi guys,

    I have a project that I'm working on for the past month and got stock towards the end. I created 5 queries and managed to export them from Access (2010) to a single Excel file.

    1. All of my 5 worksheets are missing my formats... and

    2 I have no idea how to add charts to 4 of those summary reports.

    Below is my code. Could you please tell me what I'm doing wrong. (I even tried to copy the macro from excel to get charts but when pasted on Access vba - didn't work)

    Thanks.

    Private Sub cmdREPORT_GenerateUWReport_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

    On Error GoTo cmdREPORT2_err

      ' Dim qdfReportRecordSelection As Object
       Dim appExcel As Excel.Application 'So we can auto-open the created Excel file- set as a generic object for "late binding".
       Dim wbkExcel As Excel.Workbook 'Will be set equal to the specific Excel file that we created.
       Dim wstExcel As Excel.Worksheet 'Will be set equal to the speific Excel worksheet in the workbook we have created.
       Dim rng As Excel.Range  'Dim rngExcel As Object 'Excel range object to deal with column sizes.
      ' Dim strSQL As String
       Dim dblFormattedStartDate As Double 'Contains the input date in YYYYMM format.
       Dim dblFormattedEndDate As Double 'Contains the input date in YYYYMM format.
       Dim strFileSavePath As String 'Contains user seletion for where to save the final spreadsheet.
       Dim strFilter As String 'Contains the file filter sent to the common dialog function.


                                             
       If (IsNull(comboREPORT_StartDate.Value) Or comboREPORT_StartDate.Value = "") Then
          MsgBox ("No Start Date selected.")
          Exit Sub
          
       ElseIf (IsNull(comboREPORT_EndDate.Value) Or comboREPORT_EndDate.Value = "") Then
          MsgBox ("No End Date selected.")
          Exit Sub
          
       End If
        dblFormattedStartDate = Right(comboREPORT_StartDate.Value, 4) & _
                               Left(comboREPORT_StartDate.Value, 2)
                               
       dblFormattedEndDate = Right(comboREPORT_EndDate.Value, 4) & _
                             Left(comboREPORT_EndDate.Value, 2)
          
       If (dblFormattedStartDate > dblFormattedEndDate) Then
          Exit Sub
          
       End If

       
       strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
       strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
       
       strFileSavePath = ahtCommonFileOpenSave( _
          OpenFile:=False, _
          InitialDir:="C:\Documents And Settings\" & fOSUserName() & "\Desktop\", _
          Filter:=strFilter, _
          DialogTitle:="Save file as:", _
          Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY, _
          Filename:="URC_Reports.xls")
          
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C02: Underwriting Audit Case Detail Report Record Selection", strFileSavePath, True, "Detail_Report"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D25: FA_Month", strFileSavePath, True, "FA_Month"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D35: FA_Quarter", strFileSavePath, True, "FA_Quarter"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D45: Policy_Month_Count", strFileSavePath, True, "Policy_Month_Count"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D55: Policy_Quarter_Count", strFileSavePath, True, "Policy_Quarter_Count"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D10: Risk_Issue_Details", strFileSavePath, True, "Risk_Issue_Details"
       
       
       Set appExcel = CreateObject("Excel.Application")
       appExcel.Visible = True
       appExcel.UserControl = True
       Set wbkExcel = appExcel.Workbooks.Open(strFileSavePath)
       Set wstExcel = wbkExcel.ActiveSheet
      
       
       
       
       With appExcel
        .ActiveWorkbook.Sheets("Detail_Report").Cells.Font.Name = "Times New Roman"
        .ActiveWorkbook.Sheets("Detail_Report").Cells.Font.Size = 11
        .ActiveWorkbook.Sheets("Detail_Report").Cells.NumberFormat = "@"
        .ActiveWorkbook.Sheets("Detail_Report").Rows("2:2").Select
        .ActiveWorkbook.Sheets("Detail_Report").ActiveWindow.FreezePanes = True
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").Font.Bold = True
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").Font.ColorIndex = 2
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").Interior.ColorIndex = 12
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").ColumnWidth = 15
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").RowHeight = 40
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").HorizontalAlignment = xlHAlignCenter
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").WrapText = True
        .ActiveWorkbook.Sheets("Detail_Report").Rows("1:1").AutoFilter
        .ActiveWorkbook.Sheets("Detail_Report").ActiveWorkbook.Sheets("Detail_Report").Tab.Color = 1
        
        .ActiveWorkbook.Sheets("FA_Month").Tab.Color = 92
        .ActiveWorkbook.Sheets("FA_Month").Rows("1:1").RowHeight = 40
        .ActiveWorkbook.Sheets("FA_Month").Rows("1:1").WrapText = True
        .ActiveWorkbook.Sheets("FA_Month").Rows("1:1").Font.ColorIndex = 2
        .ActiveWorkbook.Sheets("FA_Month").Rows("1:1").Interior.ColorIndex = 14
        .ActiveWorkbook.Sheets("FA_Month").Rows("1:1").Font.Bold = True
        .ActiveWorkbook.Sheets("FA_Month").Rows("1:1").HorizontalAlignment = xlHAlignCenter
        .ActiveWorkbook.Sheets("FA_Month").Columns("F:K").NumberFormat = "$#,##0"
        .ActiveWorkbook.Sheets("FA_Month").Columns("B:D").NumberFormat = "#.#%"
        .ActiveWorkbook.Sheets("FA_Month").Cells.Font.Name = "Times New Roman"
        .ActiveWorkbook.Sheets("FA_Month").Cells.Font.Size = 10
        .ActiveWorkbook.Sheets("FA_Month").Columns("A:M").EntireColumn.AutoFit
        '.ActiveWorkbook.Sheets("FA_Month").Charts.Add.ChartType = xlCylinderColStacked
        '.ActiveWorkbook.Sheets("FA_Month").ActiveChart.SetSourceData Source:=Range("FA_Month!$A$1:$D$15")
        
        
        .ActiveWorkbook.Sheets("FA_Quarter").Tab.Color = 92
        .ActiveWorkbook.Sheets("FA_Quarter").Rows("1:1").RowHeight = 40
        .ActiveWorkbook.Sheets("FA_Quarter").Rows("1:1").WrapText = True
        .ActiveWorkbook.Sheets("FA_Quarter").Rows("1:1").Font.ColorIndex = 2
        .ActiveWorkbook.Sheets("FA_Quarter").Rows("1:1").Interior.ColorIndex = 14
        .ActiveWorkbook.Sheets("FA_Quarter").Rows("1:1").Font.Bold = True
        .ActiveWorkbook.Sheets("FA_Quarter").Rows("1:1").HorizontalAlignment = xlHAlignCenter
        .ActiveWorkbook.Sheets("FA_Quarter").Columns("C:H").NumberFormat = "$#,##0"
        .ActiveWorkbook.Sheets("FA_Quarter").Cells.Font.Name = "Times New Roman"
        .ActiveWorkbook.Sheets("FA_Quarter").Cells.Font.Size = 10
        .ActiveWorkbook.Sheets("FA_Quarter").Columns("A:M").EntireColumn.AutoFit
       
        .ActiveWorkbook.Sheets("Policy_Month_Count").Tab.Color = 246
        .ActiveWorkbook.Sheets("Policy_Month_Count").Rows("1:1").RowHeight = 40
        .ActiveWorkbook.Sheets("Policy_Month_Count").Rows("1:1").WrapText = True
        .ActiveWorkbook.Sheets("Policy_Month_Count").Rows("1:1").Font.ColorIndex = 2
        .ActiveWorkbook.Sheets("Policy_Month_Count").Rows("1:1").Interior.ColorIndex = 49
        .ActiveWorkbook.Sheets("Policy_Month_Count").Rows("1:1").Font.Bold = True
        .ActiveWorkbook.Sheets("Policy_Month_Count").Rows("1:1").HorizontalAlignment = xlHAlignCenter
        .ActiveWorkbook.Sheets("Policy_Month_Count").Cells.Font.Name = "Times New Roman"
        .ActiveWorkbook.Sheets("Policy_Month_Count").Cells.Font.Size = 10
        .ActiveWorkbook.Sheets("Policy_Month_Count").Columns("A:M").EntireColumn.AutoFit
       
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Tab.Color = 246
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Rows("1:1").RowHeight = 40
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Rows("1:1").WrapText = True
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Rows("1:1").Font.ColorIndex = 2
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Rows("1:1").Interior.ColorIndex = 49
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Rows("1:1").Font.Bold = True
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Rows("1:1").HorizontalAlignment = xlHAlignCenter
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Cells.Font.Name = "Times New Roman"
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Cells.Font.Size = 10
        .ActiveWorkbook.Sheets("Policy_Quarter_Count").Columns("A:M").EntireColumn.AutoFit
        
       End With
       
    cmdREPORT2_err:

       Exit Sub


    End Sub

    Monday, July 27, 2015 8:55 PM

All replies

  • Hi Boby,

    1/. Transfer Spreadsheet does not transfer formatting as you see.

    In Access VBA screen do you have a reference to Excel.

    Does your code Compile without errors in Access?


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Wednesday, July 29, 2015 11:40 PM