Sub Final(olMail As MailItem)
Dim strPfad As String
Dim Datei As Attachments
strPfad = "C:\Daten\Dokumente\DIENST\__Lufthansa\Lagerbestand\Kormann Report\"
On Error Resume Next
Set Datei = olMail.Attachments
For i = 1 To Datei.Count
Datei.Item(i).SaveAsFile strPfad & Datei.Item(i).FileName
Next i
Dim strDatei As String
Dim strDat As String
Dim strP84_Nummer As String
Dim ro As Long
Dim co As Long
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
strDat = Format(Now(), "yyyy-mm-dd")
strDatei = "Report LUFTHANSA " & strDat & ".xlsb"
strP84_Nummer = "CUZ:K391V200-LHNI9"
With xlApp
.Visible = True
.Workbooks.Open strPfad & strDatei
Set xlBook = xlApp.Workbooks(strDatei)
Set xlSheet = xlBook.Sheets("Overview")
With xlBook
Columns("C:C").Select
Selection.Find(What:=strP84_Nummer, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ro = ActiveCell.Row
End With
With xlBook.xlSheet
Rows("6:6").Select
Selection.Find(What:=" total", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
co = ActiveCell.Column
End With
With xlBook
If Cells(ro, co).Value = 0 Then
MsgBox "Anzahl der gesuchten P84 Produktnummer ist Null!"
End If
End With
End With
End Sub