从Access导出到Excel并设置其格式 [英] Export and format multiple sheets from Access to Excel

查看：146
本文介绍了从Access导出到Excel并设置其格式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试从Access导出到Excel的多个摘要报告.源数据在Access中.最终用户通过单击按钮创建这些报告的表单也位于Access中.我面临2个困难，想知道您能否提供帮助:

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:

  1. 我成功地导出了报告，但由于某些原因我的格式未实现-请您告诉我原因并提出如何解决此问题的想法.

  1. 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.

***到目前为止，我所做的是Excel中的宏vba，并以某种方式弄清楚了如何在访问中进行翻译.为什么如此不同?为什么我不能使用相同的语言语法?我是编程新手，但从逻辑上讲，因为Microsoft Office创建了两种环境-语言是否应该相同?我的意思是我想假设为什么许多用户更喜欢购买他们的产品-具有讽刺意味的是-我的理论不支持我的假设....请帮助

*** 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

这是我的代码:

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:

cmdREPORT2_err:

   Exit Sub

End Sub

推荐答案

您是否知道在excel中录制宏，因为这将为您生成VBA代码.网络上有很多与此有关的信息.确实应该对记录器"创建的代码进行相当大的编辑，再次在网络上有所帮助.

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.

此外，作为进一步了解excel和VBA的指南，您可以查看您的电视频道ExcelIsFun，请参见

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

请注意上面的代码，我建议 带有appExcel.ActiveWorkbook.Sheets("Detail_Report") .Cells.Font.Name ="Times New Roman" 等等 ..... 以

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

，您无需使用"appExcel.ActiveWorkbook".在表格前

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

您还可以考虑使用嵌套的With语句:

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

哈维

这篇关于从Access导出到Excel并设置其格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