Compile Error

[josros60]

HI,

I have the code below and it was working fine (Open excel file from outlook) but suddenly getting this error:

Compile Error:
Method or data member not found (on the line below)

Set xWb = xExcelApp.Workbooks.Open(xExcelFile)

Complete code:

Public Sub OpenVendorChartofAcounts() 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\CHART OF ACCOUNTS.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
    
End Sub

 

[ZVI]

Hi,
Not sure about the reason of the issue, check the absence of the 'MISSING:...' items in VBE-Tools-References
You may also try this - Set xExcelApp = New Excel.Application instead of the code line with CreateObject

 

[josros60]

i tried your suggestion still same error.

it's weird because the other files open from outlook opens ok same code just this one no sure nothing has been changed.

thank you

 

[RoryA]

Is there any code in the Workbook_Open event of that workbook?

 

[ZVI]

... the other files open from outlook opens ok same code just this one

If code is working with other workbooks then my suggestion in post 2 can't help.
However, one of the problem can be in a fact that XLSMs is blocked.
How to unblock XLSM read in this page of JKP site.

 

[josros60]

no there's no Workbook_Open event in this file

I also checked the site still same prolbem

thank you for all suggestions.