[Solved] CopyRange Copy Cells to another Spreadsheet or file

Creating a macro - Writing a Script - Using the API

[Solved] CopyRange Copy Cells to another Spreadsheet or file

Postby tommy5725 » Sat Jul 21, 2012 11:07 am

Hello all,

I would like to create a macro to copy a cell range(one row, C56:J56) from my invoice template file: “C:\CalcWork\Invoice.ods” and paste it to the first empty row in column A of my invoice log file: “C:\CalcWork\InvoiceLog.ods”. It would need to be run from a button on sheet2 of “Invoice.ods” and pasting into sheet1 of “InvoiceLog.ods”.

I am doing this invoice for a family member and would like the process to be a simple button click for the usual reasons. Additionally, I realize that a database would probably be a better long term solution.

I know that my problem may seem trivial or dumb or “answered a thousand times” so I'll apologize up front. But I have spent hours trying to find this exact answer. I have read quite a bit of Mr Pitonyak's Useful Macro Information, and numerous posts from a very knowledgeable poster by the name of Villeroy however my implementation, or lack there of, seems to be the problem.

This is all very new to me and I don't know if I'm attacking it from the wrong angle/keyword, or maybe this is much more complicated than it would seem. I find very little on accessing the second file and doing the pasting part. I've tried CopyRange, but maybe it needs to be a GetDataArray SetDataArray type thing. It's a bit overwhelming when you've never tried this stuff before. In any event I'll post what I started working on below. If nothing else it should make for a good laugh.

Maybe there needs to be a thread on what you can't do. But if it can be done, or done in a different way I'd appreciate any solutions.

Thanks,
Tom


Code: Select all   Expand viewCollapse view
Sub ContractThisToSomeoneThatKnowsWhatTheyreDoing

Dim oDocA As Object
oDocA = ThisComponent 'Invoice Sheet Template file, C:\CalcWork\invoice.ods. Macro will someday run from here.
Dim oDocB As Object   'Invoice Sumary Row will be copyed to this Invoice Log Spreadsheet C:\CalcWork\invoicelog.ods.
Dim oSheetA As Object

oSheetA = thisComponent.getSheets.getByIndex( 1 )
oRange = oSheetA.getCellRangeByName( "C56:J56" )


oDocB = StartDesktop.loadComponentFromURL( ConvertToURL( "C:\CalcWork\invoicelog.ods" ), "_blank", _
        0, Array() )

cur = oSheet.createCursor
cur.gotoEndOfUsedArea(false)
cur.gotoOffset(0 - cur.rangeAddress.endColumn,1)'Column A, Row end of use area +1

'(AND PASTE Call function(AbraCadabra,Vwala))

End Sub
Last edited by Hagar Delest on Sun Jul 22, 2012 12:02 pm, edited 1 time in total.
Reason: tagged solved.
Tom
OpenOffice 3.4 on Windows 7
OpenOffice 3.1 on Ubuntu
tommy5725
 
Posts: 5
Joined: Fri Jul 20, 2012 8:25 am

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby Villeroy » Sat Jul 21, 2012 1:17 pm

Why do you try to make an invoicing system if you are not a programmer?
Why do you try to use a free-hand calculator to store structured data?
First you've got to learn programming. Then you may write something like this.
Indeed, this has been done a thousands time and the better solutions are not based on spreadsheets.

If it has to be self-made and if it is supposed to dump invoices into Calc, then it should be based on a database with a tiny little bit of supplementary macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26261
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby Keith Wild » Sat Jul 21, 2012 2:07 pm

Tom, I can't give you a full answer to your problem, but I hope the following may be helpful.

1. You state your problem and give your macro, but do not say what happens when you run the macro. Does it give an error message and if so what? Does it not give an error message, but do nothing at all? Does it not give an error message, but does something you don't want or expect?
2. I can't see a definition of oSheet. I was expecting to see a definition of an object oSheetB in terms of oDocB.
3. The following link may be useful for copying and pasting, although I don't think it does exactly what you want (viewtopic.php?f=20&t=53098)


Keith
AOO 4.1.3
macOS High Sierra version 10.13.1
Keith Wild
 
Posts: 52
Joined: Wed Aug 12, 2009 1:14 pm
Location: London, UK

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby patel » Sat Jul 21, 2012 3:25 pm

