Runtime error 91 in MS ACCESS

Question

I'm opening an Excel app within a sub that creates pivot table and a chart, and the chart will be exported as PNG file. This sub is called 3 times with 3 different excel files to be opened and each exports one PNG image. For the first call, the sub doesn't produce error. Runtime error 462 occurs when the 2nd call of the sub is executed.

I'checked the process of the excel application in Task Manager and the task wasn't killed. I've researched how to kill the process without using shell but failed.

Even if I kill the process before running the 2nd call of the sub, the error still there.

Here is my code

Function Test()
    EXLCRTGRAPH "GRAPH_D"
    EXLCRTGRAPH "GRAPH_W"
    EXLCRTGRAPH "GRAPH_M"
End Function

Sub EXLCRTGRAPH(fName As String) 'imgOut As String, tmplPath As String, fName As String)  ' + image name, template filepath and nmae, chart height and width. fName

Dim imgOut As String: imgOut = "C:\TESAMPM\MonDisk\test"
Dim tmplPath As String: tmplPath = "C:\Users\tsbatch\AppData\Roaming\Microsoft\Templates\Charts"

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(CurrentProject.path & "\test\" & fName & ".xlsx")
    Set xlSheet = xlBook.Worksheets(fName)

    Dim rangeRow As String
    Dim source As String
    Dim pvC As PivotCache
    Dim pvT As PivotTable

    xlApp.Visible = False
    xlApp.DisplayAlerts = False
    xlSheet.Activate

    xlSheet.Select
    xlSheet.Range("A1").Select
    xlSheet.Range("C1").End(xlDown).Select
    rangeRow = xlSheet.Range(Selection.Address(xlR1C1)).Row  'the error occurs here

    source = fName & "!R1C1:R" & rangeRow & "C3"

    Set pvC = xlBook.PivotCaches.Create(xlDatabase, source)
    Set pvT = pvC.CreatePivotTable(fName & "!R1C7", "pivot", , xlPivotTableVersion12)

    xlSheet.Select
    xlSheet.Cells(1, 7).Select
    With xlSheet.PivotTables("pivot").PivotFields("“ú•t")
        .Orientation = xlRowField
        .Position = 1
    End With
    With xlSheet.PivotTables("pivot").PivotFields("ƒT[ƒo[ƒhƒ‰ƒCƒu")
        .Orientation = xlRowField
        .Position = 2

    End With
    xlSheet.PivotTables("pivot").AddDataField xlSheet.PivotTables("pivot").PivotFields("‹ó‚«—e—Ê(%)"), "‡Œv / ‹ó‚«—e—Ê(%)", xlSum
    With xlSheet.PivotTables("pivot").PivotFields("ƒT[ƒo[ƒhƒ‰ƒCƒu")
        .Orientation = xlColumnField
        .Position = 1
    End With
    xlSheet.Shapes.AddChart.Select
    xlSheet.ChartObjects(1).Activate
    xlBook.ActiveChart.ApplyChartTemplate (tmplPath & "\" & fName & ".crtx")

    On Error Resume Next
    xlSheet.Shapes("Chart 1").Height = 425.1968503937
    xlSheet.Shapes("Chart 1").Width = 850.3937007874
    xlSheet.Shapes("ƒOƒ‰ƒt 1").Height = 425.1968503937
    xlSheet.Shapes("ƒOƒ‰ƒt 1").Width = 850.3937007874
    On Error GoTo 0

    xlSheet.ChartObjects(1).Chart.Export _
        FileName:=imgOut & "\" & fName & ".png", FilterName:="PNG"
    WAITPROC "0.5"

    rangeRow = ""
    xlApp.DisplayAlerts = True
    xlApp.Visible = True
    xlBook.Close (False)
    xlApp.Quit

    Set pvC = Nothing
    Set pvT = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

End Sub

Please ignore the symbols like "ƒT[ƒo[ƒhƒ‰ƒCƒu"

the error occurs in this line of code

rangeRow = xlSheet.Range(Selection.Address(xlR1C1)).Row

I can't find the cause to solve the problem, please help.

Gustav Gustav · Accepted Answer · 2018-01-25T09:46:46

You state:

rangeRow = xlSheet.Range(Selection.Address(xlR1C1)).Row

but rangeRow is declared as a String, while Row is a Long.

Runtime error 91 in MS ACCESS

1 Answers