[solved] Large copy from one doc to another doc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Large copy from one doc to another doc

Post by MATT.B »

its quick but lags a bunch at the save as commands
OpenOffice 4.1.1 ON WINDOWS 7 64
Top
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Large copy from one doc to another doc

Post by Villeroy »

Villeroy wrote:The StarBasic IDE may be primitive but it supports stop marks (F9), step mode (F8) and watch variables in a docking window (F7). This is very similar to the VBA IDE and allows clear and simple debugging of the Basic code. And yes, for the UNO objects you do need MRI or XRay.
Apart from the exact menu items and buttons, this VBA tutorial on debugging syntax errors, compile errors and runtime errors applies 100% to StarOffice. I whish that all the "whining Excel experts" on this forum would master these basics.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Top
User avatar
kingfisher
Volunteer
Posts: 2127
Joined: Tue Nov 20, 2007 10:53 am

Re: Large copy from one doc to another doc

Post by kingfisher »

I misunderstood. I thought you wanted the document to have focus so that you could close it. Thanks for the tip about 'dispose'.

You should install the extension MRI using Tools >Extension Manager.

I have set up the MRI library to load when the application is opened by including a sub in the application Standard library. Without that you would have to load the library in your code as shown.

Code: Select all

Dim oDoc1 : oDoc1 = ThisComponent
Dim oDoc2, sUrl
sUrl = "file:///data/docs/office/work/DataLog.ods" 'obviously change name and path
oDoc2 = StarDesktop.loadComponentFromURL( sUrl, "_blank", 0, Array() )
Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
MRI oDoc1
Using that code I was unable to find any property or method such as 'toFront' or 'setFocus'.

Focus, I would have thought, is managed by the operating system.
Apache OpenOffice 4.1.12 on Linux
Top
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Large copy from one doc to another doc

Post by Villeroy »

It's the frame you can focus() and the frame's component window can be moved toFront().
Getting_Frames,_Controllers_and_Models_from_Each_Other
An ordinary office document (also referred to as a "Component" or "Model") is exposed to the user by one or more controllers (aka "View") which provides the capabilities of user interaction (scrolling, clicking, selecting). After calling menu:Window>NewWindow your document has more than one controller but one distinct ActiveController. Each controller lives in one frame. A frame hosting some controller with a document has one container window (everything within the title bar and border decorations) and a component window (the area displaying the document content).

Code: Select all

doc = ThisComponent 
ctrl = doc.getCurrentController()
frame = ctrl.getFrame()
compWin = frame.getComponentWindow()
contWin = frame.getContainerWindow()
Apart from global variable ThisComponent (document hosting this Basic code or the currently active document), StarBasic knows the StarDesktop as another entry point which is basically the collection of all frames.

Code: Select all

dtp = StarDesktop
REM here we see that a component and a model are not exactly the same:
comp = StarDesktop.getCurrentComponent()
doc = comp.getModel()
frame = dtp.getCurrentFrame()
contWindow = frame.getContainerWindow()
compWindow = frame.getComponentWindow()
ctrl = frame.getController()
doc = ctrl.getModel()
Find out which is the desktop's ActiveFrame compared to the CurrentFrame.

All this had been discussed and deeply analysed 14 years ago by some true professionals on the old oooforum.org which seems to be lost by now.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Top
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Large copy from one doc to another doc

Post by MATT.B »

the issue I was having with the close statement was when my copy function completed it called another macro to save the doc but that macro was changing odoc so it was no loger set to be the document I wanted to close I fixed this by not using the same object names in my copy as my saveas macro. this also brings one last question to mind
I have been trying to figure out global variables.
so lest say I have 3 macros in one module.
in the module I set
global silent as string
and in macro 1 I set silent to = "true"
then I call macro 2 inside macro 1
how can I get silent to stay true when macro 2 is called with out defining silent inside macro 2?
right now it sets silent to a blank state when macro 2 is started
OpenOffice 4.1.1 ON WINDOWS 7 64
Top
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Large copy from one doc to another doc

Post by Villeroy »

What you try to do is too much for a beginner. Learn and practice programming basics and do your office work manually.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Top
User avatar
kingfisher
Volunteer
Posts: 2127
Joined: Tue Nov 20, 2007 10:53 am

