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,

 

[josros60]

Thanks.

I checked the link I am not that good in VBA don't how to modify it that i don't get that error, any help please?

 

[josros60]

Hi

I checked the link but I am not that good in vba and don't know how to use that sample to modify the my code, any suggestion please how to, much appreciated.

thanks.

 

[Joe4]

Then we need you to explain to us how we can determine which sheet the codes needs to activate.
How many sheets are there?
How many sheets are hidden/unhidden?
How can we identify which sheet the code should run against?

 

[josros60]

Hi

There are 14 sheets all together (Jan-Dec, Summary) that are hidden plus one more sheet call Contents (Like Table of Index) which should be one to activate because from that one I select which sheet to open.

Thank you.

 

[Joe4]

Does the sheet you need to run this against always have the same name?
If so, just change this line:

Set xWs = xWb.Sheets(1)

to this:

Set xWs = xWb.Sheets("Contents (Like Table of Index)")

putting whatever the sheet name is in between the doublt-quotes.

 

[josros60]

Thank you so much, it worked perfectly.

 

[Joe4]

You are welcome.