[solved] Large copy from one doc to another doc
[solved] Large copy from one doc to another doc
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.
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]
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1.1 ON WINDOWS 7 64
Re: LARGE COPY FROM ONE DOC TO ANOTHER DOC
after doing the math each doc will have 37 copy operations that I do
the code I'm creating this with is
this is only the sample it doesn't have all the copy operations included im still writing the code for that
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
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
Re: LARGE COPY FROM ONE DOC TO ANOTHER DOC
There is an Insert Sheet macro at
viewtopic.php?t=20808
viewtopic.php?t=20808
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
Re: LARGE COPY FROM ONE DOC TO ANOTHER DOC
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
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
Re: LARGE COPY FROM ONE DOC TO ANOTHER DOC
See section 15.6.4 of Andrew Pitonyak's OpenOffice Macros Explained. It says only
Presumably the methods are discussed in the earlier chapter (14) on Writer.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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.5 LTS
- kingfisher
- Volunteer
- Posts: 2127
- Joined: Tue Nov 20, 2007 10:53 am
Re: Large copy from one doc to another doc
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
Re: Large copy from one doc to another doc
How many copy/paste operations can you do in one minute?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 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Large copy from one doc to another doc
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 macrosVilleroy wrote:How many copy/paste operations can you do in one minute?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 hours did you waste with this idiotic macro stuff?
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
Re: Large copy from one doc to another doc
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
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
Re: Large copy from one doc to another doc
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Large copy from one doc to another doc
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 only the parts that don't have formulas need to be copied
here is an example of the 2 docs I am merging to one only the parts that don't have formulas need to be copied
OpenOffice 4.1.1 ON WINDOWS 7 64
Re: Large copy from one doc to another doc
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- kingfisher
- Volunteer
- Posts: 2127
- Joined: Tue Nov 20, 2007 10:53 am
Re: Large copy from one doc to another doc
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.
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
Re: Large copy from one doc to another doc
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Large copy from one doc to another doc
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
before opning doc2
then later in the code I did
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)
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
Code: Select all
sURL = ConvertToUrl("C:\Users\main\Documents\destination.ots")
oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
Code: Select all
odoc1.tofront()
odoc1.setfocus()
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
- kingfisher
- Volunteer
- Posts: 2127
- Joined: Tue Nov 20, 2007 10:53 am
Re: Large copy from one doc to another doc
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
Re: Large copy from one doc to another doc
otdoc is the document currently in focus I don't want to close that onekingfisher 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 )
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
Re: Large copy from one doc to another doc
Code: Select all
oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Large copy from one doc to another doc
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 inVilleroy wrote:Go ahead. Write your program. I would do that on paper sheets rather than spreadsheets.
OpenOffice 4.1.1 ON WINDOWS 7 64
Re: Large copy from one doc to another doc
otdoc = destinationVilleroy wrote:oTDoc is exactly the document you have loaded right now. You can focus any other document, oTDoc remains oTDoc.Code: Select all
oTDoc = Stardesktop.loadComponentFromURL(sURL, "_blank", 0, NoArg())
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
Re: Large copy from one doc to another doc
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
Re: Large copy from one doc to another doc
WTF - do you really think we are the idiots which do the rewriting on 753 Lines of dump Basic
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 24.8… flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Large copy from one doc to another doc
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.
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
Re: Large copy from one doc to another doc
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
Re: Large copy from one doc to another doc
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.
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
Re: Large copy from one doc to another doc
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Large copy from one doc to another doc
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
Re: Large copy from one doc to another doc
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
Re: Large copy from one doc to another doc
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Large copy from one doc to another doc
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