Re: Large copy from one doc to another doc

Post by kingfisher »

Have a look at sections 16.2.3, 17.7, 23.50 and 3.4 in Andrew Pitonyak's macro document .
Apache OpenOffice 4.1.12 on Linux
Top
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Large copy from one doc to another doc

Post by MATT.B »

Villeroy wrote:What you try to do is too much for a beginner. Learn and practice programming basics and do your office work manually.
I have experience with q-basic and excel vba but talking to oo and excel are to very different things the concepts are the same but overall the language is far more complicated with Oo than excel or even writing scripts for windows domain, so when I started this task I thought I had a good knowledge trying to talk to oo actually made me feel rather stupid
OpenOffice 4.1.1 ON WINDOWS 7 64
Top
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: [solved]Large copy from one doc to another doc

Post by MATT.B »

here is a collection of my work it may be useful to some one it may not but I figured with every one that has helped ill post my final product. it still needs work but I'm taking a break

function to test doc type

Code: Select all

function fnWhichComponent(oDoc) as string
if HasUnoInterfaces(oDoc, "com.sun.star.lang.XServiceInfo") then 
   if thisComponent.supportsService ("com.sun.star.text.GenericTextDocument") then
      fnWhichComponent = "Text"
   elseif thisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
      fnWhichComponent = "Spreadsheet"
   elseif thisComponent.supportsService("com.sun.star.presentation.PresentationDocument") then
      fnWhichComponent = "Presentation"
   elseif thisComponent.supportsService("com.sun.star.drawing.GenericDrawingDocument") then
      fnWhichComponent = "Drawing"
   else
      fnWhichComponent = "Oops current document something else"
   end if
else
   fnWhichComponent = "Not a document"
end if
End function
function to replace charicters in string usage replace(source variable, "text to search", "replace search with this text")
Function Replace(Source As String, Search As String, NewPart As String)
Dim Result As String
Result = join(split(Source, Search), NewPart)
Replace = Result
End Function
[/code]


macro to do some stupp at startup probably useless to you

Code: Select all

SUB STARTUP
	'On Error GoTo UNKNOWNERROR
	if fnWhichComponent(thisComponent) <> "Spreadsheet" then
		exit sub
	end if
	wait 600
Dim oDoc as object
Dim oView as object
Dim oSheet as object
Dim oFrame as object
Dim oCell as object
Dim Disp as object
Dim Check as string
Dim tCheck as string
Dim oCheck as string
check = ""
tcheck = ""
ocheck = ""
dim args1(2) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "Zoom.Value"
	args1(0).Value = 100
	args1(1).Name = "Zoom.ValueSet"
	args1(1).Value = 28703
	args1(2).Name = "Zoom.Type"
	args1(2).Value = 0
	oDoc = ThisComponent
	oView = oDoc.getCurrentController()
	oFrame = ThisComponent.CurrentController.Frame
	oSheet = odoc.getSheets().getByIndex(0)
	disp = createUnoService("com.sun.star.frame.DispatchHelper")
	oCell = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("ZZ1")
	Check = oCell.getstring
	oCell = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("H11")
	tCheck = oCell.getstring
	oCell = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("H59")
	oCheck = oCell.getstring
	if check = "5555" then 
		exit sub
	end if
	if oCheck = "text" then 
		SETUPDOC
		exit sub
	END IF
	If check = 5615 then
		ret& = Msgbox ("Please make sure to double check your work. Thank you", 10 + 1, "Reminder")
		disp.executeDispatch(oFrame, ".uno:Zoom", "", 0, args1())
		oCell = oSheet.getCellRangeByName("J1")
		oView.select(oCell)
		exit sub	
	else	
		if tcheck = "text" then
			'ret& = Msgbox ("ESTIMATE PAGE DETECTED", 10 + 1, "INFORMATION")
			disp.executeDispatch(oFrame, ".uno:Zoom", "", 0, args1())
			oCell = oSheet.getCellRangeByName("J1")
			oView.select(oCell)
			PREP
		end if
	end if	
UNKNOWNERROR:
   ret& = Msgbox ("Please make sure to double check your work. Thank you", 10 + 1, "Error")
Exit Sub
End Sub
macro to save doc as ods and pdf taking file name from cell

Code: Select all

