Von Base-Formular einen Calc-Dialog öffnen

Programmierung unter AOO/LO (StarBasic, Python, Java, ...)

Moderator: Moderatoren

Antworten
wellmo
**
Beiträge: 32
Registriert: Di, 13.12.2011 19:21

Re: Von Base-Formular einen Calc-Dialog öffnen

Beitrag von wellmo »

UPDATE:

Ich hab das Problem gelöst. der Code um den Dialog zu starten sieht nun folgendermassen aus:

Code: Alles auswählen

Sub Dokuerstellen
Dim Doc As Object
Dim Url As String
Dim oSheet As Object
Dim Dummy()
Url = "file:///C:/Users/Dave/Desktop/nur Dave Offertenvorlage1.ods"
Doc = StarDesktop.loadComponentFromURL(Url, "_blank", 0, Dummy())
Doc.BasicLibraries.LoadLibrary("Standard")
Doc.DialogLibraries.LoadLibrary("Standard")
oForm = Doc.DialogLibraries.Standard.dOffertenerstellen
Dlg = CreateUnoDialog(oForm)
DateField1111 = dlg.getControl("DateField1111")   
   oDateFieldModel = dlg.Model.DateField1111
   oDateFieldModel.Text = date 'aktuelles Datum im Datefield anzeigen   
   oDoc2= ThisComponent
   oForm = oDoc2.DrawPage.Forms.getbyname("Kunden") 
oFeld1 = oForm.getbyname("txtAnrede") 
   sID1 = oFeld1.text
   TextField1 = dlg.getControl("TextField1")
   oTextFieldModel = dlg.Model.TextField1
   oTextFieldModel.Text = sID1
oFeld2 = oForm.getbyname("txtVorname")
   sID2 = oFeld2.text
   TextField2 = dlg.getControl("TextField2")
   oTextFieldModel = dlg.Model.TextField2
   oTextFieldModel.Text = sID2
oFeld3 = oForm.getbyname("txtNachname")
   sID3 = oFeld3.text
   TextField3 = dlg.getControl("TextField3")
   oTextFieldModel = dlg.Model.TextField3
   oTextFieldModel.Text = sID3
oForm2 = oForm.getbyname("Adressen")   
oFeld4 = oForm2.getbyname("txtAdressen")
   sID4 = oFeld4.text
   TextField4 = dlg.getControl("TextField4")
   oTextFieldModel = dlg.Model.TextField4
   oTextFieldModel.Text = sID4      
oFeld5 = oForm2.getbyname("txtPLZ")
   sID5 = oFeld5.text
   TextField5 = dlg.getControl("TextField5")
   oTextFieldModel = dlg.Model.TextField5
   oTextFieldModel.Text = sID5
oFeld6 = oForm2.getbyname("txtOrt")
   sID6 = oFeld6.text
   TextField6 = dlg.getControl("TextField6")
   oTextFieldModel = dlg.Model.TextField6
   oTextFieldModel.Text = sID6   
oFeld7 = oForm.getbyname("fmtID2")
   sID7 = oFeld7.text
   TextField7 = dlg.getControl("TextField7")
   oTextFieldModel = dlg.Model.TextField7
   oTextFieldModel.Text = sID7 
oForm3 = oForm.getbyname("Kundennummer")
oFeld8 = oForm3.getbyname("Kundennummer2")
   sID8 = oFeld8.text
   TextField8 = dlg.getControl("TextField8")
   oTextFieldModel = dlg.Model.TextField8
   oTextFieldModel.Text = sID8  
Dlg.execute()        
REM Datum übertragen
oDoc = ThisComponent   
  oSheet = Doc.CurrentController.getactiveSheet()
   oZell = oSheet.getCellRangeByName("B15") 'Zelle anpassen
   myDate = DateField1111.text
   oZell.Value= CDate(myDate)
   'oZell.NumberFormat = 30 'Format 13.05.06
   'oZell.NumberFormat = 31 'Format Sa 13.Mai 06
   'oZell.NumberFormat = 32 'Format 05.06
   'oZell.NumberFormat = 33 'Format 13.Mai
   'oZell.NumberFormat = 34 'Format Mai
   'oZell.NumberFormat = 35 'Format 2. Quartal 06
   oZell.NumberFormat = 36 'Format 13.05.2006
   'oZell.NumberFormat = 38 'Format Samstag, 13. Mai 2006
   'oZell.NumberFormat = 39 'Format 13. Mai 06

   MyCtrl1= dlg.getControl("TextField1")
   MyText1 = MyCtrl1.Model.Text
   mycell1 = oSheet.getCellRangeByName("C7")   
   mycell1.string=MyText1   

   MyCtrl2= dlg.getControl("TextField2")
   MyText2 = MyCtrl2.Model.Text
   mycell2 = oSheet.getCellRangeByName("C1")   
   mycell2.string=MyText2   

   MyCtrl3= dlg.getControl("TextField3")
   MyText3 = MyCtrl3.Model.Text
   mycell3 = oSheet.getCellRangeByName("D1")   
   mycell3.string=MyText3   

   MyCtrl4= dlg.getControl("TextField4")
   MyText4 = MyCtrl4.Model.Text
   mycell4 = oSheet.getCellRangeByName("C9")   
   mycell4.string=MyText4   

   MyCtrl5= dlg.getControl("TextField5")
   MyText5 = MyCtrl5.Model.Text
   mycell5 = oSheet.getCellRangeByName("C2")   
   mycell5.string=MyText5 


   MyCtrl6= dlg.getControl("TextField6")
   MyText6 = MyCtrl6.Model.Text
   mycell6 = oSheet.getCellRangeByName("D2")   
   mycell6.string=MyText6   



   MyCtrl7= dlg.getControl("TextField7")
   MyText7 = MyCtrl7.Model.Text
   mycell7 = oSheet.getCellRangeByName("E2")   
   mycell7.string=MyText7   



   MyCtrl8= dlg.getControl("TextField8")
   MyText8 = MyCtrl8.Model.Text
   mycell8 = oSheet.getCellRangeByName("E17")   
   mycell8.string=MyText8   



   MyCtrl1111= dlg.getControl("TextField1111")
   MyText1111 = MyCtrl1111.Model.Text
   mycell1111 = oSheet.getCellRangeByName("F1")   
   mycell1111.string=MyText1111   
  
