Tips & Trik VB6

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"