Sub SaveAs1 
	'On Error GoTo UNKNOWNERROR
	if fnWhichComponent(thisComponent) <> "Spreadsheet" then
		exit sub
	end if
Dim oDoc as object
Dim oView as object
Dim oSheet as object
Dim oFrame as object
Dim oCell as object
Dim Disp as object
Dim Check as string
Dim tCheck as string
Dim MyFilename as string
Dim MyPath as string
Dim Cname as string
Dim url as string
Dim url1 as string
Dim Page as string
Dim args1(2) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "Zoom.Value"
	args1(0).Value = 100
	args1(1).Name = "Zoom.ValueSet"
	args1(1).Value = 28703
	args1(2).Name = "Zoom.Type"
	args1(2).Value = 0
Dim args2(1) as new com.sun.star.beans.PropertyValue
Dim Arg(0) as new com.sun.star.beans.PropertyValue
	oDoc = ThisComponent
	oView = oDoc.getCurrentController()
	oSheet = odoc.getSheets().getByIndex(0)
	disp = createUnoService("com.sun.star.frame.DispatchHelper")
	oFrame = ThisComponent.CurrentController.Frame
	oCell = oSheet.getCellRangeByName("ZZ1")
	Check = oCell.getstring
	oCell = oSheet.getCellRangeByName("H11")
	tCheck = oCell.getstring
	if check = "5555" then 
		exit sub
	end if
	If check = "5615" then
		oCell = oSheet.getCellRangeByName("C15")
		Cname = oCell.getstring
		Cname = replace(Cname, "/", " ")
		Cname = replace(Cname, "\", " ")
		ocell.setstring(Cname)
		oCell = oSheet.getCellRangeByName("U2")
		myFilename = oCell.getstring
		IF MyFilename = "  " and tCheck = "text" then
			Disp.executeDispatch(oView, ".uno:Zoom", "", 0, args1())
			oCell = oSheet.getCellRangeByName("J1")
			oView.select(oCell)
			ret& = Msgbox ("You will be prompted to save!" , 10 + 1, "INFORMATION MISSING")
			EXIT SUB
		END IF
		MyPath = "C:\Users\main\Documents\"
		url = ConvertToUrl(MyPath & MyFilename & ".ods") 
		url1= ConvertToUrl(MyPath & MyFilename & ".pdf") 
		'ret& = Msgbox ("Files saved to folder " & MyPath &  " with a filenames of " & MyFilename & " .ods and " & MyFilename & " .pdf", 10 + 1, "Information")
		oDoc.StoreAsUrl(url,Array())
		oCell = oSheet.getCellRangeByName("W47")
		Page = oCell.getstring
		IF PAGE = 1 THEN
			Arg(0).Name = "PageRange"
			Arg(0).Value = "1"
			args2(1).Name = "FilterData"
			args2(1).Value = Arg()
			goto pdf
		END IF
		IF PAGE = 2 THEN
			Arg(0).Name = "PageRange"
			Arg(0).Value = "1,2"
			args2(1).Name = "FilterData"
			args2(1).Value = Arg()
			goto pdf
		END IF
		IF PAGE = 3 THEN
			Arg(0).Name = "PageRange"
			Arg(0).Value = "1,2,3"
			args2(1).Name = "FilterData"
			args2(1).Value = Arg()
			goto pdf	
		END IF
		IF PAGE = 4 THEN
			Arg(0).Name = "PageRange"
			Arg(0).Value = "1,2,3,4"
			args2(1).Name = "FilterData"
			args2(1).Value = Arg()
			goto pdf
		END IF
		IF PAGE = 5 THEN
			Arg(0).Name = "PageRange"
			Arg(0).Value = "1,2,3,4,5"
			args2(1).Name = "FilterData"
			args2(1).Value = Arg()
			goto pdf	
		END IF			
		IF PAGE = 6 THEN
			Arg(0).Name = "PageRange"
			Arg(0).Value = "1,2,3,4,5,6"
			args2(1).Name = "FilterData"
			args2(1).Value = Arg()
			goto pdf
		End IF
	
	PDF:	
		args2(0).Name = "FilterName"
		args2(0).Value = "calc_pdf_Export"
		oDoc.storeToURL(url1,args2())
		oCell = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("z1")
		silent = oCell.getstring()
		'print silent
		if silent = "true" then
			'print "silent = true"
			exit sub
			end if	
			ret& = Msgbox ("Files saved to folder " & MyPath &  " with a filenames of " & MyFilename & " .ods and " & MyFilename & " .pdf", 10 + 1, "Information")
			EXIT SUB
		
		end if
UNKNOWNERROR:
	ret& = Msgbox ("Unknown error, Auto save macro is disabled, and you will be prompted to save", 10 + 1, "ERROR")
End Sub
code to setup document for copy and copy

Code: Select all

sub setupdoc
'ON ERROR RESUME NEXT
ret& = Msgbox ("Please don't click or type any thing, Setting up document", 10 + 1, "Please wait")
thisComponent.unprotect("")
ThisComponent.getSheets().getByIndex(0).unProtect("WOLVERINE")
ThisComponent.getSheets().getByIndex(0).unProtect("")
Dim osDoc as object
Dim osView as object
Dim osSheet as object
Dim osFrame as object
Dim osRange as object
Dim osCell as object
Dim oDoc1 as object
Dim Doc1 as object
Dim Doc2 as object
Dim oTDoc as object
Dim oTSheet as object
Dim oTCell as object
dim otview as object
Dim Disp as object
Dim oDel1 as object
Dim oDel2 as object
Dim oDel3 as object
DIM oSRangeA1 as object
Dim oSRangeA2 as object
Dim oSRangeA3 as object
Dim oSRangeA4 as object
Dim oSRangeA5 as object
Dim oSRangeA6 as object 
DIM oSRangeB1 as object
Dim oSRangeB2 as object
Dim oSRangeB3 as object
Dim oSRangeB4 as object
Dim oSRangeB5 as object
Dim oSRangeB6 as object
DIM oSRangeC1 as object
Dim oSRangeC2 as object
Dim oSRangeC3 as object
Dim oSRangeC4 as object
Dim oSRangeC5 as object
Dim oSRangeC6 as object
DIM oSRangeD1 as object
Dim oSRangeD2 as object
Dim oSRangeD3 as object
Dim oSRangeD4 as object
Dim oSRangeD5 as object
Dim oSRangeD6 as object
DIM oSRangeE1 as object
Dim oSRangeE2 as object
Dim oSRangeE3 as object
Dim oSRangeE4 as object
Dim oSRangeE5 as object
Dim oSRangeE6 as object
DIM oSRangeF1 as object
Dim oSRangeF2 as object
Dim oSRangeF3 as object
Dim oSRangeF4 as object
Dim oSRangeF5 as object
Dim oSRangeF6 as object
Dim oTsheet1 as object
Dim oTsheet2 as object
Dim oTsheet3 as object
Dim oTsheet4 as object
Dim oTsheet5 as object
Dim oTsheet6 as object
Dim oCell1 as object
Dim oCell2 as object
Dim oCell3 as object
Dim oCell4 as object
Dim oCell5 as object
Dim oCell6 as object
DIM Total As String
Dim sUrl As String
silent = "true"
Dim args3(0) as new com.sun.star.beans.PropertyValue
Dim NoArg()
Doc1 = ThisComponent.CurrentController.Frame.ContainerWindow
oDoc1 = ThisComponent
osdoc = ThisComponent
osView = osdoc.getCurrentController()
osFrame = osView.getFrame()
osSheet = osdoc.getSheets.getByIndex(0)
Disp = createUnoService("com.sun.star.frame.DispatchHelper")
oDEL1 = osSheet.getCellRangeByName("ZZ1")
oDEL2 = osSheet.getCellRangeByName("ZZ2")
oDEL3 = osSheet.getCellRangeByName("ZZ3")
args3(0).Name = "Flags"
args3(0).Value = "SVDFN"
osView.Select(ODEL1)
Disp.executeDispatch(osView, ".uno:Delete", "", 0, args3()) 'copy
osView.Select(ODEL2) 
Disp.executeDispatch(osView, ".uno:Delete", "", 0, args3())
osView.Select(ODEL3) 
Disp.executeDispatch(osView, ".uno:Delete", "", 0, args3())
osRange = osSheet.getCellRangeByPosition(0,0,21,300)
osView.Select(osRange)
Disp.executeDispatch(osView, ".uno:Copy", "", 0, NoArg()) 
sURL = ConvertToUrl("C:\Users\main\Documents\ESTIMATE.ots")
oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
addsheet
oTSheet = oTDoc.getSheets.getByIndex(6)
oTCell = oTSheet.getCellByPosition(0,0)
oTDoc.getCurrentController().Select(oTCell)
oTframe = oTDoc.getCurrentController().getFrame()
disp.executeDispatch(oTFrame, ".uno:Paste", "", 0, NoArg())
UNPROTECTALL

oCell = oTsheet.getCellRangeByName("S44")
oCell.setformula("=F44+G44+J44+L44+N44+P44+R44")
oCell = oTsheet.getCellRangeByName("S45")
oCell.setformula("=F45+G45+J45+L45+N45+P45+R45")
oCell = oTsheet.getCellRangeByName("S92")
oCell.setformula("=F92+G92+J92+L92+N92+P92+R92")
oCell = oTsheet.getCellRangeByName("S93")  
oCell.setformula("=F93+G93+J93+L93+N93+P93+R93")
oCell = oTsheet.getCellRangeByName("S140")  
oCell.setformula("=F140+G140+J140+L140+N140+P140+R140")
oCell = oTsheet.getCellRangeByName("S141")  
oCell.setformula("=F141+G141+J141+L141+N141+P141+R141")
oCell = oTsheet.getCellRangeByName("S188")  
oCell.setformula("=F188+G188+J188+L188+N188+P188+R188")
oCell = oTsheet.getCellRangeByName("S189")  
oCell.setformula("=F189+G189+J189+L189+N189+P189+R189")
oCell = oTsheet.getCellRangeByName("S236")  
oCell.setformula("=F236+G236+J236+L236+N236+P236+R236")
oCell = oTsheet.getCellRangeByName("S237")  
oCell.setformula("=F237+G237+J237+L237+N237+P237+R237")
oCell = oTsheet.getCellRangeByName("S284")  
oCell.setformula("=F284+G284+J284+L284+N284+P284+R284")
oCell = oTsheet.getCellRangeByName("S285")
oCell.setformula("=F285+G285+J285+L285+N285+P285+R285")
otDoc = ThisComponent
otView = otDoc.getCurrentController()
otFrame = otView.getFrame()
oTSheet1 = otdoc.getSheets().getByIndex(0)
oTSheet2 = otdoc.getSheets().getByIndex(1)
oTSheet3 = otdoc.getSheets().getByIndex(2)
oTSheet4 = otdoc.getSheets().getByIndex(3)
oTSheet5 = otdoc.getSheets().getByIndex(4)
oTSheet6 = otdoc.getSheets().getByIndex(5)
oSSheet = otdoc.getSheets().getByIndex(6)
oSRangeA1 = oSsheet.getCellRangeByName("B15:R19")
oSRangeA2 = oSsheet.getCellRangeByName("B21:J32")
oSRangeA3 = oSsheet.getCellRangeByName("M21:O32")
oSRangeA4 = oSsheet.getCellRangeByName("R21:T32")
oSRangeA5 = oSsheet.getCellRangeByName("F40:R40")
oSRangeA6 = oSsheet.getCellRangeByName("V40:V46")
oSRangeB1 = oSsheet.getCellRangeByName("B69:J80")
oSRangeB2 = oSsheet.getCellRangeByName("M69:O80")
oSRangeB3 = oSsheet.getCellRangeByName("R69:T80")
oSRangeB4 = oSsheet.getCellRangeByName("F88:R88")
oSRangeB5 = oSsheet.getCellRangeByName("V88:V94")
oSRangeC1 = oSsheet.getCellRangeByName("B117:J128")
oSRangeC2 = oSsheet.getCellRangeByName("M117:O128")
oSRangeC3 = oSsheet.getCellRangeByName("R117:T128")
oSRangeC4 = oSsheet.getCellRangeByName("F136:R136")
oSRangeC5 = oSsheet.getCellRangeByName("V136:V142")
oSRangeD1 = oSsheet.getCellRangeByName("B165:J176")
oSRangeD2 = oSsheet.getCellRangeByName("M165:O176")
oSRangeD3 = oSsheet.getCellRangeByName("R165:T176")
oSRangeD4 = oSsheet.getCellRangeByName("F184:R184")
oSRangeD5 = oSsheet.getCellRangeByName("V184:V190")
oSRangeE1 = oSsheet.getCellRangeByName("B213:J224")
oSRangeE2 = oSsheet.getCellRangeByName("M213:O224")
oSRangeE3 = oSsheet.getCellRangeByName("R213:T224")
oSRangeE4 = oSsheet.getCellRangeByName("F232:R232")
oSRangeE5 = oSsheet.getCellRangeByName("V232:V238")
oSRangeF1 = oSsheet.getCellRangeByName("B261:J272")
oSRangeF2 = oSsheet.getCellRangeByName("M261:O272")
oSRangeF3 = oSsheet.getCellRangeByName("R261:T272")
oSRangeF4 = oSsheet.getCellRangeByName("F280:R280")
oSRangeF5 = oSsheet.getCellRangeByName("V280:V286")
oTcellA1 = oTSheet1.getCellRangeByName("B15")
oTcellA2 = oTSheet1.getCellRangeByName("B21")
oTcellA3 = oTSheet1.getCellRangeByName("M21")
oTcellA4 = oTSheet1.getCellRangeByName("R21")
oTcellA5 = oTSheet1.getCellRangeByName("F40")
oTcellA6 = oTSheet1.getCellRangeByName("V40")
oTcellB1 = oTSheet2.getCellRangeByName("B21")
oTcellB2 = oTSheet2.getCellRangeByName("M21")
oTcellB3 = oTSheet2.getCellRangeByName("R21")
oTcellB4 = oTSheet2.getCellRangeByName("F40")
oTcellB5 = oTSheet2.getCellRangeByName("V40")
oTcellC1 = oTSheet3.getCellRangeByName("B21")
oTcellC2 = oTSheet3.getCellRangeByName("M21")
oTcellC3 = oTSheet3.getCellRangeByName("R21")
oTcellC4 = oTSheet3.getCellRangeByName("F40")
oTcellC5 = oTSheet3.getCellRangeByName("V40")
oTcellD1 = oTSheet4.getCellRangeByName("B21")
oTcellD2 = oTSheet4.getCellRangeByName("M21")
oTcellD3 = oTSheet4.getCellRangeByName("R21")
oTcellD4 = oTSheet4.getCellRangeByName("F40")
oTcellD5 = oTSheet4.getCellRangeByName("V40")
oTcellE1 = oTSheet5.getCellRangeByName("B21")
oTcellE2 = oTSheet5.getCellRangeByName("M21")
oTcellE3 = oTSheet5.getCellRangeByName("R21")
oTcellE4 = oTSheet5.getCellRangeByName("F40")
oTcellE5 = oTSheet5.getCellRangeByName("V40")
oTcellF1 = oTSheet6.getCellRangeByName("B21")
oTcellF2 = oTSheet6.getCellRangeByName("M21")
oTcellF3 = oTSheet6.getCellRangeByName("R21")
oTcellF4 = oTSheet6.getCellRangeByName("F40")
oTcellF5 = oTSheet6.getCellRangeByName("V40")
otView.Select(oSRangeA1)
Disp.executeDispatch(otView, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellA1) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeA2) 
Disp.executeDispatch(otView, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellA2) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeA3) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellA3) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeA3) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellA3) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeA5) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellA5) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeA5)
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellA5) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeB1) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellB1) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeB2) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellB2) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeB3) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellB3) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeB4) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellB4) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeB5) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellB5) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeC1) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellC1) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeC2) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellC2) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeC3) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellC3) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeC4) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellC4) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeC5) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellC5) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeD1) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellD1) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeD2) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellD2) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeD3) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellD3) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeD4) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellD4) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeD5) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellD5) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeE1) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellE1) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeE2) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellE2) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeE3) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellE3) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeE4) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellE4) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeE5) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellE5) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeF1) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellF1) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeF2) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellF2) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeF3) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellF3) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeF4) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellF4) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeF5) 
Disp.executeDispatch(otview, ".uno:Copy", "", 0, NoArg())
otview.Select(oTCellF5) 
Disp.executeDispatch(otFrame, ".uno:Paste", "", 0, NoArg())
otview.Select(oSRangeA1)
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeA2) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeA3) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeA3) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeA5) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeA5)
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeB1) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeB2) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeB3) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeB4) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeB5) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeC1) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeC2) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeC3) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeC4) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeC5) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeD1) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeD2) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeD3) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeD4) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeD5) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeE1) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeE2) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeE3) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeE4) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeE5) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeF1) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeF2) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeF3) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeF4) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
otview.Select(oSRangeF5) 
Disp.executeDispatch(otview, ".uno:Delete", "", 0, args3())
oCell = oSsheet.getCellRangeByName("H13")
Total = oCell.getstring
ocell = ThisComponent.getSheets.getByIndex(0).getCellRangeByName("A11")
ThisComponent.getCurrentController().Select(oCell)
ThisComponent.getSheets().removeByName("source")
oCell = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("z1")
oCell.setstring("true")
PROTECTALL
PREP
SAVEAS1
ThisComponent.unprotect("")
ThisComponent.getSheets().getByIndex(0).unprotect("")
oCell = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("z1")
oCell.setstring("")
ThisComponent.getSheets().getByIndex(0).protect("")
ThisComponent.protect("")
'Doc1.tofront()
'Doc1.setfocus()
oDoc1.close(true)
ret& = Msgbox ("You may now continue", 10 + 1, "Document Ready")
End Sub
code to print number of pages baced on a number inside a cell

