[solved] Large copy from one doc to another doc

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

[solved] Large copy from one doc to another doc

Post by MATT.B »

I have a large task ahead of me and not sure where to get started or how I should go about doing this
I created a number of macros for a doc I use every day to save me some time but I have 2 version of this doc and I want to convert the old to the new
my issue is the old is one is 1 sheet and the new one is 6 sheets no I have started with a template for copying from one doc to another my issue is im still learning the code, and the only way I know how to do this is open the source copy a range to the clipboard open the destination and paste, but I don't know how to switch back to the source after pasting. so I came up with a workaround, what if I insert the source as a sheet in the destination and do my copy operations and then delete the source sheet, issue with that is I cant figure out how to insert a sheet from file using a macro code. I do about 5 of these each day and there are 12-25 copy and paste operations that I must do for each source document.
Last edited by RoryOF on Tue Nov 03, 2015 6:46 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

after doing the math each doc will have 37 copy operations that I do
the code I'm creating this with is

Code: Select all

sub copy
Dim oSourceDoc, oSourceSheet, oSourceRange
Dim oTargetDoc, oTargetSheet, oTargetCell
Dim oDisp, octl 
Dim sUrl As String
Dim NoArg()
oSourceDoc=ThisComponent
octl = oSourcedoc.getCurrentController()
oSourceframe = octl.getFrame()
oSourceSheet= oSourceDoc.Sheets(0)
oSourceRange = oSourceSheet.getCellRangeByPosition(1,14,19,39)
oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
octl.Select(oSourceRange)
oDisp.executeDispatch(octl, ".uno:Copy", "", 0, NoArg()) 
sURL = "private:factory/scalc"
oTargetDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
oTargetSheet = oTargetDoc.getSheets.getByIndex(0)
oTargetCell = oTargetSheet.getCellByPosition(0,0)
oTargetDoc.getCurrentController().Select(oTargetCell)
oTargetframe = oTargetDoc.getCurrentController().getFrame()
oDisp.executeDispatch(oTargetFrame, ".uno:Paste", "", 0, NoArg())
End Sub
this is only the sample it doesn't have all the copy operations included im still writing the code for that
Last edited by MATT.B on Wed Oct 14, 2015 3:36 pm, edited 1 time in total.
OpenOffice 4.1.1 ON WINDOWS 7 64
User avatar
RoryOF
Moderator
Posts: 35068
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: LARGE COPY FROM ONE DOC TO ANOTHER DOC

Post by RoryOF »

There is an Insert Sheet macro at
viewtopic.php?t=20808
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
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 »

thank you that helped a lot, may be you cant help with one more thing, do you know how to unprotect the document? I know the code for sheet ThisComponent.getSheets().getByIndex(0).unprotect("pass")
but I don't know how to do the document and the macro recorder doesn't seem to give a working code for this operation
OpenOffice 4.1.1 ON WINDOWS 7 64
User avatar
RoryOF
Moderator
Posts: 35068
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: LARGE COPY FROM ONE DOC TO ANOTHER DOC

Post by RoryOF »

See section 15.6.4 of Andrew Pitonyak's OpenOffice Macros Explained. It says only
Calc documents and individual spreadsheets support the XProtectable interface. Use the methods protect(password) and unprotect(password) to activate or disable protection. The password is passed in as a String. The isProtected() method returns True if protection is currently active.
Presumably the methods are discussed in the earlier chapter (14) on Writer.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
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 »

To protect a spreadsheet document (which I believe you refer to) :

Code: Select all

ThisComponent.protect("apassword")
Last edited by kingfisher on Thu Oct 15, 2015 8:51 am, edited 2 times in total.
Apache OpenOffice 4.1.12 on Linux
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 »