Code: Select all   Expand viewCollapse view
Sub rangecopy
Dim oDocA As Object, oDocB As Object, oSheetA As Object, Dummy(), oRangeA as object, targetcell as object
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDocA = ThisComponent
oFrameA = oDocA.CurrentController.Frame
oSheetA = oDocA.getSheets.getByIndex(0)
oRangeA = oSheetA.getCellRangeByName("C56:J56")
oDocA.CurrentController.Select(oRangeA)
oDispatcher.executeDispatch(oFrameA, ".uno:Copy", "", 0, Dummy())
oDocB = StarDesktop.loadComponentFromURL ("file:///D:/DATI/prova/invoicelog.ods", "_blank",0, Dummy() )
oSheetB = oDocB.getSheets.getByIndex(0)
c = oSheetB.createCursor
c.gotoEndOfUsedArea(false)
LastRow = c.RangeAddress.EndRow +1
targetcell= oSheetB.getCellByPosition(0,LastRow)
oDocB.CurrentController.Select(targetcell)
oFrameB = oDocB.CurrentController.Frame
oDispatcher.executeDispatch(oFrameB, ".uno:Paste", "", 0, Dummy())
End Sub
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
patel
 
Posts: 23
Joined: Tue Jun 19, 2012 2:48 pm

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby Villeroy » Sat Jul 21, 2012 11:53 pm

There is no reason why the input cells should be copied to the end of the list. Just insert everything on top of the list. Or insert anywhere with a time stamp, so you can generate any sort order.
Or use a database table with a database form without a single line of stupid Basic code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26261
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby kingfisher » Sun Jul 22, 2012 4:31 am

Villeroy knows a lot about this software and you would do well to heed his advice.

Perhaps you need a quick and dirty solution until you have time to put together a better system. If I was starting to use this software now I would probably learn Python but the consensus a few years ago seemed to be to use StarBasic.

I've cobbled this together from code I have been using for several years:

Code: Select all   Expand viewCollapse view
Sub exportRange
REM cobbled together and untested
Dim oExport, oSheet1, oSource, sUrl, oDataLog

REM Open source file if not already open
REM sExport needs to be declared Dim sExport
REM sExport = "file:///data/sets/reports/export.ods"
REM oExport = StarDesktop.loadComponentFromURL( sExport, "_blank", 0, Array() )

REM Assuming the source file is the active file:
oExport = ThisComponent : oSheet1 = oExport.Sheets.getbyIndex ( 0 ) 'first sheet
oSource = oSheet1.getCellRangeByName ( "C56:J56" )

REM Open data log (DataLog.ods), paste data at the end of the last sheet.
sUrl = "file:///data/sets/office/work/DataLog.ods"
oDataLog = StarDesktop.loadComponentFromURL( sUrl, "_blank", 0, Array() )

REM Find the row one below the end row of the data log.
Dim iNum, oSheet2, oCursor, tRange, lTarget, _
    oTarget

iNum = oDataLog.Sheets.getCount()
oSheet2 = oDataLog.Sheets.getByIndex( iNum-1 ) 'last sheet
oCursor = oSheet2.createCursor() : oCursor.gotoEndOfUsedArea( true )
tRange = oCursor.RangeAddress : lTarget = tRange.EndRow + 1
oTarget = getCellRangeByPosition ( 2, lTarget, 9, lTarget ) 'Cn:Jn
oTarget.setDataArray( oSource.getDataArray )


Obviously you will have to change the file names, paths and variable names.
Apache OpenOffice 4.1.2 (64 bit) on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2116
Joined: Tue Nov 20, 2007 10:53 am

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby tommy5725 » Sun Jul 22, 2012 10:37 am

Villeroy wrote:Why do you try to make an invoicing system if you are not a programmer?
Why do you try to use a free-hand calculator to store structured data?
First you've got to learn programming. Then you may write something like this.
Indeed, this has been done a thousands time and the better solutions are not based on spreadsheets.

If it has to be self-made and if it is supposed to dump invoices into Calc, then it should be based on a database with a tiny little bit of supplementary macro code.


“Why do you try to make an invoicing system if you are not a programmer?”

Very good question. The short answer is I haven't found a better solution. However with all the questions you answer around here I feel I must do better than that.

You see, I've worked with countless people that use QB(Quickbooks) and they have to change out their software constantly as each version gets “sunset”. And they tend to run into all sorts of issues every time. I'm looking for a more permanent solution, something that doesn’t change often, for multiple reasons. Also I would like it to end up running on the Ubuntu OS and have not found a good replacement for QB. What I'm doing is trying to learn a few things for myself while helping my older cousin, who is more of an uncle in all reality. He was a longtime dairy farmer who became a landscaper as the times have changed. He is one of the most giving, hardest working people I have ever known. However his billing/accounting methods are; well, just beyond post-its. His success is of the utmost importance to me as my mother was born in that farmhouse and I grew up there.