Code: Select all

sub pageprint
'On Error GoTo UNKNOWNERROR
dim disp as object
Dim oSheet 
Dim oReturn : oReturn = oDoc.Sheets.getByIndex(0)
dim ocell as object
dim oview as object
DIM PCHECK AS STRING
Dim Page as string

oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSheet = odoc.getSheets().getByIndex(0)
oCell = oSheet.getCellRangeByName("ZZ3")
Pcheck = oCell.getstring
	If Pcheck <> 5615 then
		exit sub
	end if
oCell = oSheet.getCellRangeByName("W47")
page = oCell.getstring
	IF PAGE = 1 OR PAGE > 1 THEN
		oSheet = oDoc.Sheets.getByIndex(0)   
		oView.setActiveSheet(oSheet)
		oDoc.Print(Array())
		Wait 50
	END IF
	IF  PAGE = 2 OR PAGE > 2 THEN
		oSheet = oDoc.Sheets.getByIndex(1)   
		oView.setActiveSheet(oSheet)
		oDoc.Print(Array())
		Wait 50   
	END IF
	IF  PAGE = 3 OR PAGE > 3 THEN
		oSheet = oDoc.Sheets.getByIndex(2)   
		oView.setActiveSheet(oSheet)
		oDoc.Print(Array())
		Wait 50   
	END IF   
	IF PAGE = 4 OR PAGE > 4 THEN
		oSheet = oDoc.Sheets.getByIndex(3)   
		OVIEW.setActiveSheet(oSheet)
		ODOC.Print(Array())
		Wait 50   
	END IF
	IF  PAGE = 5 OR PAGE > 5 THEN
		oSheet = oDoc.Sheets.getByIndex(4)   
		oView.setActiveSheet(oSheet)
		oDoc.Print(Array())
		Wait 50   
	END IF   
	IF  PAGE = 6 OR PAGE > 6 THEN
		oSheet = oDoc.Sheets.getByIndex(5)   
		oView.setActiveSheet(oSheet)
		oDoc.Print(Array())
		Wait 50   
	END IF   