MATT.B wrote:after doing the math each doc will have 37 copy operations that I do
the code I'm creating this with is
[... lot of rubbish ...]
How many copy/paste operations can you do in one minute?
How many hours did you waste with this idiotic macro stuff?
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
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:
MATT.B wrote:after doing the math each doc will have 37 copy operations that I do
the code I'm creating this with is
[... lot of rubbish ...]
How many copy/paste operations can you do in one minute?
How many hours did you waste with this idiotic macro stuff?
actually not many I opened the doc in excel and it wrote most of the code for me I just have to change the prefixes to work for Oo but on this doc I would say I have spent a good 20-40 hours but that's time from creating the initial doc. writing the formulas, and then now the macros but its not time wasted its time enjoyed I learned some making this and have saved my self average 5 hours a day in work I think its time well spent, and so far only about 10 hours of that time is actually on the macros
the rest was creating the document formatting it to look good and then creating the formulas to add my totals
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

and its no rubbish
B15-B19/B21-R32/F40-R40/F45-R45/F44-R44/V44-v46
now do that for 6 pages
let do the math that's 6 * 6 +1 that's 37 the code will have 37 copy operation not every doc is 6 pages some are only 3
I import the data into a database, tracking how much people have spent my main doc is set up for easy import of the data but I have a person that refuses to use the new doc creating more work for me meaning I have to sift through 6 pages of data to see what I need to copy. what I'm doing it creating a macro that will turn the old doc to the new doc when its opened so my already written code that imports that data into the database will work. the old doc is 6 pages on one sheet the new doc seperates each page to a sheet making all the formulas that same and the code was easy to write just change the sheet number and repeat
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

You can select multiple sheets and paste into all sheets in one go.
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
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 »

I know that but I'm copying from 1 sheet to multiple sheets and I cant do large sections at once because it breaks the formulas im not expecting any one to do my work for me just needed help with a portion of the work and I was told by other posters its better to give the hole problem not just the small section that I need help with

here is an example of the 2 docs I am merging to one
destination.ods
(57.32 KiB) Downloaded 343 times
SOURCE.ODS
(32.32 KiB) Downloaded 357 times
only the parts that don't have formulas need to be copied
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

Go ahead. Write your program. I would do that on paper sheets rather than spreadsheets.
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
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 have been unable to find any simple way of inserting a sheet from another file.

This forum topic contains some references to StarBasic which may help you. Also look at this web page. I also suggest you add the extension MRI.
Apache OpenOffice 4.1.12 on Linux
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 »

Function getImportedSheet can do the tricky job in various ways.
In the test routine I specified the file path to my spreadsheet document, a sheet name in that document, the name and position of the new sheet to be inserted. Helper function getUniqueName gets a non-existing name for any new sheet to be inserted.
Depending on argument nMode it imports sheets with values only or with values and formulas.
Depending on argument bUnlink it keeps the link to the source sheet or breaks the link.
 Edit: Added a tiny detail that makes it possible to refresh an existing sheet link. Simply call the function with the name of an existing sheet. 

Code: Select all

REM  *****  BASIC  *****

Sub test_ImportSheet()

const cPathName = "/home/andreas/Dokumente/AOOForumAttachments.ods"
const cSourceSheet = "Sheet2"
const cNewSheetPrefix = "Import_"

nMode = com.sun.star.sheet.SheetLinkMode.NORMAL ' with formulas
' nMode = com.sun.star.sheet.SheetLinkMode.VALUE ' values only
sURL = convertToURL(cPathName)
sName = getUniqueName(ThisComponent.getSheets(), cNewSheetPrefix, 3)

oSheet = getImportedSheet( _
	oDoc:=ThisComponent, _
	sURL:=sURL, _
	sSheetName:=cSourceSheet, _
	sNewSheet:=sName, _
	nPos:=0, _
	nMode:=nMode, _
	bUnlink:=True _
)
' GlobalScope.BasicLibraries.loadLibrary("MRILib")
' mri oSheet
End Sub

Function getImportedSheet(oDoc, sURL$, sSheetName$, sNewSheet$, nPos%, nMode%, bUnlink AS Boolean)
	oSheets = oDoc.getSheets()