“Why do you try to use a free-hand calculator to store structured data?”

Same short answer as above. Long answer: Keeping in mind that I actually had to explain what “click on it” meant when I first sat him down on a computer, I need something that he will actually use. I would like the Invoice log file for two reasons, one as a back up that he never has to see or even know is there, and two something that I can use to easily total quarterly taxes and revenues. Also this will allow me to easily make some charts and reports. I also foolishly thought that this would be much easier than it has turned out to be(I'm sure you've heard that before ;) . Every time I make a macro to solve a problem it seems to create 2 more. However, I'm very close to a one click macro that will copy the invoice to a new file with the invoice number as a name for reference, then log the key information to the invoice log file, and then return to a clean invoice ready to start all over. This is a good start.

“First you've got to learn programming. Then you may write something like this.
Indeed, this has been done a thousands time and the better solutions are not based on spreadsheets.”

No I am not a programer, some times I wish I was. But I never was a webmaster, and that didn't stop me; a sound engineer and that didn't stop me, or even a dad until recently. And while doing this I got to thinking that there are probably a lot of small business out there who can't afford a dedicated secretary, who may also struggle with billing and invoicing. And if I could come up with a system to handle all the appointment setting and do the customer service and billing for 10-20 local small business, I might be able to drop the 9-5 and spend some more time with my son.

I know this is not the right solution for that scenario, but a temp fix for my cousin.

“There is no reason why the input cells should be copied to the end of the list. Just insert everything on top of the list.”

Your probably right about that but I thought it may save me some trouble down the line.

“Or insert anywhere with a time stamp, so you can generate any sort order.”

The invoice number is a timestamp and is included in the range. yymmddhhmmss

“Or use a database table with a database form without a single line of stupid Basic code.”

Now your speaking Greek to me, sorry.

That being said, I would like to ask again. What do you recommend? Learning a programing language? If so which? Should I look toward Base, or MS Access? Neither? Web-based is nice no crashes- Freshbooks, Xero? Should I try to hire someone to build a program? Or would you recommend something completely different? I am very open to suggestion at this point.

Thank you all for your thoughts and time, I am most appreciative to all the help you all have provided.

Tom
Tom
OpenOffice 3.4 on Windows 7
OpenOffice 3.1 on Ubuntu
tommy5725
 
Posts: 5
Joined: Fri Jul 20, 2012 8:25 am

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby tommy5725 » Sun Jul 22, 2012 10:47 am

patel wrote:
Code: Select all   Expand viewCollapse view
Sub rangecopy
Dim oDocA As Object, oDocB As Object, oSheetA As Object, Dummy(), oRangeA as object, targetcell as object
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDocA = ThisComponent
oFrameA = oDocA.CurrentController.Frame
oSheetA = oDocA.getSheets.getByIndex(0)
oRangeA = oSheetA.getCellRangeByName("C56:J56")
oDocA.CurrentController.Select(oRangeA)
oDispatcher.executeDispatch(oFrameA, ".uno:Copy", "", 0, Dummy())
oDocB = StarDesktop.loadComponentFromURL ("file:///D:/DATI/prova/invoicelog.ods", "_blank",0, Dummy() )
oSheetB = oDocB.getSheets.getByIndex(0)
c = oSheetB.createCursor
c.gotoEndOfUsedArea(false)
LastRow = c.RangeAddress.EndRow +1
targetcell= oSheetB.getCellByPosition(0,LastRow)
oDocB.CurrentController.Select(targetcell)
oFrameB = oDocB.CurrentController.Frame
oDispatcher.executeDispatch(oFrameB, ".uno:Paste", "", 0, Dummy())
End Sub


Wow, Perfect! I wish I could buy you a steak dinner!! I have spent weeks playing around with all sorts of code, to no avail. Then you come along with this and it does it on the first try. Do you know if there is there something i can add so it saves and closes so the user never sees the InvoiceLog file?

Many, many thanks,
Tom
Tom
OpenOffice 3.4 on Windows 7
OpenOffice 3.1 on Ubuntu
tommy5725
 
Posts: 5
Joined: Fri Jul 20, 2012 8:25 am

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby Villeroy » Sun Jul 22, 2012 12:46 pm

I have spent weeks playing around with all sorts of code, to no avail.

Totally inadequate approach towards programming.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26261
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby patel » Sun Jul 22, 2012 2:29 pm