oView.setActiveSheet(oReturn)
EXIT SUB
UNKNOWNERROR:
ret& = Msgbox ("Unknown error, Print page macro is disabled", 10 + 1, "ERROR")
End Sub
prablaly useless to you
code to prep document for saveas1 macro if information is missing

Code: Select all

sub PREP
Dim oDoc as object
Dim oView as object
Dim oSheet as object
Dim oFrame as object
Dim oCell as object
Dim Disp as object
disp = createUnoService("com.sun.star.frame.DispatchHelper")
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oframe = oview.Frame
unprotectall
oSheet = odoc.getSheets().getByIndex(0)
'oView.Select(oCell)
oCell = oSheet.getCellRangeByName("u1")
oCell.setstring("FILENAME")
oCell = oSheet.getCellRangeByName("u2")
oCell.setformula("=CONCATENATE($Q$16;"+CHR$(34)+" "+CHR$(34)+";$C$15;"+CHR$(34)+" "+CHR$(34)+";$Q$18)")
oCell = oSheet.getCellRangeByName("ZZ1")
oCell.setstring("5615")
oCell = oSheet.getCellRangeByName("ZZ2")
oCell.setstring("5615")
oCell = oSheet.getCellRangeByName("ZZ3")
oCell.setstring("5615")
oCell = oSheet.getCellRangeByName("W47")
oCell.setformula("=$PAGE_6.$W$47+$PAGE_5.$W$47+$PAGE_4.$W$47+$PAGE_3.$W$47+$PAGE_2.$W$47+1")
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
oSheet = odoc.getSheets().getByIndex(1)
oCell = oSheet.getCellRangeByName("W47")
oCell.setformula("=IF($V$47>0;1;0)")
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
oSheet = odoc.getSheets().getByIndex(2)
oCell = oSheet.getCellRangeByName("W47")
oCell.setformula("=IF($V$47>0;1;0)")
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
oSheet = odoc.getSheets().getByIndex(2)
oCell = oSheet.getCellRangeByName("W47")
oCell.setformula("=IF($V$47>0;1;0)")
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
oSheet = odoc.getSheets().getByIndex(3)
oCell = oSheet.getCellRangeByName("W47")
oCell.setformula("=IF($V$47>0;1;0)")
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
oSheet = odoc.getSheets().getByIndex(4)
oCell = oSheet.getCellRangeByName("W47")
oCell.setformula("=IF($V$47>0;1;0)")
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
oSheet = odoc.getSheets().getByIndex(5)
oCell = oSheet.getCellRangeByName("W47")
oCell.setformula("=IF($V$47>0;1;0)")
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
protectall
oSheet = odoc.getSheets().getByIndex(0)
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
end sub
code to unprotect and protect document and pages