REM any existing sheet link will be refreshed
	if not oSheets.hasByName(sNewSheet) then
		oSheets.insertNewByName(sNewSheet, nPos)
	endif
	oSheet = oSheets.getByName(sNewSheet)
	oSheet.setLinkURL(sURL)
	oSheet.setLinkSheetName(sSheetName)
	oSheet.setLinkMode(nMode)
	if bUnlink then oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
getImportedSheet = oSheet
End Function

Function getUniqueName(oContainer, sName, Optional nDigits)
REM get a name for a new object sName01, sName02, sName03,...
if isMissing(nDigits) then nDigits = 3
Dim i%,sNew$
sNew = sName
Do 
	i = i +1
	sNew = sName & format(i, String(nDigits,"0")
loop while oContainer.hasByName(sNew)
getUniqueName = sNew
End Function
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
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 »

thank you every one for all the help I have my code written but I have one issue that I haven't been able to figure out
and that's closing the source document after the copy has completed
I couldn't figure out how to switch the active document/window so I copied the source opened the destination, created a new sheet and pasted to it then did all the copy operations and then deleted the created sheet now I need to switch back to the original document I tried a

Code: Select all

odoc1 = ThisComponent.CurrentController.Frame.ContainerWindow
before opning doc2

Code: Select all

sURL = ConvertToUrl("C:\Users\main\Documents\destination.ots")
oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
then later in the code I did

Code: Select all

odoc1.tofront()
odoc1.setfocus()
but it doesn't do any thing
does any one know how I can bring source.otd back to front so its the active window again?
this way I can do
thisComponent.colse(true)
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

This is what I have used in code for some years. oTDoc is the variable name of your document. The document does not have to be in focus to close. Your code defines the document variable when it is 'loaded'.

Code: Select all

oTDoc.dispose()
oTDoc.close( true )
Apache OpenOffice 4.1.12 on Linux
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 »

kingfisher wrote:This is what I have used in code for some years. oTDoc is the variable name of your document. The document does not have to be in focus to close. Your code defines the document variable when it is 'loaded'.

Code: Select all

oTDoc.dispose()
oTDoc.close( true )
otdoc is the document currently in focus I don't want to close that one
I want to close the doc I opened through windows explorer odoc1
and you shouldn't use dispose any more it can cause oo to crash resulting in data loss
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

Code: Select all

oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
oTDoc is exactly the document you have loaded right now. You can focus any other document, oTDoc remains oTDoc.
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
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:Go ahead. Write your program. I would do that on paper sheets rather than spreadsheets.
we did it all on paper now we are going computerized it works out we always have a copy of it now and now when the sheet is request via email I don't have to scan it I can just click 2 buttons and the email open up with the file attached and all I have to do it type the email address in
OpenOffice 4.1.1 ON WINDOWS 7 64
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:

Code: Select all

oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
oTDoc is exactly the document you have loaded right now. You can focus any other document, oTDoc remains oTDoc.
otdoc = destination
odoc1 = source

I know i'm saying I don't want to close otdoc I want to send it to background or bring odoc1 to the front I tried
odoc1.tofront()
odoc1.setfocus()
but this seems to do nothing
I tried odoc1.close(true)
but it just closes the document in view
or does nothing
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

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 Replace(Source As String, Search As String, NewPart As String)
  Dim Result As String  
  Result = join(split(Source, Search), NewPart)
  Replace = Result
End Function
'oSheet.getCellRangeByName("A11")
'oView.Select(oCell)

SUB STARTUP
On Error GoTo UNKNOWNERROR
Dim check as string
dim tcheck as string
dim document as object
dim dispatcher as object
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSheet = odoc.getSheets().getByIndex(0)
document = oView.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
if fnWhichComponent(thisComponent) <> "Spreadsheet" then
	exit sub
	end if
wait 600

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
'PRINT oCheck
if oCheck = "TOTAL ESTIMATE PRICE" then 
	SETUPDOC
	END IF
If check = 5615 then
	ret& = Msgbox ("Please make sure to double check your work. Thank you", 10 + 1, "Reminder")
	dim args14(2) as new com.sun.star.beans.PropertyValue
	args14(0).Name = "Zoom.Value"
	args14(0).Value = 100
	args14(1).Name = "Zoom.ValueSet"
	args14(1).Value = 28703
	args14(2).Name = "Zoom.Type"
	args14(2).Value = 0
	dispatcher.executeDispatch(document, ".uno:Zoom", "", 0, args14())
	oCell = oSheet.getCellRangeByName("J1")
	oView.select(oCell)
	else
	if tcheck = "TOTAL ESTIMATE PRICE" then
		'ret& = Msgbox ("ESTIMATE PAGE DETECTED", 10 + 1, "INFORMATION")
		dim args15(2) as new com.sun.star.beans.PropertyValue
		args15(0).Name = "Zoom.Value"
		args15(0).Value = 100
		args15(1).Name = "Zoom.ValueSet"
		args15(1).Value = 28703
		args15(2).Name = "Zoom.Type"
		args15(2).Value = 0
		dispatcher.executeDispatch(document, ".uno:Zoom", "", 0, args15())
		oCell = oSheet.getCellRangeByName("J1")
		oView.select(oCell)
		PREP
	end if

end if
EXIT SUB
UNKNOWNERROR:
   ret& = Msgbox ("Please make sure to double check your work. Thank you", 10 + 1, "Error")
   Exit Sub
END SUB
	



Sub SaveAs1 
'On Error GoTo UNKNOWNERROR
if fnWhichComponent(thisComponent) <> "Spreadsheet" then
  exit sub
end if
Dim Check as string
Dim tCheck as string
dim Document as object
dim Dispatcher as object
Dim MyFilename as string
dim MyPath as string
Dim Url as string
dim Url1 as string
DIM PAGE AS STRING
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSheet = odoc.getSheets().getByIndex(0)
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
document = ThisComponent.CurrentController.Frame
oCell = oSheet.getCellRangeByName("ZZ2")
CHECK = oCell.getstring
oCell = oSheet.getCellRangeByName("H11")
tcheck = oCell.getstring
	If check = 5615 then
		oCell = oSheet.getCellRangeByName("U2")
		myFilename = oCell.getstring
		myfilename = Replace(myfilename, "/", " ")
		myfilename = replace(myfilename, "\", " ")
		oCell = oSheet.getCellRangeByName("C15")
		Cname = oCell.getstring
		Cname = replace(Cname, "/", " ")
		Cname = replace(Cname, "\", " ")
		ocell.setstring(Cname)	
			IF MyFilename = "  " and tCheck = "TOTAL ESTIMATE PRICE" then
					dim args16(2) as new com.sun.star.beans.PropertyValue
					args16(0).Name = "Zoom.Value"
					args16(0).Value = 100
					args16(1).Name = "Zoom.ValueSet"
					args16(1).Value = 28703
					args16(2).Name = "Zoom.Type"
					args16(2).Value = 0
					dispatcher.executeDispatch(document, ".uno:Zoom", "", 0, args16())
					oCell = oSheet.getCellRangeByName("J1")
					oView.select(oCell)
					PREP
						EXIT SUB
			END IF
		oCell = oSheet.getCellRangeByName("U4")
		MyPath = "C:\Users\main\Documents\EST PDF\"
		'MyPath = oCell.getstring
		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")
		'PRINT MYPATH
		'PRINT MYFILENAME
		'PRINT URL
		oDoc.StoreAsUrl(url,Array())
		dim args12(1) as new com.sun.star.beans.PropertyValue
		dim Arg(0) as new com.sun.star.beans.PropertyValue
		oCell = oSheet.getCellRangeByName("W47")
		page = oCell.getstring
			IF PAGE = 1 THEN
				Arg(0).Name = "PageRange"
				Arg(0).Value = "1"
				args12(1).Name = "FilterData"
				args12(1).Value = Arg()
				goto pdf
			END IF
			IF PAGE = 2 THEN
				Arg(0).Name = "PageRange"
				Arg(0).Value = "1,2"
				args12(1).Name = "FilterData"
				args12(1).Value = Arg()
				goto pdf
			END IF
			IF PAGE = 3 THEN
				Arg(0).Name = "PageRange"
				Arg(0).Value = "1,2,3"
				args12(1).Name = "FilterData"
				args12(1).Value = Arg()
				goto pdf	
			END IF
			IF PAGE = 4 THEN
				Arg(0).Name = "PageRange"
				Arg(0).Value = "1,2,3,4"
				args12(1).Name = "FilterData"
				args12(1).Value = Arg()
				goto pdf
			END IF
			IF PAGE = 5 THEN
				Arg(0).Name = "PageRange"
				Arg(0).Value = "1,2,3,4,5"
				args12(1).Name = "FilterData"
				args12(1).Value = Arg()
				goto pdf	
			END IF			
			IF PAGE = 6 THEN
				Arg(0).Name = "PageRange"
				Arg(0).Value = "1,2,3,4,5,6"
				args12(1).Name = "FilterData"
				args12(1).Value = Arg()
				goto pdf
			END IF
		PDF:	
		args12(0).Name = "FilterName"
		args12(0).Value = "calc_pdf_Export"
		oDoc.storeToURL(url1,args12())
		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






sub pageprint
On Error GoTo UNKNOWNERROR
dim document as object
dim dispatcher as object
Dim oSheet : oSheet = oDoc.Sheets.getByIndex(0)
Dim oReturn : oReturn = oDoc.Sheets.getByIndex(0)
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

sub PREP
dim document as object
dim dispatcher as object
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDoc = ThisComponent
oView = oDoc.getCurrentController()
document = 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

'dim args10(0) as new com.sun.star.beans.PropertyValue
'args10(0).Name = "ToPoint"
'args10(0).Value = "$H$11"
'dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())

oSheet = odoc.getSheets().getByIndex(0)
ocell = oSheet.getCellRangeByName("H11")
oView.Select(oCell)
'saveas1 
'odoc.close(true)
end sub







SUB COPY
DIM CHECK, C, CELL, OCELL, OCHECK
Dim oDoc, oSheet, NoArg()
DIM oSRangeA1, oSRangeA2, oSRangeA3, oSRangeA4, oSRangeA5, oSRangeA6, oSRangeB1, oSRangeB2, oSRangeB3, oSRangeB4, oSRangeB5, oSRangeB6, oSRangeC1, oSRangeC2, oSRangeC3, oSRangeC4, oSRangeC5, oSRangeC6, oSRangeD1, oSRangeD2, oSRangeD3, oSRangeD4, oSRangeD5, oSRangeD6, oSRangeE1, oSRangeE2, oSRangeE3, oSRangeE4, oSRangeE5, oSRangeE6, oSRangeF1, oSRangeF2, oSRangeF3, oSRangeF4, oSRangeF5, oSRangeF6
Dim oCell1, oCell2, oCell3, oCell4, oCell5, oCell6
Dim oTsheet1, oTsheet2, oTsheet3, oTsheet4, oTsheet5, oTsheet6, oSsheet
Dim Dispatcher, octl, oFrame
Dim oSourceDoc, oSourceSheet, oSourceRange
Dim oTargetDoc, oTargetSheet, oTargetCell
Dim sUrl As String
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oFrame = oView.getFrame()
oTSheet1 = odoc.getSheets().getByIndex(0)
oTSheet2 = odoc.getSheets().getByIndex(1)
oTSheet3 = odoc.getSheets().getByIndex(2)
oTSheet4 = odoc.getSheets().getByIndex(3)
oTSheet5 = odoc.getSheets().getByIndex(4)
oTSheet6 = odoc.getSheets().getByIndex(5)
oSSheet = odoc.getSheets().getByIndex(6)
oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
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")
oView.Select(oSRangeA1)
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg()) 'copy
oView.Select(oTCellA1) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeA2) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellA2) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeA3) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellA3) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeA3) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellA3) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeA5) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellA5) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeA5)
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellA5) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeB1) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellB1) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeB2) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellB2) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeB3) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellB3) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeB4) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellB4) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeB5) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellB5) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeC1) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellC1) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeC2) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellC2) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeC3) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellC3) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeC4) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellC4) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeC5) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellC5) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeD1) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellD1) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeD2) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellD2) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeD3) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellD3) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeD4) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellD4) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeD5) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellD5) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeE1) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellE1) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeE2) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellE2) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeE3) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellE3) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeE4) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellE4) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeE5) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellE5) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeF1) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellF1) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeF2) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellF2) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeF3) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellF3) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeF4) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellF4) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
oView.Select(oSRangeF5) 
oDisp.executeDispatch(oView, ".uno:Copy", "", 0, NoArg())
oView.Select(oTCellF5) 
oDisp.executeDispatch(oFrame, ".uno:Paste", "", 0, NoArg())
dim args100(0) as new com.sun.star.beans.PropertyValue
args100(0).Name = "Flags"
args100(0).Value = "SVDFN"
oView.Select(oSRangeA1)
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100()) 'copy
oView.Select(oSRangeA2) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeA3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeA3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeA5) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeA5)
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeB1) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeB2) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeB3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeB4) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeB5) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeC1) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeC2) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeC3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeC4) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeC5) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeD1) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeD2) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeD3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeD4) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeD5) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeE1) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeE2) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeE3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeE4) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeE5) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeF1) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeF2) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeF3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeF4) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(oSRangeF5) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oCell = OSSHEET.getCellRangeByName("H13")
check = oCell.getstring
ocell = ThisComponent.getSheets.getByIndex(0).getCellRangeByName("A11")
ThisComponent.getCurrentController().Select(oCell)
END SUB


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("password")
ThisComponent.getSheets().getByIndex(0).unProtect("")
DIM DOC1, DOC2
Dim oDoc, oSheet, oRange
Dim oTDoc, oTSheet, oTCell
Dim oDisp, octl 
Dim sUrl As String
Dim NoArg(),odel1,odel2,odel3
Dim oTsheet1, oTsheet2, oTsheet3, oTsheet4, oTsheet5, oTsheet6, oSsheet
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oFrame = oView.getFrame()
oDoc=ThisComponent
octl = odoc.getCurrentController()
oframe = octl.getFrame()
OSHEET = ODOC.getSheets.getByIndex(0)
oSheets= oDoc.Sheets(0)
oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
dim args100(0) as new com.sun.star.beans.PropertyValue
args100(0).Name = "Flags"
args100(0).Value = "SVDFN"
oDEL1 = oSheet.getCellRangeByName("ZZ1")
oDEL2 = oSheet.getCellRangeByName("ZZ2")
oDEL3 = oSheet.getCellRangeByName("ZZ3")
oView.Select(ODEL1)
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100()) 'copy
oView.Select(ODEL2) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oView.Select(ODEL3) 
oDisp.executeDispatch(oView, ".uno:Delete", "", 0, args100())
oRange = oSheets.getCellRangeByPosition(0,0,21,300)
oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
octl.Select(oRange)
oDisp.executeDispatch(octl, ".uno:Copy", "", 0, NoArg()) 
sURL = ConvertToUrl("C:\Users\main\Documents\ESTIMATE.ots")
doc1 = ThisComponent.CurrentController.Frame.ContainerWindow
oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
addsheet
oTSheet = oTDoc.getSheets.getByIndex(6)
oTCell = oTSheet.getCellByPosition(0,0)
doc2 = ThisComponent.CurrentController.Frame.ContainerWindow
oTDoc.getCurrentController().Select(oTCell)
oTframe = oTDoc.getCurrentController().getFrame()
oDisp.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")
COPY
DELSHEET
PROTECTALL
PROTECT
PREP
SAVEAS1
ocell = ThisComponent.getSheets.getByIndex(0).getCellRangeByName("A11")
ThisComponent.getCurrentController().Select(oCell)
doc1.tofront()
doc1.setfocus()
DOC1.CLOSE
'DOC2.TOFRONT()
'DOC2.SETFOCUS()
End Sub


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

