女士访问Excel Late Binging VBA [英] Ms-Access to Excel Late Binging VBA

问题描述

我当前正在使用从MS Access 2016到MS Excel的后期绑定: 条件格式代码不执行所需的工作，但运行时不会抱怨错误.

I am currently using late binding from MS Access 2016 to MS Excel: The conditional formatting code does not perform the required job, but does not complain of an error when run.

我能够创建Excel工作表并编辑工作表，但目前无法在Excel工作表中创建条件格式.我试图在下面定义变量，但感觉好像缺少了一些东西.

I am able to create an Excel sheet and edit the sheet, but currently am unable to create conditional formatting in the excel sheet. I have attempted to define the variables below, but feel as though I am missing something.

Option Explicit

Sub SendEmailXLS()
    Dim appExcel As Object
    Dim objActiveWkb As Object
    Dim rng As Object
    Const xlConditionValueLowestValue As Long = 1
    Const xlConditionValuePercentile As Long = 5
    Const xlConditionValueHighestValue As Long = 2

    DoCmd.OpenReport "REPORT_XLS", acViewReport, WhereCondition:="EmailAddress='" & Me.User_Login & "'"
    DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="REPORT_XLS", OutputFormat:=acFormatXLS, Outputfile:="\\XXX\REPORT_XLS.xls"

    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = False
    appExcel.Application.Workbooks.Open ("\\XXX\REPORT_XLS.xls")

    Set objActiveWkb = appExcel.Application.ActiveWorkbook

    With objActiveWkb

        .Worksheets(1).Cells.Select
        .Worksheets(1).Columns("A:AI").Font.Size = 8
        .Worksheets(1).Rows(1).Font.Bold = True
        .Worksheets(1).Columns("A:AH").HorizontalAlignment = -4108
        .Worksheets(1).Columns("B").ColumnWidth = 8
        .Worksheets(1).Columns("AJ").Interior.Color = RGB(0, 0, 0)
        .Worksheets(1).Columns("A").ColumnWidth = 0.1
        .Worksheets(1).Columns("A").Interior.Color = RGB(0, 0, 0)
        .Worksheets(1).Columns("K:L").NumberFormat = "$#,##0"
        .Worksheets(1).Columns("N:AF").NumberFormat = "$#,##0"
        .Worksheets(1).Columns("AG:AH").NumberFormat = "0.0%"
        .Worksheets(1).Rows(1).EntireRow.Insert
        .Worksheets(1).Range("B2:AI2").Interior.Color = RGB(50, 100, 20)
        .Worksheets(1).Range("O1:Q1").Interior.Color = RGB(50, 100, 20)
        .Worksheets(1).Columns("A").Borders.Weight = 2
        .Worksheets(1).Columns("O:Q").Borders.Weight = 2
        .Worksheets(1).Columns("U:AC").Borders.Weight = 2
        .Worksheets(1).Columns("AJ").Borders.Weight = 2
        .Worksheets(1).Range("U1:AC1").Interior.Color = RGB(50, 100, 20)

        Set rng = .Worksheets(1).Columns("AD:AD")

        rng.FormatConditions.AddColorScale ColorScaleType:=3
        rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
        rng.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
        With rng.FormatConditions(1).ColorScaleCriteria(1).FormatColor
            .Color = 7039480
            .TintAndShade = 0
        End With
        rng.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
        rng.FormatConditions(1).ColorScaleCriteria(2).Value = 50
        With rng.FormatConditions(1).ColorScaleCriteria(2).FormatColor
            .Color = 8711167
            .TintAndShade = 0
        End With
        rng.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
        With rng.FormatConditions(1).ColorScaleCriteria(3).FormatColor
            .Color = 8109667
            .TintAndShade = 0
        End With        

    End With

    objActiveWkb.Close savechanges:=True
    appExcel.Application.Quit
    Set objActiveWkb = Nothing: Set appExcel = Nothing
End Sub

使用AppExcel.Selection时不会发生错误，但也不会执行作业.

Error does not occur when using AppExcel.Selection but job is not performed either.

.Worksheets(1).Range("AD:AD").Select
appExcel.Selection.FormatConditions.AddColorScale ColorScaleType:=3
appExcel.Selection.FormatConditions(appExcel.Selection.FormatConditions.Count).SetFirstPriority
    appExcel.Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
    1
With appExcel.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
    .Color = 7039480
    .TintAndShade = 0
End With
appExcel.Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
    5
appExcel.Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With appExcel.Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
    .Color = 8711167
    .TintAndShade = 0
End With
appExcel.Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
    2
With appExcel.Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
    .Color = 8109667
    .TintAndShade = 0
 End With

推荐答案

您的代码不知道(例如)xlConditionValueLowestValue代表什么-它是内置的Excel常量，但是对您的代码而言(没有VB项目参考)到Excel)，它看起来像一个未声明的变量.如果您在每个模块的顶部使用Option Explicit，则编译器会对此有所帮助.

Your code doesn't know what (eg) xlConditionValueLowestValue represents - it's a built-in Excel constant, but to your code (without a VB project reference to Excel) it just looks like an undeclared variable. The compiler would have helpfully complained about this, if you used Option Explicit at the top of every module.

如果您使用的是早期绑定，则编译器将查看Excel对象库以尝试解析这些值中的任何一个.

If you were using early binding, the compiler would look at the Excel object library to try to resolve any of these values.

因此，在使用后期绑定时，通常需要通过在自己的代码中创建匹配的常量来告诉您有关这些Excel常量的代码.另外，您也可以替换从Excel中的VB编辑器找到的数值，也可以通过Google替换.

So, when using late binding you need to tell your code about these Excel constants, typically by creating matching constants in your own code. Alternatively you can substitute the numeric values, which you can find from the VB editor in Excel, and likely also via Google.

编辑:尝试进行此更改

替换此:

.Worksheets(1).Columns("AD:AD").Select

与

Set rng = .Worksheets(1).Columns("AD:AD")

然后将Selection的以下所有实例替换为rng

then replace all following instances of Selection with rng

这篇关于女士访问Excel Late Binging VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！