Berikut adalah sedikit tips dan trik untuk para pengemar VB6 yang sering menggunakan output reportnya
ke Ms Excel dan 'terpaksa' harus di rubah ke OpenOffice.
Untuk membuka File dan tulis di Calc
' ***************
Dim oSM 'Object Root Membuka OpenOffice dari VB
Dim oSheet As Object ' Object Untuk Buka Sheet
Dim oDesk 'Object Untuk start Frame Desktop OpenOffice
Dim oPageStyles As Object ' Object Untuk Property Output
Dim oDoc As Object 'Object Untuk Membuka Dokument
Dim arg() 'Object Kosong
Set oSM = CreateObject("com.sun.star.ServiceManager")
Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
Set oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, arg())
Set oSheet = oDoc.getSheets().getByName("Sheet1") ' Aktifkan Sheet Calc
Set oPageStyles = oDoc.StyleFamilies.getByName("PageStyles")
oPageStyles.getByName("Default").HeaderIsOn = False ' Header Di matikan
oPageStyles.getByName("Default").FooterIsOn = False ' Footer di matikan
oPageStyles.getByName("Default").pagescale = 100 ' Skala Output 100%
oSheet.getCellByposition(0, 15).setString ("Total ") 'Cetak Kata 'Total' di Kolom 0 (A) dan Kolom Baris 15
' ***************
Untuk Mengeluarkan report ke pdf
' ***************
Dim oSM 'Object Root Membuka OpenOffice dari VB
Dim oSheet As Object ' Object Untuk Buka Sheet
Dim oDesk 'Object Untuk start Frame Desktop OpenOffice
Dim oPageStyles As Object ' Object Untuk Property Output
Dim oDoc As Object 'Object Untuk Membuka Dokument
Dim arg() 'Object Kosong
Dim OpenParam(1) As Object ' Parameter tuk buka file
Set oSM = CreateObject("com.sun.star.ServiceManager")
Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
' memakai function MakeProvertyValue Tuk assign fungsinya Hidden / tidak file yg kita buka
' dalam contoh ini file OO di hidden sehingga kesannya langsung dibentuk pdf-nya
Set OpenParam(0) = MakePropertyValue("Hidden", True)
Set oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, OpenParam())
Set oSheet = oDoc.getSheets().getByName("Sheet1") ' Aktifkan Sheet Calc
Set oPageStyles = oDoc.StyleFamilies.getByName("PageStyles")
oPageStyles.getByName("Default").HeaderIsOn = False ' Header Di matikan
oPageStyles.getByName("Default").FooterIsOn = False ' Footer di matikan
oPageStyles.getByName("Default").pagescale = 100 ' Skala Output 100%
oSheet.getCellByposition(0, 15).setString ("Total ") 'Cetak Kata 'Total' di Kolom 0 (A) dan Kolom Baris 15
' buka file calc dan di convert, dikarenakan penamaan direktori OO beda dgn windows
' maka harus diconvert terlebih dahulu dengan modul converttourl
Call oDoc.storeToURL(ConvertToUrl(App.Path & "\test.odt"), arg())
' simpan dan buka file OO ke pdf dengan modul SaveAsPDF
SaveAsPDF Trim(App.Path & "\testpdf" & Format(Now(), "nnss")), oDoc, oDesk
Public Function MakePropertyValue(cName, uValue) As Object
Dim oPropertyValue As Object
Dim oSM As Object
Set oSM = CreateObject("com.sun.star.ServiceManager")
Set oPropertyValue = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oPropertyValue.Name = cName
oPropertyValue.Value = uValue
Set MakePropertyValue = oPropertyValue
End Function
Public Sub SaveAsPDF(FilenameAndPath As String, Vodoc As Object, Vodesk)
Dim arg()
Dim OpenParam(1) As Object 'Parameters to open the doc
Dim SaveParam(1) As Object 'Parameters to save the doc
Set OpenParam(0) = MakePropertyValue("Hidden", True) ' Open the file hidden
Set SaveParam(0) = MakePropertyValue("FilterName", "calc_pdf_Export")
Call Vodoc.storeToURL(ConvertToUrl(Trim(FilenameAndPath)) & ".pdf", SaveParam())
ShellExecute 0&, "open", FilenameAndPath & ".pdf", "", "", vbMaximizedFocus
Set oDesk = Nothing
Set oSM = Nothing
End Sub
Public Function ConvertToUrl(strFile) As String
strFile = Replace(strFile, "\", "/")
' strFile = Replace(strFile, ":", "|")
strFile = Replace(strFile, " ", "%20")
strFile = "file:///" + strFile
ConvertToUrl = strFile
End Function
' ***************
Untuk membuat Hasil Printing Landscape
' *******************
' Untuk disisipkan pada program "Untuk membuka File dan tulis di Calc "
Dim paramCetak(0) As Object
Set paramCetak(0) = MakePropertyValue("PaperOrientation", 1) ' 0=PORTRAIT 1=LANDSCAPE
oDoc.Printer = paramCetak()
' *******************
Daftar Pustaka : "Useful Macro Information For OpenOffice.org By Andrew Pitonyak"
Senin, 04 Mei 2009
Langganan:
Postingan (Atom)