Sub addSheet
If ThisComponent.Sheets().hasByName("source") Then
   'Msgbox "A sheet named: """ & sSheetName & """ already exists!"
Else
   If ThisComponent.Sheets().getCount() > 255 Then
      'Msgbox "Maximum number of sheets reached - cannot insert!"
   Else
      ' insert as index 0 = first sheet in file
      ThisComponent.Sheets().insertNewByName("source", 6 )
   End If
End If
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

SUB UNPROTECTALL
ON ERROR RESUME NEXT
ThisComponent.unprotect("")
ThisComponent.getSheets().getByIndex(0).unprotect("password")
ThisComponent.getSheets().getByIndex(0).unprotect("")
ThisComponent.getSheets().getByIndex(1).unprotect("password")
ThisComponent.getSheets().getByIndex(1).unprotect("")
ThisComponent.getSheets().getByIndex(2).unprotect("password")
ThisComponent.getSheets().getByIndex(2).unprotect("")
ThisComponent.getSheets().getByIndex(3).unprotect("password")
ThisComponent.getSheets().getByIndex(3).unprotect("")
ThisComponent.getSheets().getByIndex(4).unprotect("password")
ThisComponent.getSheets().getByIndex(4).unprotect("")
ThisComponent.getSheets().getByIndex(5).unprotect("password")
ThisComponent.getSheets().getByIndex(5).unprotect("")
end sub
OpenOffice 4.1.1 ON WINDOWS 7 64
User avatar
karolus
Volunteer
Posts: 1226
Joined: Sat Jul 02, 2011 9:47 am