tommy5725 wrote: I have spent weeks playing around with all sorts of code, to no avail. Then you come along with this and it does it on the first try.
You have to read documentation http://wiki.services.openoffice.org/wik ... ASIC_Guide and the bible http://www.pitonyak.org/oo.php

Do you know if there is there something i can add so it saves and closes so the user never sees the InvoiceLog file?
Tom

Code: Select all   Expand viewCollapse view
oDocB.Store
oDocB.close(true)
OpenOffice 4.1 on Windows 10
LibreOffice 5.2 on Windows 10
patel
 
Posts: 23
Joined: Tue Jun 19, 2012 2:48 pm

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby tommy5725 » Mon Jul 23, 2012 5:29 am

Villeroy wrote:
I have spent weeks playing around with all sorts of code, to no avail.

Totally inadequate approach towards programming.


Once again your undoubtedly right, however I unfortunatly am more of a hands on learner. Given this reply am i to take it that given my circumstances that you feel spending time learning OOO Basic will benefit me in future en-devours? Or is time better spent elsewhere?

Thanks again,
Tom
Tom
OpenOffice 3.4 on Windows 7
OpenOffice 3.1 on Ubuntu
tommy5725
 
Posts: 5
Joined: Fri Jul 20, 2012 8:25 am

Re: Macro CopyRange Copy Cells to another Spreadsheet or fil

Postby tommy5725 » Mon Jul 23, 2012 5:37 am

patel wrote:
tommy5725 wrote: I have spent weeks playing around with all sorts of code, to no avail. Then you come along with this and it does it on the first try.
You have to read documentation http://wiki.services.openoffice.org/wik ... ASIC_Guide and the bible http://www.pitonyak.org/oo.php

Do you know if there is there something i can add so it saves and closes so the user never sees the InvoiceLog file?
Tom

Code: Select all   Expand viewCollapse view
oDocB.Store
oDocB.close(true)


Good links, and a very heart felt thank you! I'm not good for much but If there's anything I could ever do to thank you let me know.

Best wishes,
Tom
Tom
OpenOffice 3.4 on Windows 7
OpenOffice 3.1 on Ubuntu
tommy5725
 
Posts: 5
Joined: Fri Jul 20, 2012 8:25 am

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby giorgoskn » Wed Jan 09, 2019 1:29 am

Hi there guys!

I decided to bring this thread back to life, because I have some remarks and some questions. I consider myself a VB and OO Basic amateur and I have found macros an interesting task to play with and learn, but not much more than that. I have already moved to databases for the real business stuff actually...

One fact I discovered, playing around with macros to copy cell content between sheets or docs, is that there are quite a lot different ways to do the same thing. Which of them should be used is only a matter of perspective or preference, or are there other parameters one should take into account?

After doing some R&D, trying to make a macro to copy NON Adjacent cells into the first available line of another sheet, this was what I managed to think of:

Code: Select all   Expand viewCollapse view
Sub Nextinvoice
Dim oDocA As Object, oSheetA As Object, oSheetB As Object, Dummy(), oCellA as Object, oCellB as Object, oCellC as Object, targetcell as object
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

oDocA = ThisComponent
oFrameA = oDocA.CurrentController.Frame
oSheetA = oDocA.getSheets.getByIndex(0)
oCellA = oSheetA.getCellRangeByName("F2")
oDocA.CurrentController.Select(oCellA)

oDispatcher.executeDispatch(oFrameA, ".uno:Copy", "", 0, Dummy())
oSheetB = oDocA.getSheets.getByIndex(2)
c = oSheetB.createCursor
c.gotoEndOfUsedArea(false)
LastRow = c.RangeAddress.EndRow +1
targetcell= oSheetB.getCellByPosition(0,LastRow)
oDocA.CurrentController.Select(targetcell)
oFrameB = oDocA.CurrentController.Frame
oDispatcher.executeDispatch(oFrameB, ".uno:Paste", "", 0, Dummy())


oDocA = ThisComponent
oFrameA = oDocA.CurrentController.Frame
oSheetA = oDocA.getSheets.getByIndex(0)
oCellB = oSheetA.getCellRangeByName("F3")
oDocA.CurrentController.Select(oCellB)

