Run-time error 1004

[josros60]

Hi,

I have the code below in outlook 2016 to open an excel file it has been working fine, the last two days is giving me the run-time error 1004 "Activate method of Range class failed" and highlight the following line of code:

  xExcelRange.Activate

here the complete code:

Public Sub OpenVendorInvoiceTracking()
  Dim xExcelFile As String
    Dim xExcelApp As Excel.Application
    Dim xWb As Excel.Workbook
    Dim xWs As Excel.Worksheet
    Dim xExcelRange As Excel.Range
    xExcelFile = "\\mtlnas01\share\Accountant Files\AP Team\Jose\NCL_XLSM\2020 Accounts Payable Checklist - NCL.xlsm"
    Set xExcelApp = CreateObject("Excel.Application")
    Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
    Set xWs = xWb.Sheets(1)
    xWs.Activate
    Set xExcelRange = xWs.Range("A1")
    xExcelRange.Activate
    xExcelApp.Visible = True
    xWb.Windows(1).WindowState = xlMaximized

Thank you,

 

[Joe4]

Is cell A1 hidden or protected?

 

[josros60]

No.

neither.

 

[Fluff]

Check that you don't have any hidden or very hidden sheets.

 

[josros60]

I checked, is no protected or very hidden anything.

 

[Joe4]

Are you doing in Excel VBA, or some other application's VBA?
I ask because I noticed your variable declarations are a bit curious:

   Dim xExcelApp As Excel.Application
    Dim xWb As Excel.Workbook
    Dim xWs As Excel.Worksheet
    Dim xExcelRange As Excel.Range

It usually isn't necessary to preface Worksbook, Worksheet, and Range with "Excel." if you are working from Excel VBA.

 

[josros60]

Yes,

should I remove those lines and see?

 

[Joe4]

Trying placing this line just above the line causing the error, and let us know what it returns when you run the code again:

MsgBox xWb.Sheets(1).Name

The value that it returns, is that sheet visible?
Is there anything special about cell A1 on that sheet (merged, protected, etc)?

 

[josros60]

Trying placing this line just above the line causing the error, and let us know what it returns when you run the code again:

MsgBox xWb.Sheets(1).Name

The value that it returns, is that sheet visible?
Is there anything special about cell A1 on that sheet (merged, protected, etc)?

Hi,

I did

it returned

Jul

which one of the sheets that is hidden been like that alway never had an issue with it.

thanks.

 

[Joe4]

I did

it returned

Jul

which one of the sheets that is hidden been like that alway never had an issue with it.

Well there is your problem right there. It is trying to activate a hidden sheet, which it cannot do.
You need to be careful about using references like "Sheets(1)".

If you want to select the first visible worksheet, you can do something like this:

How does VBA determine the 1st visible ws?

Several macros determine which worksheets are visible or hidden. I would like for the macro to end with the first sheet selected (active, visible on screen, not sure of terminology). First means the leftmost tab (of visible sheets) in the order I have them which is not alphabetical and not in...

www.mrexcel.com