VBScript Print from Calc

Discussions about using 3rd party extension with OpenOffice.org
Post Reply
kha@di-teknik.dk
Posts: 1
Joined: Wed Aug 03, 2016 11:25 am

VBScript Print from Calc

Post by kha@di-teknik.dk »

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

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
leonard_pgh
Posts: 3
Joined: Wed Feb 01, 2017 7:10 pm

Re: VBScript Print from Calc

Post by leonard_pgh »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VBScript Print from Calc

Post by Zizi64 »

Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
leonard_pgh
Posts: 3
Joined: Wed Feb 01, 2017 7:10 pm

Re: VBScript Print from Calc

Post by leonard_pgh »

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

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

Post by leonard_pgh »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VBScript Print from Calc

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Post Reply