oDispatcher.executeDispatch(oFrameA, ".uno:Copy", "", 0, Dummy())
oSheetB = oDocA.getSheets.getByIndex(2)
c = oSheetB.createCursor
c.gotoEndOfUsedArea(false)
LastRow = c.RangeAddress.EndRow
targetcell= oSheetB.getCellByPosition(1,LastRow)
oDocA.CurrentController.Select(targetcell)
oFrameB = oDocA.CurrentController.Frame
oDispatcher.executeDispatch(oFrameB, ".uno:Paste", "", 0, Dummy())


oDocA = ThisComponent
oFrameA = oDocA.CurrentController.Frame
oSheetA = oDocA.getSheets.getByIndex(0)
oCellC = oSheetA.getCellRangeByName("B7")
oDocA.CurrentController.Select(oCellC)

oDispatcher.executeDispatch(oFrameA, ".uno:Copy", "", 0, Dummy())
oSheetB = oDocA.getSheets.getByIndex(2)
c = oSheetB.createCursor
c.gotoEndOfUsedArea(false)
LastRow = c.RangeAddress.EndRow
targetcell= oSheetB.getCellByPosition(2,LastRow)
oDocA.CurrentController.Select(targetcell)
oFrameB = oDocA.CurrentController.Frame
oDispatcher.executeDispatch(oFrameB, ".uno:Paste", "", 0, Dummy())


oCellD.clearContents(1)

oCell = oCellA
oCell.value = oCell.value + 1   
End Sub


As you can see, the only way I found was to start off from patel's code and do the process over for each separate cell. Trying to use Named Range or Cell Range resulted in pasting the cells following source cell positions, not in a single line, that wasn't what I was aiming for. **The last 4 lines are just for "refreshing" source sheet and get it ready for new input. Could someone please shed some light on these:

1) I am pretty sure my code includes unnecessary lines, due to recurrent processes, but couldn't find any. Whatever I removed resulted in malfunction. Are there actually unnecessary lines?
2) Why does patel use Dummy() for his dispatcher and not Args(), as in other similar lines? If answer is too complicated, never mind...
3) Using this macro, the pasted cells follow Normal Paste. Trying to use "oDispatcher.executeDispatch(oFrameB, ".uno:PasteSpecial", "", 0, Dummy())" instead, leeds to "Special Paste" dialog box appearing as soon as macro reaches this line. Clicking OK lets it finish normally, but I haven't found out how to automate the pasteSpecial call, so the box doesn't even pop up.
4) Since cell F3 is an auto-date cell (=TODAY()), this macro copies the whole cell and not only the date value. Resulting, of course, in a whole row changing values every day.... not very useful! :D
I guess the way to deal with that is the answer to 3), pasting only the value and not the formula. What if one would keep Normal Paste in his dispatcher call and then DELETE formula (+whatever else) in the pasted cells? Could that work also?

I really honestly apologize for the length and the noob questions!
OpenOffice 4.1 on Windows10
giorgoskn
 
Posts: 5
Joined: Wed Jan 09, 2019 12:36 am

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby Villeroy » Wed Jan 09, 2019 11:29 am

A macro does not need to select anything in the document.
Avoid dispatches.
This macro destroys any spreadsheet functionality (if there is any at all) since it disregards references.
Don't use any macros at all before you are really proficient with spreadsheets. Spreadsheets help non-programmers to handle data in more visual ways. Doing the most trivial spreadsheet stuff by means of macro code is counterproductive. Any program written in a script language can do this kind of list keeping stuff by far more efficiently with 5 lines of code without any office suite being installed.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26261
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby giorgoskn » Sat Jan 12, 2019 1:59 am

Thank you for your answer Villeroy! I really apreciate your deep knowledge and your advice on so many things

Villeroy wrote:... This macro destroys any spreadsheet functionality (if there is any at all) since it disregards references....


I didn't get what you mean there. Spreadsheet functionality isn't all about entering and sorting data and doing calculations with them? How does a macro (aiming at selecting some of these data and arranging them differently) destroy that?

Villeroy wrote:... Any program written in a script language can do this kind of list keeping stuff by far more efficiently with 5 lines of code without any office suite being installed.....

As I already wrote, I don't use this spreadsheet for any productivity purpose, but I would like to be able to propose something to any friend of mine, who would like a rather simple option to do a rather simple accounting routine. If he wants to be really professional, I totally agree with you, there are other good options. But if not? Could you suggest something?
OpenOffice 4.1 on Windows10
giorgoskn
 
Posts: 5
Joined: Wed Jan 09, 2019 12:36 am

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby mikele » Sat Jan 12, 2019 3:56 pm

