Error is: Compile error: Method or data member not found after EXCEL upgrade to v. 2019

%3CLINGO-SUB%20id%3D%22lingo-sub-1142381%22%20slang%3D%22en-US%22%3EError%20is%3A%20Compile%20error%3A%20Method%20or%20data%20member%20not%20found%20after%20EXCEL%20upgrade%20to%20v.%202019%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142381%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20members%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20had%20working%20VBA%20code%20copying%20some%20data%20from%20Outlook%20mail%20to%20Excel%20file.%20It%20worked%20without%20problems%20until%20Excel%20version%20was%20upgraded%20to%20Excel%20365%2F2019.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EError%20is%3A%20Compile%20error%3A%20Method%20or%20data%20member%20not%20found%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAny%20idea%20what%20could%20be%20causing%20the%20error%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%20of%20the%20code%2C%20compiler%20stops%20at%20part%20of%20the%20code%20in%20%3CFONT%20color%3D%22%23FF0000%22%3ERED%3C%2FFONT%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20CopyData()%3CBR%20%2F%3EDim%20excelWorkbookPath%20As%20String%3CBR%20%2F%3EexcelWorkbookPath%20%3D%20%22C%3A%5CPath%5Cdata.xlsx%22%3CBR%20%2F%3EDim%20rpl%20As%20Outlook.MailItem%3CBR%20%2F%3EDim%20itm%20As%20Object%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20objApp%20As%20Outlook.Application%3CBR%20%2F%3ESet%20objApp%20%3D%20Application%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESelect%20Case%20TypeName(objApp.ActiveWindow)%3CBR%20%2F%3ECase%20%22Explorer%22%3CBR%20%2F%3ESet%20itm%20%3D%20objApp.ActiveExplorer.Selection.Item(1)%3CBR%20%2F%3ECase%20%22Inspector%22%3CBR%20%2F%3ESet%20itm%20%3D%20objApp.ActiveInspector.CurrentItem%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3ESet%20objApp%20%3D%20Nothing%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20InStr(1%2C%20itm.Subject%2C%20%22Some%20text%20%22%2C%20vbTextCompare)%20%26gt%3B%200%20Then%3CBR%20%2F%3EMsgBox%20(%22Some%20text%20.%22)%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20FileExists(excelWorkbookPath)%20Then%3CBR%20%2F%3EMsgBox%20(%22Some%20text%20%3A%20%22%20%26amp%3B%20excelWorkbookPath)%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20IsFileOpen(excelWorkbookPath)%20Then%3CBR%20%2F%3EMsgBox%20(%22Some%20text%20%22)%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20excelApp%20As%20Excel.Application%3CBR%20%2F%3ESet%20excelApp%20%3D%20CreateObject(%22Excel.Application%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20excelWorkbook%20As%20Excel.Workbook%3CBR%20%2F%3ESet%20excelWorkbook%20%3D%20excelApp.Workbooks.Open(excelWorkbookPath)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20ws%20As%20Worksheet%3CBR%20%2F%3ESet%20ws%20%3D%20excelWorkbook.Sheets(%22Data%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20newRowIndex%20As%20Integer%3CBR%20%2F%3EnewRowIndex%20%3D%20ws.Range(%22A%22%20%26amp%3B%20ws.%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3ERows.%3C%2FSTRONG%3E%3C%2FFONT%3ECount).End(xlUp).Row%20%2B%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECONTINUED....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1142381%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142425%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20is%3A%20Compile%20error%3A%20Method%20or%20data%20member%20not%20found%20after%20EXCEL%20upgrade%20to%20v.%202019%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142425%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321150%22%20target%3D%22_blank%22%3E%40Kapucino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20and%20see%20if%20that%20works%20for%20you...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EnewRowIndex%20%3D%20ws.Range(%22A%22%20%26amp%3B%20ws.Rows.Count).End(3)(2).Row%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

Dear members,

 

We had working VBA code copying some data from Outlook mail to Excel file. It worked without problems until Excel version was upgraded to Excel 365/2019.

 

Error is: Compile error: Method or data member not found 

 

Any idea what could be causing the error?

 

 

Part of the code, compiler stops at part of the code in RED.

 

Sub CopyData()
Dim excelWorkbookPath As String
excelWorkbookPath = "C:\Path\data.xlsx"
Dim rpl As Outlook.MailItem
Dim itm As Object

Dim objApp As Outlook.Application
Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set itm = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set itm = objApp.ActiveInspector.CurrentItem
End Select
Set objApp = Nothing

If Not InStr(1, itm.Subject, "Some text ", vbTextCompare) > 0 Then
MsgBox ("Some text .")
Exit Sub
End If

If Not FileExists(excelWorkbookPath) Then
MsgBox ("Some text : " & excelWorkbookPath)
Exit Sub
End If

If IsFileOpen(excelWorkbookPath) Then
MsgBox ("Some text ")
Exit Sub
End If

Dim excelApp As Excel.Application
Set excelApp = CreateObject("Excel.Application")

Dim excelWorkbook As Excel.Workbook
Set excelWorkbook = excelApp.Workbooks.Open(excelWorkbookPath)

Dim ws As Worksheet
Set ws = excelWorkbook.Sheets("Data")

Dim newRowIndex As Integer
newRowIndex = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1

 

CONTINUED....

1 Reply
Highlighted

@Kapucino 

Try this and see if that works for you...

 

newRowIndex = ws.Range("A" & ws.Rows.Count).End(3)(2).Row