Calling a personal macro using VB script failed with - Method 'Cells' of Object'_Global' failed VBA

Question

I am getting error while running the below vb script to run the personal macro stored on an macro enabled workbook. `"Method 'Sheets' of object '_Global' failed"`

    'Open an instance of Excel
    Set objExcel=CreateObject("Excel.Application")
    objExcel.Visible=True

Set objWorkbook=objExcel.Workbooks.Open("C:\Microsoft\Excel\XLSTART\PERSONAL.XLSB") 'Call macro objExcel.Run "PERSONAL.XLSB!PLAN"

' Macro which is being called by the above script

     Option Explicit
    Public NewCols As Long          ' EGPO

    Dim i As Long
    Dim colx As Long

    Sub PLAN()
        Application.ScreenUpdating = False

        Call PLAN
         Application.ScreenUpdating = True
    End Sub

    Private Sub LatestCRDTwo()
    On Error Resume Next
    Dim wb As Workbook
    Dim P As String, Q As String
        P = "Pass\Fail"
        Q = "Test Cases"
    Application.ScreenUpdating = False

' Any workbook which is macro enabled will be used
Set wb = ActiveWorkbook

    With Sheets("EGPO")
        Sheets("EGPO").Select
        NewCols = .UsedRange.Columns.Count
        .AutoFilterMode = False
' Add columns starting at column 4 and insert columns at every 2nd column
        For colx = 4 To 40 Step 2 
            NewCols = .UsedRange.Columns.Count
            Columns(colx).Insert Shift:=xlToRight
            NewCols = NewCols + 1
        Next colx
    End With
    On Error Resume Next
'Below "With statements" is to copy the values in the inserted columns
    With Sheets("EGPO")
        Sheets("EGPO").Select
        NewCols = .UsedRange.Columns.Count

' Loop to paste the values For i = 4 To NewCols Step 2 If wb.Worksheets("EGPO").Cells(1, 1).Value = "" Then wb.Worksheets("EGPO").Cells(2, i) = P wb.Worksheets("EGPO").Cells(2, i).Interior.ColorIndex = 15 wb.Worksheets("EGPO").Cells(1, i) = Q End If NewCols = NewCols + 1 Next i

    End With
    On Error Resume Next
    Application.ScreenUpdating = True
    End Sub

The error is likely in the macro that you are invoking. Can you post its contents here? — Isaac Moore
@ Isaac - I have added the macro code below the vb script in my post, Thanks — USA

Unknown Unknown · Accepted Answer · 2016-06-18T07:14:10

I'm think that this is just a template

C:\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Use Workbooks("Personal.xlsb").FullName to find the actual path

C:\Users\best buy\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Apparently PERSONAL.XLSB!PLAN is running, because that is what is throwing the error. I think the problem is there is no active workbook. Trying to activate or select a worksheet on a hidden worksheet could throw this error.

Calling a personal macro using VB script failed with - Method 'Cells' of Object'_Global' failed VBA

1 Answers