VBScript Print from Calc

Discussions about using 3rd party extension with OpenOffice.org

VBScript Print from Calc

Postby kha@di-teknik.dk » Wed Aug 03, 2016 11:44 am

Hi All

I am a beginner i vbs and need som help! I have a problem with my script.

info:
OS Windows 10 and server 2012
VBS is written in WinCC from Siemens
OpenOffice 4.1.2 Calc

I have a vb script that moves som values to a calc spreedsheat and all this works
but i can't get it to print the spreedsheat out.

My code so fare :-)

Code: Select all   Expand viewCollapse view
Sub OnClick(ByVal Item)                                                               
Dim WinCCTag
Dim TankNr
Dim objTag
Dim Year
Dim Month
Dim Day
Dim Dato
Dim Tid
Dim objPopup
Dim sMO
Dim oD
Dim wb
Dim oSheet



'Lave dato til file navn
   Year = DatePart("yyyy",Date,vbMonday,vbFirstFourDays)
   Month = DatePart("m",Date,vbMonday,vbFirstFourDays)
   Day = DatePart("D",Date,vbMonday,vbFirstFourDays)

   Dato = Year &  Month &  Day & Hour(Now) & Minute(Now) & Second (Now)

'Hente Tank nr.
   Set TankNr = HMIRuntime.Tags("Lokal.TankNR")
   TankNr.Read

'Åben OpenOffice Calc
Set sMO = CreateObject("com.sun.star.ServiceManager")  'To open the Sun Server
Set oD = sMO.createInstance("com.sun.star.frame.Desktop")  'To open the Desktop
Set wb = oD.loadComponentFromURL("file:///C:\Projekt\Div\Tank_Parameter\Master\Parameter_Master_Tank.ods", "_blank", 0, Array())
Set oSheet = wb.CurrentController.ActiveSheet

'Skriv data til rapporten
      oSheet.getCellByPosition( 3, 4 ).String = Day & "." & MonthName (Month) & " " & Year         
      oSheet.getCellByPosition( 3, 5 ).String = Hour(Now) & ":" & Minute(Now) & ":" & Second (Now)
      oSheet.getCellByPosition( 3, 6 ).String = TankNr.Value
   
      Set WinCCTag = HMIRuntime.Tags("Lokal.Gruppe")
         WinCCTag.Read
      oSheet.getCellByPosition(3, 8).String = WinCCTag.Value
            
      Set WinCCTag = HMIRuntime.Tags("Lokal.Kunde")
         WinCCTag.Read
      oSheet.getCellByPosition(3, 9).String = WinCCTag.Value
   
      Set WinCCTag = HMIRuntime.Tags("Lokal.Produkt")
         WinCCTag.Read
      oSheet.getCellByPosition(3, 10).String = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags("Lokal.A_Maal")
         WinCCTag.Read
      oSheet.getCellByPosition(3,12).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags("Lokal.Diameter")
         WinCCTag.Read
      oSheet.getCellByPosition(3,13).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags("Lokal.Hoejde")
         WinCCTag.Read
      oSheet.getCellByPosition(3, 14).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".BundVolumen")
         WinCCTag.Read
      oSheet.getCellByPosition(3, 15).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags("_Norminel_Niveau_SP")
         WinCCTag.Read
      oSheet.getCellByPosition(3, 16).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Density")
         WinCCTag.Read
      oSheet.getCellByPosition(3, 18).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Tank_Aktiv")
         WinCCTag.Read
         
      If WinCCTag.Value = 1 Then
      oSheet.getCellByPosition(3, 20).String = "Til"   
      Else
      oSheet.getCellByPosition(3, 20).String = "Fra"
      End If
      
      Set WinCCTag = HMIRuntime.Tags(".Niveau_SP")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 14).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Niveau_Dif_Tons")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 15).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Niveau_Dif_Tid_Min")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 16).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags("_HurtigNiveau_Dif_Tons")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 17).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags("_HurtigNiveau_Dif_Tid_Min")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 18).Value = WinCCTag.Value
            
      Set WinCCTag = HMIRuntime.Tags(".HH_AlarmLimit")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 20).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".H_AlarmLimit")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 21).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".L_AlarmLimit")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 22).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".LL_AlarmLimit")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 23).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Tons")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 25).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".VCF")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 26).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Produkt_25C")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 27).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Max_Volumen")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 28).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Niveau")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 8).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Volumen")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 9).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Temp")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 10).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Flow")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 11).Value = WinCCTag.Value
      
      Set WinCCTag = HMIRuntime.Tags(".Tid_Fuld")
         WinCCTag.Read
      oSheet.getCellByPosition(7, 12).Value = WinCCTag.Value      


On Error Resume Next   
'Print      
CallByName oSheet, "Print", vbMethod, Array() ------------------------------------------------------------------------------------------------- Help her

