CODEWITHSUNDEEP
excelvbarange
user3613962
user3613962

Reputation: 1

Error code 1004 on

I'm trying to export a query from Access 2010 To Excel 2010 then have conditional formatting inserted. This code exports the information fine, but throws an error 1004 "Range of object_Global Failed". The code was build using an excel macro then copying and pasting into my Access module. Any assistance with this would be greatly appreciated.

Sub Macro2()
Dim objXls As Excel.Application
    Dim MyBook As Excel.Workbook
    Dim MySheet As Excel.Worksheet
    Dim MyFile As String


    DoCmd.TransferSpreadsheet acExport, 8, "VASL/OCA Report", _
    "G:\shared documents\FSFN OCA Adoption Reconciliations\FY14\VASL-OCA Reconciliation", True

    Set objXls = CreateObject("Excel.Application")
    MyFile = "G:\shared documents\FSFN OCA Adoption Reconciliations\FY14\VASL-OCA Reconciliation.xls"
    objXls.Workbooks.Open ("" & MyFile)
    objXls.Visible = True

    Set MyBook = objXls.Workbooks("VASL-OCA Reconciliation.xls")
    Set MySheet = MyBook.Worksheets("VASL_OCA_Report")

    MySheet.Activate

    Range("G2").Select

    'deleted "scrollRow" lines

    Range("G2:G808").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=H2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Range("H808").Select

    'deleted "scrollRow" lines

    Range("H2:H808").Select
    Range("H808").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=G2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True



    End Sub

Upvotes: 0

Views: 325

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Untested:

Sub Macro2()

    Const FLDR As String = "G:\shared documents\FSFN OCA Adoption Reconciliations\FY14\"
    Dim objXls As Excel.Application
    Dim MyBook As Excel.Workbook
    Dim MySheet As Excel.Worksheet

    DoCmd.TransferSpreadsheet acExport, 8, "VASL/OCA Report", _
                              FLDR & "VASL-OCA Reconciliation", True

    Set objXls = CreateObject("Excel.Application")
    objXls.Visible = True

    Set MyBook = objXls.Workbooks.Open(FLDR & "VASL-OCA Reconciliation.xls")
    Set MySheet = MyBook.Worksheets("VASL_OCA_Report") '<<EDIT

    MySheet.Activate

    With MySheet.Range("G2:G808")
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
                              Formula1:="=H2"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = True
    End With

    With MySheet.Range("H2:H808")
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
                            Formula1:="=G2"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = True
    End With

End Sub

Upvotes: 1

Related Questions