Hallo Spezialisten,
ich habe eine xls Datei die erste Zeile sind die Überschriften Kundennummer, Kundenname usw.... ab Zeile zwei kommen die Inhalte.
Ich möchte in der xls Datei die Kundennumer nehmen als key definieren in der Ansicht suchen, wenn die Kundennummer nicht exisitert möchte ich ein neues Dokument anlegen mit deren Feldinhalt
Wird der Kunde gefunden soll er nichts tun und die nächste Zeile analysieren. hier mein Quellcode
Die Stelle wo der Debugger eine Fehlermeldung bringt habe ich markiert.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim rtitem As NotesRichTextItem, SendRtitem As NotesRichTextItem
Dim itemReaders As NotesItem, itemReaders2 As NotesItem
Dim object As NotesEmbeddedObject
Dim row As Integer, written As Integer ,iWCount As Integer
Dim colsum As Currency, colsum2 As Currency, colval As Currency, colname As Currency
Dim Excel As Variant, xlWorkbook As Variant, xlSheet As Variant, vIntLeser(3) As Variant
Dim One As String, xlFilename As String
Dim cRechname As String, cPathname As String
Dim tmpKundennr As String
Dim vdoc As NotesDocument
Dim i As String
Dim filenum As Integer
Dim pathName As String
Dim cFilename As String
Dim PathFileName As String
pathName$ = "E:\TestKUR\LCU\*"
cFilename$ = Dir$(pathName$ & "*.xls", 0)
PathFileName = "E:\TestKUR\LCU\" & cFilename$
iWCount = 1
Set db = session.CurrentDatabase
Set Excel = CreateObject( "Excel.Application" )
' *** ---------------------- öffnet Excel File ---------------------------
Excel.Visible = False
Excel.Workbooks.Open PathFileName
Set xlWorkbook = Excel.ActiveWorkbook
'Set xlSheet = xlWorkbook.Worksheets(1)
Set xlSheet = xlWorkbook.ActiveSheet ' *** Nur ein Sheet - wird hier definiert
row = 2
With xlSheet
tmpKundennr = .Cells( row, 1 ).Value
Set view = db.GetView ("vwLogistik")
While tmpKundennr <> ""
'tmpKundennr = .Cells( row, 1 ).Value
Set vdoc = view.GetDocumentByKey(tmpKundennr)
If vdoc Is Nothing Then --> Fehlermeldung: Object Variable not set
vdoc.Form = "maLCU"
vdoc.txCustNo = .Cells( row, 2 ).Value
vdoc.txCustName = .Cells( row, 3 ).Value
vdoc.fdMandant = .Cells( row, 4 ).Value
vdoc.fdAnzahlMA = .Cells( row, 5 ).Value
vdoc.fdBranche = .Cells( row, 6 ).Value
vdoc.fdAPKunde = .Cells( row, 7 ).Value
vdoc.fdUmsatzAktiv = .Cells( row, 8 ).Value
vdoc.fdUmsatzPassive = .Cells( row, 9 ).Value
vdoc.fdUmsatzEMechanik = .Cells( row, 10 ).Value
vdoc.fdUmsatzTotal = .Cells( row, 11 ).Value
vdoc.fdMargeAktiv = .Cells( row, 12 ).Value
vdoc.fdMargePassive = .Cells( row, 13 ).Value
vdoc.fdMargeEMechanik = .Cells( row, 14 ).Value
vdoc.fdUmsatzAktiv_1 = .Cells( row, 15).Value
vdoc.fdUmsatzPassive_1 = .Cells( row, 16 ).Value
vdoc.fdUmsatzEMechanik_1 = .Cells( row, 17 ).Value
vdoc.fdUmsatzTotal_1 = .Cells( row, 18 ).Value
vdoc.fdMargeAktiv_1 = .Cells( row, 19 ).Value
vdoc.fdMargePassive_1 = .Cells( row, 20 ).Value
vdoc.fdMargeEMechanik_1 = .Cells( row, 21 ).Value
vdoc.fdUmsatzAktiv_2 = .Cells( row, 22 ).Value
vdoc.fdUmsatzPassive_2 = .Cells( row, 23 ).Value
vdoc.fdUmsatzEMechanik_2 = .Cells( row, 24 ).Value
vdoc.fdUmsatzTotal_2 = .Cells( row, 25 ).Value
vdoc.fdMargeAktiv_2 = .Cells( row, 26 ).Value
vdoc.fdMargePassive_2 = .Cells( row, 27 ).Value
vdoc.fdMargeEMechanik_2 = .Cells( row, 28 ).Value
Call vdoc.save(False,True)
row = row + 1
tmpKundennr = .Cells( row, 1 ).Value
Else
row = row + 1
tmpKundennr = .Cells( row, 1 ).Value
End If
Wend
End With
NextDoc:
' *** Disconnecting from Excel...
xlWorkbook.Close False
Excel.Quit
Set Excel = Nothing
End Sub
