Referencing ThisWorkbook produces Run Time Error 429

Question

The below code throws

run time error 429 activex component can't create object

on With ThisWorkbook.

    Dim myFirstRow As Long 
    Dim myLastRow As Long 
    Dim myFirstColumn As Long 
    Dim myLastColumn As Long 
    Dim mySourceData As String 
    Dim myDestinationRange As String 
    Dim mySourceWorksheet As Worksheet 
    Dim myDestinationWorksheet As Worksheet 
    Dim myPivotTable As PivotTable

    With ThisWorkbook    ' ** 
       Set mySourceWorksheet = .Worksheets("WAM_RAW_DATA")
       Set myDestinationWorksheet = .Worksheets.Add 
    End With 

    myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)
    myFirstRow = 1
    myLastRow = 2020
    myFirstColumn = 1
    myLastColumn = 40

    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1) 
    End With 

    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNewSheet")

    With myPivotTable 
        .PivotFields("Agent Name").Orientation = xlRowField 
        .Position = 1 
    End With

    With myPivotTable 
        .PivotFields("Login Date").Orientation = xlColumnField 
        .Position = 1 
    End With

    With myPivotTable 
        .PivotFields("Login Time").Orientation = xlDataField 
        .Position = 1 
        .Function = xlMin 
        .NumberFormat = "h:mm:ss" 
    End With
End Sub

Please tell in which line you get the error. It's impossible that it occurs here `With ThisWorkbook` — Pᴇʜ, Nov 23 '18 at 12:48
its in the 10th line when i run a macro it got stuck and This Workbook line highlighted in yellow — Sachin, Nov 23 '18 at 13:38

score 0 · Answer 1 · answered Nov 24 '18 at 09:03

0

This might help How do I resolve "Run-time error '429': ActiveX component can't create object"?

However, meanwhile you can try some other solutions to use that statement, like:

Dim WB As Workbook

Set WB = ActiveWorkbook
    ! OR !
Set WB = Workbooks("name of workbook.xlsm")

answered Nov 24 '18 at 09:03

FAB

2,505
1
10
21

1

Second one... Thank You – Sachin Dec 11 '18 at 09:11

Referencing ThisWorkbook produces Run Time Error 429

1 Answers1