导出访问查询到Excel格式 [英] Export Access Query to Excel with Formatting

问题描述

所以，我有，我想送回到EXCEL在访问查询。在使用导出向导是很好，很正常，我想更多的自动化添加到导出过程。到目前为止，我的工作code所以出口在最后excel表会产生一定的格式。至于基本的格式，我很好，我发现有很多的资源来帮助我。

So I have a query in access that I want to send back to excel. While using the export wizard is fine and dandy I want to add more automation to the exporting process. So far I am working on code so during the export the final excel sheet will have some formatting. As far as basic formatting I am fine, I found many resources to help me with this.

我的问题是，我要设置条件格式，这样，如果一个特定的列（G）的值，则整个行被高亮显示。我有点失去了如何通过VBA code设置条件格式为Excel中访问

My problem is that I want to set up conditional formatting so that if a specific column(G) has a value, then the whole row is highlighted. I am a bit lost on how to set up conditional formatting for Excel through vba code in Access

下面是我

Dim appExcel As Variant
 Dim MyStr As String
 Dim rng As Excel.Range

' Creates Excel object and Adds a Workbook to it
    Set appExcel = CreateObject("Excel.application")
    appExcel.Visible = False
    appExcel.Workbooks.Add


    Set wksNew = appExcel.Worksheets("Sheet1")

    appExcel.Visible = True

' The first thing I do to the worksheet is to set the font.
' Not all are required, but I included them as examples.
 With appExcel
    .Cells.Font.Name = "Calbri"
    .Cells.Font.Size = 11
    .Cells.NumberFormat = "@"                                   'all set to Text Fields
    ' My first row will contain column names, so I want to freeze it
    .Rows("2:2").Select
    .ActiveWindow.FreezePanes = True

    ' ... and I want the header row to be bold
    .Rows("1:1").Font.Bold = True
    .Rows("1:1").Font.ColorIndex = 1
    .Rows("1:1").Interior.ColorIndex = 15

    ' Adds conditional formatting based on Values in the G column

    rng = .Range("A2:J20").Select
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT($G2 = 0)"
    rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With appExcel.Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

End With

目前的code执行，直到我的条件格式块，然后它告诉我，对象变量或带块未设置。

Currently the code executes until my conditional formatting block and then it tells me that the Object Variable or With block is not set.

推荐答案

我查了下code运行，直到结束：

I checked that the following code runs until the end:

Dim appExcel As Variant
 Dim MyStr As String
 Dim rng As Excel.Range
 Dim wksNew

' Creates Excel object and Adds a Workbook to it
    Set appExcel = CreateObject("Excel.application")
    appExcel.Visible = False
    appExcel.Workbooks.Add


'   Set wksNew = appExcel.Worksheets("Sheet1")
    Set wksNew = appExcel.Worksheets(1)

    appExcel.Visible = True

' The first thing I do to the worksheet is to set the font.
' Not all are required, but I included them as examples.
 With appExcel
    .Cells.Font.Name = "Calbri"
    .Cells.Font.Size = 11
    .Cells.NumberFormat = "@"                                   'all set to Text Fields
    ' My first row will contain column names, so I want to freeze it
    .Rows("2:2").Select
    .ActiveWindow.FreezePanes = True

    ' ... and I want the header row to be bold
    .Rows("1:1").Font.Bold = True
    .Rows("1:1").Font.ColorIndex = 1
    .Rows("1:1").Interior.ColorIndex = 15

    ' Adds conditional formatting based on Values in the G column

    Set rng = .Range("A2:J20")
    rng.Select
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT($G2 = 0)"
    rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
    With rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

End With

祝你好运。

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