Re: Large copy from one doc to another doc

Post by karolus »

WTF - do you really think we are the idiots which do the rewriting on 753 Lines of dump Basic
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 24.8… flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
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 »

who said any thing about rewriting?
and who said you were an idiot?
yes its messy and yes it needs to be rewritten but its unfinished, that and this is my first macro write with no basic experience before now its a learning progress.
i'm just trying to get from one open window to another open window I didn't say here is my code rewrite it please. I wrote it in sections and need to clean a lot of it up.
but till I figure out how to do the last step I'm holding off on rewriting it.
right now I need to change from the loaded document to the original document ignore the rest of the code I posted that for a friend that's going to help me clean it up and make it run faster.
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

now I get it your saying its my code that's causing this not to work ok
OpenOffice 4.1.1 ON WINDOWS 7 64
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, kingfisher, and RoryOF thank you for all the help you have supplied on this task.
I have some major rewrites to do on my code but you have helped me complete my task and you have thought me many things that I wasn't getting out of my book.
I think I need to invest in a better book. any suggestions are welcome.
OpenOffice 4.1.1 ON WINDOWS 7 64
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 »

The language is simply Basic and any Basic book will help with correct syntax and some Basic functions. But the language is not the complicated thing. The complicated thing is the thing you talk to.
Even a very experienced programmer with perfect skills in Basic language would have a hard time to get his head around the availlable services and interfaces of this office suite.
Knowing how to write correct Basic syntax, this is the best introduction in the complex thing: http://www.pitonyak.org/oo.php
It answers your question in chapter 5.41
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
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 »

yeah i use that often as a reference i think somewhere in my code im redefining my variables and its causing some things not to work as expected
OpenOffice 4.1.1 ON WINDOWS 7 64
User avatar
RoryOF
Moderator
Posts: 35068
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Large copy from one doc to another doc

Post by RoryOF »

One of the (crude?) debugging tricks is to insert a series of Print statements (or equivalent) which keep track of the variable(s) of interest. So they slow down the execution, but they let you see what is happening.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
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 »

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.
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
User avatar
RoryOF
Moderator
Posts: 35068
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Large copy from one doc to another doc

Post by RoryOF »

I often go for "quick and dirty", Villeroy :-) Probably a hangover from my Fortran II days.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
Post Reply