'Saving the document With completed status To a New file.
Call oSheet.StoreToURL("file:///C:\Projekt/Div/Tank_Parameter/" & TankNr.Value & "/" & TankNr.Value & "_" & Dato & ".ods", Array())

'Closing the document.
oSheet.Close (True)
Set wb = Nothing
Set oSM = Nothing
Set oD = Nothing
Set oSheet = Nothing

End Sub


Thanks!!!!
Last edited by RoryOF on Wed Aug 03, 2016 11:54 am, edited 1 time in total.
Reason: Added [code] tags. [RoryOF, Moderator]
OpenOffice 4.1.2 on Windows 10, Windows 7 and Server 2012
kha@di-teknik.dk
 
Posts: 1
Joined: Wed Aug 03, 2016 11:25 am

Re: VBScript Print from Calc

Postby leonard_pgh » Wed Feb 01, 2017 7:15 pm

hi, i have same problem with you. i have trouble to print in calc sheet from wincc.
i also have tried so many method like .print etc.

do you have solution for this ??

thanks
Open Office 4.1.2
leonard_pgh
 
Posts: 3
Joined: Wed Feb 01, 2017 7:10 pm

Re: VBScript Print from Calc

Postby Zizi64 » Wed Feb 01, 2017 8:34 pm

Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7918
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VBScript Print from Calc

Postby leonard_pgh » Thu Feb 02, 2017 4:05 am

thanks to your reply

sorry, actually i'm new to vb script.

this is part of my code which handle open office document
Code: Select all   Expand viewCollapse view
Dim oSM, oDesk, oDoc, oPropertyValue, oSheet, oCell, xPropSet
         Set oSM = CreateObject("com.sun.star.ServiceManager")
         Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
         Set oDoc = oDesk.loadComponentFromURL("file:///d:\Report\Report.xls", "_blank", 0, Array())
         Set oSheet = oDoc.Sheets.getByName("Sheet1")
         
         Set oCell = oSheet.getCellrangeByname ("A1:K1")
         oCell.Merge True
         Set oCell = oSheet.getcellbyPosition (0,0)
         oCell.String = "DAILY REPORT BCP"
         oCell.HoriJustify = 2 ' Center Alignment
         oCell.CharWeight = 150.000000 ' Bold
         oCell.CharHeight = 20
                     
Dim arg0,arg(0)
Dim dispatcher,document
         Set document   = ThisComponent.CurrentController.Frame
         Set dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
         dispatcher.executeDispatch document, ".uno:PrintDefault", "", 0, Array()
         'dispatcher.executeDispatch oDoc, ".uno:PrintDefault", "", 0, Array()
         'dispatcher.executeDispatch oSheet, ".uno:PrintDefault", "", 0, Array()
         
         'Set arg(0) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
         'arg(0).Name = "CopyCount"
         'arg(0).Value = 1
         'Print     
         'oDoc.Print (arg(0))
            
         If Err.Number <> 0 Then
            Msgbox Err.Description
            Err.Clear
         End If
         Set oPropertyValue = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
         oPropertyValue.Name = "FilterName"
         oPropertyValue.Value = "MS Excel 97"
         oDoc.storeToURL "file:///" &FileNew, Array(oPropertyValue)
         oDoc.Close True
         
         Set oPropertyValue = Nothing
         Set oCell = Nothing
         Set oDoc = Nothing
         Set oDesk = Nothing
         Set oSM = Nothing


as you can see in my code wincc i never use "As object" or "As", since it dont recognize it it will return in error code.
i've tried several code to just print my sheet but it seems return error.
Your code return "object required" and .Print code return error "automation bridge Uno convertion utilities ....".

thanks
Open Office 4.1.2
leonard_pgh
 
Posts: 3
Joined: Wed Feb 01, 2017 7:10 pm

Re: VBScript Print from Calc

Postby leonard_pgh » Thu Feb 02, 2017 4:13 am

i've discovered the print method
Set arg0 = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
arg0.Name = "CopyCount"
arg0.Value = 1
oDoc.Print Array(arg0)

thanks
Open Office 4.1.2
leonard_pgh
 
Posts: 3
Joined: Wed Feb 01, 2017 7:10 pm

Re: VBScript Print from Calc

Postby Zizi64 » Thu Feb 02, 2017 9:09 am

The name of the embedded programming language and IDE of the Apache OpenOffice asnd the LibreOffice is not "VB", nor "VBA", nor "VBscript". (These are products of the Micro$oft.)

The embedded IDE is named as "StarBasic" (or simply: "Basic"), and you can call the API (Application Programming Interface) functions from the Basic.


(But - of course - you can call the API functions from other codes written in other programming languages, and from other IDE-s)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7918
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Extensions

Who is online

Users browsing this forum: No registered users and 1 guest