Domino 9 und frühere Versionen > Administration & Userprobleme

Import von Excel-Datenfeldern via Script

(1/1)

Master Of Desaster:
...ich schon wieder... :-[

Hab ein LotusScript für meine Belange modifiziert - funzt soweit auch prima. Aber:
Die Variablen temp21 und temp22 werden mit Uhrzeiten gefüllt, die entsprechenden Felder in der Maske sind auch als Uhrzeit definiert. Warum kommen dann nur 0,irgendwas-Zahlen raus?
Beim Datumsfeld (temp5) funktioniert es doch auch?
Ist das Script falsch und/oder bin ich zu doof und/oder liegt der Fehler ganz woanders?

Ich poste das Script mal, vielleicht hat einer ja ne Idee:


--- Code: ---Sub Click(Source As Button)
'The action button is located on a view named Import1
   Dim ws As New NotesUIWorkspace
   Dim session As New NotesSession
   Dim db As NotesDatabase
   Set db = session.CurrentDatabase
   Dim view As NotesView
   Dim doc As NotesDocument
   Dim temp1 As String
   Dim temp2 As String
   Dim temp3 As String
   Dim temp4 As String
   Dim temp5 As Variant
   Dim temp6 As String  
   Dim temp7 As Long  
   Dim temp8 As String  
   Dim temp9 As String  
   Dim temp10 As String  
   Dim temp11 As String  
   Dim temp12 As String  
   Dim temp13 As String  
   Dim temp14 As String  
   Dim temp15 As String  
   Dim temp16 As String  
   Dim temp17 As Double  
   Dim temp18 As Double  
   Dim temp19 As Double  
   Dim temp20 As String  
   Dim temp21 As Variant  
   Dim temp22 As Variant  
   Dim temp23 As String
   Dim r As Long
   Dim xlApp As Variant
   Dim xlSheet As Variant
   Dim cursor As Long
   Dim recordcheck As String
   
   On Error Goto ERRORLABEL
   Dim FileName As String
   Dim DefaultFileName As String
   DefaultFileName="c:\temp\sheet.xls"
   
   NamePrompt$="Enter the complete Path and File Name of the Excel file to be imported:" &Chr(13)
   FileName=Inputbox(NamePrompt$,"Import File Name Specification",DefaultFileName,100,100)
   If FileName="" Then Goto SubEnd
 'Create an Excel object for the spreadsheet
 'It may be necessary to have the proper MS Office DLLs installed on the PC running this application
 'That is, the person must be able to open the XLS file with Excel
   Set xlApp = CreateObject("Excel.Application")
   xlApp.Application.Workbooks.Open Filename
'File not found will return ERR=213 and the routine will be ended
   With xlApp.workbooks.Add
 'Not sure what this line is for, unless it's to be sure that at least one workbook is present in the XLS file
   End With
'Stop
   Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
   recordcheck="x"
   r=1 ' Row counter - first row contains fieldnames from the database;  
'these fieldnames are not needed as long as the spreadsheet follows a specified format
   While recordcheck <>"0" And recordcheck <> "" And r<5000
  'Recordcheck = "0" when there is no value in the first cell of the row,  if integer, or "", if string
  'Important - Set r to a value that will stop the import routine without missing any data, if the other criteria do not
'For this example, six columns are imported
      cursor=0
      r=r+1
      temp1=xlSheet.Cells(r,1).value
      recordcheck=Cstr(temp1)
      If recordcheck="0" Then Goto Finished ' Avoid generating empty record
      temp2=xlSheet.Cells(r,2).value
      temp3=xlSheet.Cells(r,3).value
      temp4=xlSheet.Cells(r,4).value
      temp5=xlSheet.Cells(r,5).value
      temp6=xlSheet.Cells(r,6).value
      temp7=xlSheet.Cells(r,7).value
      temp8=xlSheet.Cells(r,8).value
      temp9=xlSheet.Cells(r,9).value
      temp10=xlSheet.Cells(r,10).value
      temp11=xlSheet.Cells(r,11).value
      temp12=xlSheet.Cells(r,12).value
      temp13=xlSheet.Cells(r,13).value
      temp14=xlSheet.Cells(r,14).value
      temp15=xlSheet.Cells(r,15).value
      temp16=xlSheet.Cells(r,16).value
      temp17=xlSheet.Cells(r,17).value
      temp18=xlSheet.Cells(r,18).value
      temp19=xlSheet.Cells(r,19).value
      temp20=xlSheet.Cells(r,20).value
      temp21=xlSheet.Cells(r,21).value
      temp22=xlSheet.Cells(r,22).value
      temp23=xlSheet.Cells(r,23).value
  'The above lines lines could be combined with those below, but keep separate for ease in debugging until ready to finalize the code
      Set doc = New NotesDocument(db)
      doc.Form = "Servicebericht"
      doc.SB_Nummer = temp1
      doc.SB_Geraetetyp = temp2
      doc.SB_Geraetenummer= temp3
      doc.SB_Techniker = temp4
      doc.SB_Datum = temp5
      doc.SB_Status = temp6
      doc.SB_Betriebsstunden = temp7
      doc.SB_AC = temp8
      doc.SB_KD_Nummer = temp9
      doc.SB_Kunde = temp10
      doc.SB_Ort = temp11
      doc.SB_Fehlercode1 = temp12
      doc.SB_Fehlercode2 = temp13
      doc.SB_Fehlercode3 = temp14
      doc.SB_Fehlercode4 = temp15
      doc.SB_Fehlercode5 = temp16
      doc.SB_Arbeitszeit = temp17
      doc.SB_Wartezeit = temp18
      doc.SB_Fahrzeit = temp19
      doc.SB_Fahrzone = temp20
      doc.SB_von = temp21
      doc.SB_bis = temp22
      doc.SB_Region = temp23
      Call Doc.Save(True, False)
Finished:
   Wend
   Goto SubClose
   
ERRORLABEL:
 'Msgbox "An error was encountered."
 'Print "Error: " Error(), "   Err:" Err(), " Erl:" Erl()
   If Err=213 Then
      Messagebox Filename & " was not found. Verify Path and Filename, then try the Import again."
      Resume SubEnd
   Else
      Messagebox "Error" & Str(Err) & ": " & Error$
   End If
   Resume Next
SubClose:
   xlApp.activeworkbook.close
   xlApp.Quit
   Set xlapp = Nothing
   Set view=db.getview("import1")
   Call ws.viewrefresh
SubEnd:
End Sub


--- Ende Code ---

Danke,
MOD

ata:
... ich habe mir leider nicht die Zeit genommen dein Script in allen Tiefen anzusehen, aber ich würde es mit Datumskonvertierungen versuchen - du findest alles in der NotesHilfe ...

ata

Navigation

[0] Themen-Index