Ms-Access to Excel Late Binging VBA

问题:

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.

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

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

回答1:

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.

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

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.

EDIT: try this change

Replace this:

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

with

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

then replace all following instances of Selection with rng

回答2:

Here is a handy dandy list of excel constants you can create a global definition module with. Check whats here versus what youre using.

https://www.dropbox.com/sh/mcxx9byernn67qh/AABmCwBn11_36VqqlsXGtq2Wa?dl=0