OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Macro to copy from one spreadsheet to another
Goto page Previous  1, 2, 3  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 25, 2006 3:29 pm    Post subject: Reply with quote

Your first posting: wrote:
I want to set up a macro in spreadsheet A that will pick a few values out of spreadsheet B, and paste them into specific cells in spreadsheet A.

Instead of letting spreadsheet A pick data from one of the Bs, my code pushes data from any spreadsheet B into spreadsheet A. A does not have any code, at least no code for this task. This requires a template for all your B-docs to come. This template has the code, required named ranges and some thingy to call the code, so each document derived from that template will be able to push it's data into your A-doc if it is accessible. The editor of the new invoice will not see anything of that. If he/she happens to click the export-control he should get an error message or nothing at all. Needs some error handler if target is unavailable. The most simple error handler in basic is:
Code:

Sub copyToMAinSheet
on error goto exitErr:
...
exitErr:
' do nothing or message
End Sub

If the helper functions are in "My Macros" of your OOo installation the code will not work, unless someone re-implements the functions on his machine.
Of course your A-doc needs to be open when you want to export. My macro does this for you (StarDesktop.loadComponentFromURL(....). There is no way to write into a closed spreadheet.
It's around midinight here. In germany we celebrate christmas evening on 24th of December. All guests are gone and TV is too bad.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Mon Dec 25, 2006 4:24 pm    Post subject: Reply with quote

OK, I'm getting into your macro. I've cut and pasted it from the listing you gave me earlier, and I'm trying to make it work. I've run into an error that I don't understand:



Sub copyToMainSheet()
Const sURL$ = "file:///home/frank/common/Data/Text/Chippy2/OO_Invoice+Spreadsheet/MonthB.ods"
Const sSheetName$ = "Posted_Invoices"
Dim oTgtAddress as New com.sun.star.table.CellRangeAddress

With thisComponent.NamedRanges("Data").getReferredCells
iColumns = .getColumns.getCount -1
data() = .getDataArray()
End With

When it gets to the line "iColumns = .getColumns.getCount -1

I get a runtime error.

"Inadmissable Value or Data Type"
"Index Out of Defined Range"

I'm guessing that the reference to "Data" is something that I have to assign?

I have 9 cells that I need to pull data from in the invoice (B-doc) and copy that to a single row in my master spreadsheet (A-doc). These cells are scattered all over the B-doc invoice spreadsheet. Do I need to define them somehow?

Thanks.

By the way, I don't know how to past code snippets into this board and have them stay formatted like you have. What do I need to do?

Never mind. I just figured it out in a test post, but I cannot now edit this one for some reason.

Frank.
_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Mon Dec 25, 2006 4:30 pm    Post subject: Reply with quote

Code:

Sub copyToMainSheet()
Const sURL$ = "file:///home/frank/common/Data/Text/Chippy2/OO_Invoice+Spreadsheet/MonthB.ods"
Const sSheetName$ = "Posted_Invoices"
Dim oTgtAddress as New com.sun.star.table.CellRangeAddress

   With thisComponent.NamedRanges("Data").getReferredCells
     iColumns = .getColumns.getCount -1
     data() = .getDataArray()
   End With
   
REM does not harm if it is already loaded:


There. That is how it should have looked.
_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 25, 2006 5:03 pm    Post subject: Reply with quote

Yes, you need to gather the 9 cells in a named range "Data", so "Data" refers to 9 cells in a row.
Some free space, assuming another (hidden) sheet of the invoice-template:
A1: =$Invoice.$A$1
B1: =$Invoice.$A$2
...
I1: =$TaxSheet.$C3
Select A1:I1 and type "Data" (without quotes) into the name box left of the formula bar. This is the named range where the macro gets it's dataArray() from.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Mon Dec 25, 2006 5:10 pm    Post subject: Reply with quote

OK, I'm stumped for the evening, and I hope you've gone to bed by this point. Smile

I did a search of the PDF for the book I have "OpenOffice.org Macros Explained" and it only has one short reference to "NamedRanges" and that is on page 367 (in the event you have this book as well). It states (hate it when I can't cut and paste from a PDF):

Quote:
The document's NamedRanges property contains the collection of named ranges in the document. You can extract each individual named range by using named and indexed access.


I don't understand how to do this. I've gone through the OOo Resource Kit book and found some information on naming 'regions' on page 612 and 613. It says that I can automatically name several regions. However, when I tried this, it appeared as if nothing happened.

Hope you had a good rest. Smile

Frank.
_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Mon Dec 25, 2006 5:15 pm    Post subject: Reply with quote

Villeroy:

>Yes, you need to gather the 9 cells in a named range "Data", so "Data" refers to 9 cells in a row. <

OK, got it. Our last two messages crossed. Give me a few minutes to set this up, or go to bed. Smile

Frank.
_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 25, 2006 5:20 pm    Post subject: Reply with quote

OOops, sorry! I should really test any code.
ThisComponent.NamedRanges.getByName("Data")
or
ThisComponent.NamedRanges.getByIndex(0) gets the alphabetically first one.
My ThisComponent.NamedRanges("Data") is too lazy and unfortunately Basic returns the first one happily where it should raise an error.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 25, 2006 5:37 pm    Post subject: Reply with quote

Done testing with a new document having 9 cells named "Data", one cell named "Done" and another cell, having
=IF(Done;"Done";HYPERLINK("vnd.sun.star.script:Standard.Module1.copyToMainSheet?language=Basic&location=document";"EXPORT"))
Code:

Sub copyToMainSheet()
REM the most chaotic spreadsheet in this world:
Const sURL$ = "file:///home/andreas/Documents/OOo/oooforum.ods"
Const sSheetName$ = "wordSplit"
'on error goto exitErr:
REM this is where my helper functions are:
GlobalScope.BasicLibraries.loadLibrary("Calc")
Dim oTgtAddress as New com.sun.star.table.CellRangeAddress
With thisComponent.NamedRanges
  with .getByName("Data").getReferredCells
  iColumns = .getColumns.getCount -1
  data() = .getDataArray()
  end with
  cellStop = .getByName("Done").getReferredCells.getCellByPosition(0,0)
End With
If cBool(cellStop.getValue) then
  msgbox "Already done!"
  exit sub
end if
REM does not harm if it is already loaded:
oTgtDoc = StarDesktop.loadComponentFromURL(sURL,"_default",0,Array())
oTgtSheet = oTgtDoc.getSheets.getByname(sSheetName)
oUsedRg = getUsedRange(oTgtSheet)
oUsedAddress = oUsedRg.getRangeAddress
oTgtAddress.Sheet = oUsedAddress.Sheet
oTgtAddress.StartColumn = oUsedAddress.StartColumn
oTgtAddress.StartRow = oUsedAddress.EndRow +1
oTgtAddress.EndColumn = oUsedAddress.StartColumn +iColumns
oTgtAddress.EndRow = oUsedAddress.EndRow +1
oTgtRange = getRangeFromAddress(oTgtDoc,oTgtAddress)
oTgtRange.setDataArray(data())
cellStop.setValue(1)
thisComponent.store
exitErr:
End Sub


'on error goto exitErr:
is off while debugging
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Mon Dec 25, 2006 5:45 pm    Post subject: Reply with quote

Wow!

You're going faster than I can keep up!

Anyway, the first correction you made fixed up a runtime error, which you fixed before I could post back to you. Therefore, the first piece of code works as it should, and it does very ably lift the info out of my invoice sheet, and paste it into the master spreadsheet. It is also lighting quick!

Now, the rest of your work with regard to setting up a button to make the macro run, I'll work on tomorrow.

You're a better man than I am. I'm tired.... Sad

>REM the most chaotic spreadsheet in this world: <

No, it actually works quite well and is really quite orderly. I should send you copies of what I am doing so that you can get the idea. It really works quite well, and it is very flexible.

Thanks so much. I'll spend part of tomorrow figuring out just what all this code does so that I understand it better. Not only did I need this particular tool, but I want to become more adept at macro programming in OO. I have some other projects that I want to put together as well.

Can you suggest a source of information on the OO API that shows some examples, and gives information on what is being returned? So much of this is so poorly documented.

I have the link at

http://api.openoffice.org/docs/common/ref/com/sun/star/module-ix.html

but the information there is cryptic at best.

Thanks again.

Frank.
_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 25, 2006 6:27 pm    Post subject: Reply with quote

In my opinion, the very first thing you have to learn before you start coding is the application itself. Knowing all the builtin functionality makes scripting much more powerful and reduced to a bare minimum. It's like html and javascript vs. html and css. Knowing about css makes tons of script-code obsolete. Knowing about styles, templates and the advanced builtin features makes an office document more compatible, fast, reliable and the code just needs to call some API-routines in order to save mouse-clicks (well, in an ideal world).
This is *the* tool for inspection of objects:
http://sourceforge.net/project/showfiles.php?group_id=87718&package_id=101416
It shows all properties and methods of a service and pops up your favorite browser with the relevant page of the API-reference.
This is the API-reference online:
http://api.openoffice.org/docs/common/ref/com/sun/star/module-ix.html
If you want it offline just install this:
http://download.openoffice.org/2.1.0/sdk.html
All together it amounts to thousands of print-pages of rather technical documentation.
The SDK requires further setup only for bindings to compiled languages (Java, C++). For script-coders it contains the complete reference with developer's guide.
This is my basic-wrapper for calling xray. It is in library "Standard" (always loaded) and loads the xray library. If the passed variable happens to be a (nested) array it xrays each element with cancel-option.
Code:

Sub myXRay(v)
Dim i%,obj,x%,sRec$
Static iRec%
   globalscope.basiclibraries.loadlibrary("XrayTool")
   if isarray(v) then
      iRec = iRec +1
      for i = lBound(v) to uBound(v)
         obj = v(i)
         myXRay obj 'recursive for nested arrays
         if (i< uBound(v)) then
            If iRec > 1 then sRec$ = "[Recursion="& iRec &"] " : else sRec =""
            x = msgbox(sRec & i -lBound(v)+2 &" / "& uBound(v) - lBound(v) +1,5,"X-ARRAY")
            if x = 2 then exit for
         endif
      next
      iRec = iRec -1
   else
      xray v
   end if
End Sub

_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Mon Dec 25, 2006 7:21 pm    Post subject: Reply with quote

Villeroy:

>In my opinion, the very first thing you have to learn before you start coding is the application itself.<

My newbie-ness was that obvious, was it? Embarassed

It is a valid point, however, as I have only been using OpenOffice for about 3 years, and mostly for light word processing. It is an awesome product, and I tend to agree with Andrew Pitonyak when he mentioned in his book that there is no one person today that is able to understand all of OO as it is SO big and SO complex.

I'll try the xray tool. I found myself hobbled this time by a lack of specific references. As I said at the outset, I've done programming before, but it was a long time ago. One of my most-used tools was the DOS Programmers Reference, where all the calls in the API were clearly documented. I referred to that constantly when writing my one 8,000 line opus that I and several others used for several years in the late 80's and 90's.

Thanks again so much for your help.

Frank.
_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Wed Dec 27, 2006 7:45 pm    Post subject: Reply with quote

Villeroy:

OK, the macro works a peach! I did the hyperlink as you suggested, and that got set up just fine as well. However, when I click the link, it crashes StarOffice 8.0 every time.

Suggestions?

Frank.
_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Dec 28, 2006 1:34 am    Post subject: Reply with quote

Hard to tell without url.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Frank Billington
OOo Advocate
OOo Advocate


Joined: 10 Feb 2004
Posts: 226

PostPosted: Thu Dec 28, 2006 7:45 am    Post subject: Reply with quote

Villeroy:

Not sure which url you mean, so here goes:

The one you supplied as a link is:

=IF(Done;"Done";HYPERLINK("vnd.sun.star.script:Standard.Module1.copyToMainSheet?language=Basic&location=document";"EXPORT"))

Location of both the invoice spreadsheet and the master spreadsheet is (curently):

/home/frank/common/Data/Text/Chippy2/OO_Invoice+Spreadsheet/000001.ods

and

/home/frank/common/Data/Text/Chippy2/OO_Invoice+Spreadsheet/MonthB.ods

I'm going to try to attach two jpeg screen shots of the location of the macro, as well as the upper part of the macro showing the defined constants.

These were the first things I looked at when the macro crashed SO8. I thought the hyperlink may have had a typo, so I checked both of your messages where you had the hyperlink typed in, and they both matched what I had cut and pasted into the second sheet of my invoice spreadsheet. I also checked the constant definitions, and they appear to be alright. The macro runs just fine when launched from the macro editor. It crashes SO8 only when called from the hyperlink.

Frank.









_________________
Single-booting Linux, all day, every day, at home and at work.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Dec 28, 2006 8:39 am    Post subject: Reply with quote

Yes, I wondered about the hyperlink-url. Anyway, the program should not crash because of a bad url. Your url looks fine. I'm clueless.
The entire url is passed to the macro as a string argument. Normally it is no problem to ignore a given argument in basic. May be it helps to add the argument to the routine call:
We do not need the dummy but let's take it, if it helps to avoid a crash...
Sub copyToMainSheet(optional dummy)
Optional in order to allow calls without arguments, such as the call from tools>macros>run.
btw: a recent example how to the argument could be used:
http://www.oooforum.org/forum/viewtopic.phtml?p=198495
No, I don't really believe that helps.
Some other urls:
=HYPERLINK(".uno:NewDoc") [calls file open dialog]
=Hyperlink("http://www.oooforum.org")
=hyperlink("file:///home/frank/common/Data/Text/Chippy2/OO_Invoice+Spreadsheet/000001.ods ")
Does this crash too?
What if you create "normal" hyperlinks?
Insert>Hyperlink...:URL: <any of the above urls> "Apply" "Close", hit enter in order to finish cell-input.
Other ways to call a macro:
Tools>Customize>Menues... add a menu-entry, which calls the macro
View>Toolbars>Form controls
Click the triangle in order to enter edit-mode, choose the button-tool, draw a button on the sheet
context-menu of button: Control...
Tab events: "Mouse buton released">Assign the macro...
Click the triangle in order to leave edit-mode, click the button on the sheet.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page Previous  1, 2, 3  Next
Page 2 of 3

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group