Export and format multiple sheets from Access to Excel

Question

I'm trying to export multiple summary reports from Access to Excel. The source data is in Access. The form where the end user create these reports by a click of a button is also in Access. I'm facing 2 difficulties and wonder if you can help:

I successfully manage to export my reports but for some reason my formats are not implemented - Could you please tell me why and propose an idea of how can I fix this.
Along with my reports I would like to add charts. Would you please help me understand of how do I even start with this.

*** So far what I did is macro vba in excel and somehow figured out how to translate it in access. Why is it so different and how come I can't use the same language syntax? I'm fairly new to programming but logically since Microsoft Office created both environments - shouldn't the language be the same? I mean I would assume why many users prefer to buy their products - Ironically - my theory doesn't support my assumption.... please help

Here is my code:

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

On Error GoTo cmdREPORT2_err

   Dim appExcel As Variant
   Dim wbkExcel As Object
   Dim wstExcel As Object
   Dim dblFormattedStartDate As Double
   Dim dblFormattedEndDate As Double
   Dim strFileSavePath As String
   Dim strFilter As String


   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
      MsgBox ("Start Date is greater than End Date.")
      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, "D60: DetailReportDonna", strFileSavePath, True, "Detail_Report"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D24: FA_Month", strFileSavePath, True, "FA_Month"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D34: FA_Quarter", strFileSavePath, True, "FA_Quarter"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D40: Policy_Month_Count", strFileSavePath, True, "Policy_Month_Count"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "D50: 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
   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").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").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").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").Columns("A:M").EntireColumn.AutoFit
    .ActiveWorkbook.Sheets("FA_Month").Columns("C:H").NumberFormat = "$#,##0"
    .ActiveWorkbook.Sheets("FA_Month").Cells.Font.Name = "Times New Roman"
    .ActiveWorkbook.Sheets("FA_Month").Cells.Font.Size = 11
    .ActiveWorkbook.Sheets("FA_Month").Cells.HorizontalAlignment = xlHAlignRight

    .ActiveWorkbook.Sheets("FA_Quarter").Tab.Color = 92
    .ActiveWorkbook.Sheets("FA_Quarter").Rows("1:1").RowHeight = 40
    .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").Columns("A:M").EntireColumn.AutoFit
    .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 = 11
    .ActiveWorkbook.Sheets("FA_Quarter").Cells.HorizontalAlignment = xlHAlignRight

    .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").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").Columns("A:M").EntireColumn.AutoFit
    .ActiveWorkbook.Sheets("Policy_Month_Count").Cells.Font.Name = "Times New Roman"
    .ActiveWorkbook.Sheets("Policy_Month_Count").Cells.Font.Size = 11
    .ActiveWorkbook.Sheets("Policy_Month_Count").Cells.HorizontalAlignment = xlHAlignRight

    .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").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").Columns("A:M").EntireColumn.AutoFit
    .ActiveWorkbook.Sheets("Policy_Quarter_Count").Cells.Font.Name = "Times New Roman"
    .ActiveWorkbook.Sheets("Policy_Quarter_Count").Cells.Font.Size = 11
    .ActiveWorkbook.Sheets("Policy_Quarter_Count").Cells.HorizontalAlignment = xlHAlignRight

   End With

cmdREPORT2_err:

   Exit Sub

End Sub

You probably need to add the reference to Excel in access. At the code window goto `tools`, `references...` and check the `Microsoft Excel 14.0 Object Library` This will give you objects like `Excel.Application` and `Workbook` and `Worksheet` objcets to interact with the Excel Files — Evan, Jul 16 '15 at 17:25
Change your declerations to `Dim appExcel As Excel.Application` `Dim wbkExcel As Excel.Workbok` `Dim wstExcel As Excel.Worksheet` and you should be good — Evan, Jul 16 '15 at 17:27
Thanks Evan. That Helped me a lot. Would you please tell me what else I need to add to the formats to get a chart. — Robert, Jul 22 '15 at 13:52
Here is what I had in mind - but it's not working properly... .ActiveWorkbook.Sheets("FA_Month").Charts.Add.ChartType = xlCylinderColStacked .ActiveWorkbook.Sheets("FA_Month").ActiveChart.SetSourceData Source:=Range("FA_Month!$A$1:$D$15") — Robert, Jul 22 '15 at 13:55
Ask a new question and link it here, I will look at it but comment formatting is not easy to read for multiple lines of code — Evan, Jul 22 '15 at 14:04
Hi Evan, Regarding this question - everything was exported successfully but all my formats are gone - could you please tell me how to fix that. Thanks. — Robert, Jul 27 '15 at 15:11
Robert, That is a different question than the one we are commenting on. As I mentioned in a previous comment You will have better success asking a new question. If you link it here I will have a look at it. — Evan, Jul 27 '15 at 15:28

HarveyFrench · Answer 1 · 2015-07-30T08:26:23.977

0

Do you know about recording macros in excel as this will generate VBA code for you. There's lots of info on the web about this. Really the code the "recorder" creates should be edited quite significantly, again there is help on the web.

Also, as a pointer to learning more about excel and VBA you can look at the ExcelIsFun you tube channel see here

Note in the above code I would recommend With appExcel.ActiveWorkbook.Sheets("Detail_Report") .Cells.Font.Name = "Times New Roman" etc ..... end with

With appExcel.ActiveWorkbook.Sheets("FA_Month")
     .Tab.Color = 92
     .Rows("1:1").RowHeight = 40
     etc ....
End with

and you don't need to use "appExcel.ActiveWorkbook." in front of Sheets

You might also consider using nested With statements:

With appExcel.ActiveWorkbook.Sheets("Detail_Report")
     .Cells.Font.Name = "Times New Roman"
     .Tab.Color = 92

    With .Rows("1:1")
         .RowHeight = 40
         etc...
    End With

end with

With appExcel.ActiveWorkbook.Sheets("FA_Month")
     .Tab.Color = 92

    With .Rows("1:1")
         .RowHeight = 40
         etc...
    End With

    With .cells
         .font.size = 24
         etc...
    End With

End with

Harvey

edited Jul 30 '15 at 08:26

answered Jul 27 '15 at 20:52

HarveyFrench

4,440
4
20
36

Thanks Harvey for your input. Just wanted to clarify your last comment. Since I have multiple sheets do I have to... With appExcel.ActiveWorkbook.Sheets("FA_Month") ... End with – Robert Jul 29 '15 at 20:57
Thanks Harvey for your input. Just wanted to clarify your last comment. Since I have multiple sheets do I have to... With appExcel.ActiveWorkbook.Sheets("FA_Month") ... End with With appExcel.ActiveWorkbook.Sheets("FA_Quarter") ... End with etc. for each month OR write the End with at the end of all 5 sheets? I know this question is kinda silly and yes you are right that there are many posts on it online but all of them considered same format and hence they loop - this is where i go nuts - cause i dont wana loop. I want to treat each sheet differently. – Robert Jul 29 '15 at 21:02
Your first statement is right. "With" is used to tell VBA how to treat any code that starts with a "." eg .Tab.Color The "." is "converted" by VBA to be the text after the most recent previous with statement. ie appExcel.ActiveWorkbook.Sheets("FA_Month").Tab.Color – HarveyFrench Jul 30 '15 at 08:22

Export and format multiple sheets from Access to Excel

1 Answers1

Linked