End Sub
In dem Code musste ich noch die Codezeilen schreiben die die Werte aus den Textfeldern des Dialoges ins Sheet übertragen.

und in die Bibliothek " Meine Makros" musste ich die Codes schreiben für die Buttons ( Speichern, Drucken) auf den Sheets und noch ein paar Variablen als global definieren.

Code: Alles auswählen

global dlg as Object
global odialog as Object
global Dlg as Object
global oDialog as Object
global oDoc as Object
global oSheet as Object
global mySheet as Object
global Doc as Object

Sub GlobaleVariablen
end Sub

Sub SpeichernOfferte1seitig
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim oCell4 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Offerte1seitig")
   oCell1 = oSheet.getCellByPosition(3, 0) 
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0)
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
if oSheet.getcellbyposition(0 ,11).string = "Offerte" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Offerten"" (""KundenID"", ""Offerten"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if   
if oSheet.getcellbyposition(0 ,11).string = "Auftragsbestätigung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Aufträge"" (""KundenID"", ""Aufträge"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if
if oSheet.getcellbyposition(0 ,11).string = "Rechnung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end if    
end Sub
   
Sub SpeichernOfferte
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Offerte")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(1,13) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
  if oSheet.getcellbyposition(0 ,11).string = "Offerte" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Offerten"" (""KundenID"", ""Offerten"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if   
if oSheet.getcellbyposition(0 ,11).string = "Auftragsbestätigung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Aufträge"" (""KundenID"", ""Aufträge"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if 
end Sub

Sub SpeichernOfferte15seitig
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Offerte15seitig")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
if oSheet.getcellbyposition(0 ,11).string = "Offerte" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Offerten"" (""KundenID"", ""Offerten"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if   
if oSheet.getcellbyposition(0 ,11).string = "Auftragsbestätigung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Aufträge"" (""KundenID"", ""Aufträge"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if
if oSheet.getcellbyposition(0 ,11).string = "Rechnung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end if 
end Sub

Sub SpeichernOfferte25seitig
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Offerte25seitig")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
if oSheet.getcellbyposition(0 ,11).string = "Offerte" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Offerten"" (""KundenID"", ""Offerten"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if   
if oSheet.getcellbyposition(0 ,11).string = "Auftragsbestätigung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Aufträge"" (""KundenID"", ""Aufträge"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if
if oSheet.getcellbyposition(0 ,11).string = "Rechnung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end if 
end Sub

Sub SpeichernOfferte35seitig
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Offerte35seitig")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
if oSheet.getcellbyposition(0 ,11).string = "Offerte" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Offerten"" (""KundenID"", ""Offerten"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if   
if oSheet.getcellbyposition(0 ,11).string = "Auftragsbestätigung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Aufträge"" (""KundenID"", ""Aufträge"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if
if oSheet.getcellbyposition(0 ,11).string = "Rechnung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end if 
end Sub

Sub SpeichernOfferte05seitig
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Offerte05seitig")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
if oSheet.getcellbyposition(0 ,11).string = "Offerte" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Offerten"" (""KundenID"", ""Offerten"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if   
if oSheet.getcellbyposition(0 ,11).string = "Auftragsbestätigung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Aufträge"" (""KundenID"", ""Aufträge"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if
if oSheet.getcellbyposition(0 ,11).string = "Rechnung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end if 
end Sub

Sub SpeichernAkonto
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Akonto")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   Filename = "RG" & " " & sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
if oSheet.getcellbyposition(0 ,11).string = "1.Akonto" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if   
if oSheet.getcellbyposition(0 ,11).string = "2.Akonto" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end if
if oSheet.getcellbyposition(0 ,11).string = "Schlussrechnung" then    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end if 
end Sub

Sub SpeichernRG1SmV
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("RG1SmV")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())    
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end Sub

Sub SpeichernRG1SoV
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("RG1SoV")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(1, 13) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Rechnungen"" (""KundenID"", ""Rechnungen"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL) 
end Sub

Sub SpeichernLieferschein
   Dim oDoc As Object
   Dim oSheet As Object
   Dim oCell1 As Object
   Dim oCell2 As Object
   Dim oCell3 As Object
   Dim sName As String
   Dim sVorname As String
   Dim sLaufwerk As String
   Dim sDirname As String
   Dim sFilename as String
   Dim DatabaseContext as Object
   Dim oDataquelle as Object 
   Dim oDatVerb as Object
   Dim oStatement as Object
   Dim sSQL as String
   Dim sID as String
   Dim dummy()
   oDoc = ThisComponent
   oSheet = oDoc.Sheets.getByName("Lieferschein")
   'Falls der Name in einer Zelle abgelegt wurde:
   oCell1 = oSheet.getCellByPosition(3, 0) 'Tabellenfeld, in dem der Name steht
   sName = oCell1.String
   oCell2 = oSheet.getCellByPosition(2, 0) 'Tabellenfeld, in dem der Vorname steht
   sVorname = oCell2.String
   oCell3 = oSheet.getCellByPosition(4, 0) 'Tabellenfeld, in dem das Geburtsdatum steht (Format TT.MM.JJJJ)
   sNummer= oCell3.String
   oCell4 = oSheet.getCellByPosition(4, 1)
   sID= oCell4.String
   sLaufwerk = "C:/Users/Dave/Desktop/Kunden/"
   sDirname =  sName & " " & sVorname
   sFilename = sNummer & " " & sName & " " & sVorname
   neuerpfad = sLaufwerk + "/" + sDirname + "/" + sFilename + ".ods"
   dateiurl=converttourl(neuerpfad)
   odoc.storetourl(dateiurl,dummy())
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDatenquelle = DatabaseContext.getByName("wellmo nur Dave")
   oDatVerb = oDatenquelle.getConnection("","")
   oStatement = oDatVerb.createStatement()
   sSQL = "INSERT INTO ""Lieferscheine"" (""KundenID"", ""Lieferscheine"", ""Datum"") VALUES ('"+sID+"','"+sFilename+"', now)"
   oStatement.executeUpdate(sSQL)
end Sub

Sub DruckenOfferte
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(1)
Cellrangeaddress.Sheet = 1
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 184
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenOfferte1seitig
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(42)
Cellrangeaddress.Sheet = 42
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 90
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenOfferte15seitig
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(45)
Cellrangeaddress.Sheet = 45
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 108
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenOfferte25seitig
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(46)
Cellrangeaddress.Sheet = 46
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 163
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenOfferte35seitig
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(47)
Cellrangeaddress.Sheet = 47
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 219
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenOfferte05seitig
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(49)
Cellrangeaddress.Sheet = 49
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 54
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenRGS1mV
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(43)
Cellrangeaddress.Sheet = 43
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 53
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenRGS1oV
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(44)
Cellrangeaddress.Sheet = 44
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 53
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenAkonto
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(48)
Cellrangeaddress.Sheet = 48
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 53
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub DruckenLieferschein
Dim CellrangeAddress AS New com.sun.star.table.CellRangeAddress
Dim args(0)
Doc = ThisComponent
Sheet = Doc.Sheets(50)
Cellrangeaddress.Sheet = 50
Cellrangeaddress.StartColumn = 0
Cellrangeaddress.Startrow = 0
Cellrangeaddress.EndColumn = 5
Cellrangeaddress.EndRow = 55
args(0) = Cellrangeaddress
Sheet.setPrintAreas(args()) 
dim printProp(1) as new com.sun.star.beans.PropertyValue
printProp(0).name = "Wait"
printProp(0).Value = true
printProp(1).name = "CopyCount"
printProp(1).Value = 2
Doc.print(printProp())
end Sub

Sub Tabellenwechsel(quelle)
x = quelle.Source.Model.Label
blatt = ThisComponent.Sheets().getByName(x)
ThisComponent.CurrentController.setActiveSheet(blatt)
End Sub

Somit funktioniert alles wie es muss und soll, ich bin Happy und Danke euch allen nochmals für die Hilfe. Evtl. kann man ja den einten oder anderen Code nun auch von mir abgucken :shock:

Das einzige was schade ist, das man die Codes aus "Meine Makros" mit "REM" in die Dokumenten-Bibliothek kopieren muss, damit man es auf anderen Rechnern auch zum laufen bringt.

Ich habe beide Dateien nochmals hochgeladen. Damit Sie funktionieren muss man in der Bibliothek "Meine Makros" ein Modul Namens "Global" machen und den markierten Code in der Bibliothek "wellmo nur Dave" im Modul1 dort reinkopieren.
Ich hoffe jmd kann etwas damit, oder einem Teil davon, anfangen und ich kann somit auch wem behilflich sein.

Gruss Dave
Dateianhänge
wellmo nur Dave.odb
(34.61 KiB) 73-mal heruntergeladen
nur Dave Offertenvorlage1.ods
(103.89 KiB) 112-mal heruntergeladen
Nach oben
Antworten

Zurück zu „Makros und allgemeine Programmierung“