Code: Select all

SUB UNPROTECTALL
'ON ERROR RESUME NEXT
ThisComponent.unprotect("")
'ThisComponent.getSheets().getByIndex(0).unprotect("WOLVERINE")
ThisComponent.getSheets().getByIndex(0).unprotect("")
'ThisComponent.getSheets().getByIndex(1).unprotect("WOLVERINE")
ThisComponent.getSheets().getByIndex(1).unprotect("")
'ThisComponent.getSheets().getByIndex(2).unprotect("WOLVERINE")
ThisComponent.getSheets().getByIndex(2).unprotect("")
'ThisComponent.getSheets().getByIndex(3).unprotect("WOLVERINE")
ThisComponent.getSheets().getByIndex(3).unprotect("")
'ThisComponent.getSheets().getByIndex(4).unprotect("WOLVERINE")
ThisComponent.getSheets().getByIndex(4).unprotect("")
'ThisComponent.getSheets().getByIndex(5).unprotect("WOLVERINE")
ThisComponent.getSheets().getByIndex(5).unprotect("")
'ThisComponent.getSheets().getByIndex(6).unprotect("WOLVERINE")
'ThisComponent.getSheets().getByIndex(6).unprotect("")
end sub

SUB PROTECTALL
ThisComponent.getSheets().getByIndex(0).Protect("")
ThisComponent.getSheets().getByIndex(1).Protect("")
ThisComponent.getSheets().getByIndex(2).Protect("")
ThisComponent.getSheets().getByIndex(3).Protect("")
ThisComponent.getSheets().getByIndex(4).Protect("")
ThisComponent.getSheets().getByIndex(5).Protect("")
ThisComponent.protect("")
END SUB
code to add sheet and del sheet

Code: Select all

sub delSheet
ThisComponent.getSheets().removeByName("source")
End Sub

Sub addSheet
	If ThisComponent.Sheets().hasByName("source") Then
		Else
		If ThisComponent.Sheets().getCount() > 255 Then
			Else
			ThisComponent.Sheets().insertNewByName("source", 6 )
		End If
	End If
End Sub
OpenOffice 4.1.1 ON WINDOWS 7 64
Top
Post Reply

Return to “Macros and UNO API”