vba 使用格式将 Access 查询导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19547118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export Access Query to Excel with Formatting
提问by Kevin Malloy
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.
所以我有一个访问查询，我想发送回 excel。虽然使用导出向导很好，但我想为导出过程添加更多自动化。到目前为止，我正在编写代码，因此在导出过程中，最终的 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
我的问题是我想设置条件格式，以便如果特定列（G）有一个值，那么整行都会突出显示。我对如何通过 Access 中的 vba 代码为 Excel 设置条件格式有点迷茫
Here is what I have
这是我所拥有的
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
Currently the code executes until my conditional formatting block and then it tells me that the Object Variable or With block is not set.
目前，代码一直执行到我的条件格式化块，然后它告诉我对象变量或 With 块没有设置。
采纳答案by jacouh
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
Good luck.
祝你好运。