Hello,
what do you want to do? As far as I understand the macro, do you want to copy the contents (not the cells themselve) of cell F2, F3 and B7 from Sheet1 to Sheet3 (in first empty row) and afterwards increment F2 by 1. One cell (which one?) should be cleared. Correct?
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 33
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby Villeroy » Sat Jan 12, 2019 7:42 pm

giorgoskn wrote:Thank you for your answer Villeroy! I really apreciate your deep knowledge and your advice on so many things

Villeroy wrote:... This macro destroys any spreadsheet functionality (if there is any at all) since it disregards references....


I didn't get what you mean there. Spreadsheet functionality isn't all about entering and sorting data and doing calculations with them? How does a macro (aiming at selecting some of these data and arranging them differently) destroy that?


Enter any formulas, named ranges, charts, validation, conditional formatting, pivot tables, whatever to your sheet. Then run the macro which appends some data somehow. All the formulas, names and charts will not include the new data. You will have to adjust all references which may be a lot more work than doing the job of data insertion manually and properly
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26261
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby giorgoskn » Sun Jan 13, 2019 7:07 pm

mikele wrote:Hello,
what do you want to do? As far as I understand the macro, do you want to copy the contents (not the cells themselve) of cell F2, F3 and B7 from Sheet1 to Sheet3 (in first empty row) and afterwards increment F2 by 1. One cell (which one?) should be cleared. Correct?


Yes mikele, exactly that. CellD that made you wonder is wrong, it's cell B7 (CellC) actually - I just copied the older txt file by mistake.

What I still haven't figured out is how to copy only values and not all cell contents (including formula and formatting). Using Uno:PasteSpecial instead, brings up the Paste Special dialog box, which is OK, but needs 1 additional click for every pasted cell. Is there a way to avoid that?
OpenOffice 4.1 on Windows10
giorgoskn
 
Posts: 5
Joined: Wed Jan 09, 2019 12:36 am

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby Villeroy » Sun Jan 13, 2019 7:28 pm

This macro has been written a hundred times in dozends of variations: viewtopic.php?f=21&t=93099
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26261
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby mikele » Sun Jan 13, 2019 7:47 pm

Hi,
that is easy done. To understand some basics: here is a simple solution (it's not the most efficient code ...)
Code: Select all   Expand viewCollapse view
Sub copy_some_values

   'access to document and sheets
   odoc=ThisComponent
   otab1=odoc.sheets(0)
   otab2=odoc.sheets(2)
   'get the values
   val1=otab1.getCellRangeByName("F2").Value
   val2=otab1.getCellRangeByName("F3").Value
   val3=otab1.getCellRangeByName("B7").Value
   'get destination
   ocur = otab2.createCursor
   ocur.gotoEndOfUsedArea(false)
   iLastRow = ocur.RangeAddress.EndRow
   'set values
   otab2.getCellByPosition(0,iLastRow+1).value=val1
   otab2.getCellByPosition(1,iLastRow+1).value=val2
   otab2.getCellByPosition(2,iLastRow+1).value=val3
   'final work
   otab1.getCellRangeByName("F2").Value=val1+1
   otab1.getCellRangeByName("B7").clearContents(1)
   
End Sub
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 33
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby giorgoskn » Sun Jan 13, 2019 10:06 pm

mikele wrote:Hi,
that is easy done. To understand some basics: here is a simple solution (it's not the most efficient code ...)
Code: Select all   Expand viewCollapse view
Sub copy_some_values
   .....
End Sub


I suspected there were too many lines of code in my macro for this simple task, but you nailed it man!! Perfect!
*Just 1 small detail: For B7 I needed it to copy text, not value, so I will change it a little
OpenOffice 4.1 on Windows10
giorgoskn
 
Posts: 5
Joined: Wed Jan 09, 2019 12:36 am

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby mikele » Sun Jan 13, 2019 10:14 pm

LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 33
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby giorgoskn » Sun Jan 13, 2019 10:42 pm

Yeap! I already fixed it! Thanks a lot man
OpenOffice 4.1 on Windows10
giorgoskn
 
Posts: 5
Joined: Wed Jan 09, 2019 12:36 am

Re: [Solved] CopyRange Copy Cells to another Spreadsheet or

Postby Villeroy » Mon Jan 14, 2019 11:33 pm

@ the casual reader: I've updated my ready-to use macro and wrapped it into an example document. viewtopic.php?f=21&t=93099 (copy data from sheets in same document). And there is also the other variant viewtopic.php?f=21&t=77069 which copies a single sheet from every spreadsheet or csv file in a specific folder into a master file derived from the attached template.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26261
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests