[users] Re: Open Forge Calc Translation of Quattro Pro data files

[Don Daugherty]

On 11/18/2011 9:58 AM, Dick Bingham wrote:
Please Help :  (1) I am interested in joining  this effort as a USER 
-- Not developer.
(2) I am searching for a way to convert ( translate)  several hundred 
files prepared under Quattro Pro ( dos , "for windows" and /or Corel 
version QP10).
I am aware that it is possible to change each QPRO file to an Excel 
Format by simply Opening each file and then Saving AS excel ( xls ? ).
  Would this make it possible  to then use Open Source  Calc to  add 
to / delete from or modify each file.. ?

Alternatively, is there a way to convert directly ?

I'm not aware of any way to do a direct conversion, but I've used the 
other approach successfully on several files.  I found doing a  QPro 
Save As to Excel v5/v7 format (I think that was the choice that worked) 
gave results I could successfully open in OOo Calc.  The results usually 
were not perfect but I could then edit to get what I wanted.  Some 
QP-functions translated ok; others required hand-conversion.  Macros 
didn't convert at all.  I could probably give you some advice/tips if 
you decide to go this way.  I suspect we should do this by private email 
unless there is more interest out there than l think.

--
-
To unsubscribe send email to users-unsubscr...@openoffice.org
For additional commands send email to sy...@openoffice.org
with Subject: help

[users] Re: I'm having trouble creating a formula, can anyone help?

[Don Daugherty]

On 8/28/2011 11:20 PM, Randy Barrett wrote:
I'm trying to make a formula where lets say cell A1 has a number 
without a formula. In cell H1 I want to make a formula that follows 
these guidlines.

  A1  H1
0-99  0
100-199 1
200-299 2
300-399 3
400-499 4
500-599 5
600-699 6
700-799 7
800-899 8
900-999 9
1000+   Platinum

is it possible and how do i do it?
If  I'm correctly understanding your guidelines, enter the following 
into cell H1 and copy it down into cells H2, H3, ...

=IF(A6<1000;QUOTIENT(A6;100);"Platinum")


--
-
To unsubscribe send email to users-unsubscr...@openoffice.org
For additional commands send email to sy...@openoffice.org
with Subject: help

[users] Re: I'm having trouble creating a formula, can anyone help?

[Don Daugherty]

On 8/28/2011 11:20 PM, Randy Barrett wrote:

I'm trying to make a formula where lets say cell A1 has a number
without a formula. In cell H1 I want to make a formula that follows
these guidlines.
  A1  H1
0-99  0
100-199 1
200-299 2
300-399 3
400-499 4
500-599 5
600-699 6
700-799 7
800-899 8
900-999 9
1000+   Platinum

is it possible and how do i do it?
If  I'm correctly understanding your guidelines, enter the following 
into cell H1 and copy it down into cells H2, H3, ...

=IF(A6<1000;QUOTIENT(A6;100);"Platinum")

My bad!  Obviously this should read
=IF(A1<1000;QUOTIENT(A1;100);"Platinum")
which is, of course equivalent to the solution provided by
Johnny Rosenberg


--
-
To unsubscribe send email to users-unsubscr...@openoffice.org
For additional commands send email to sy...@openoffice.org
with Subject: help

[users] Re: Formulas

[Don Daugherty]

On 7/1/2011 10:26 AM, Stacey Singleton wrote:

Hi,

Please can you tell me if there is a formula which counts the number of
unique sets of information in a spreadsheet?

Kind regards,
How do you define a "set of information"?   Is it the contents of the 
set of cells in a line, for example?


--
-
To unsubscribe send email to users-unsubscr...@openoffice.org
For additional commands send email to sy...@openoffice.org
with Subject: help

[users] Re: Spreadsheet import, fixed width, remaining delimiters challenge

[Don Daugherty]

On 7/1/2011 11:45 AM, Jim Parkhurst wrote:

Windows XP
Service Pack 3
All current patches
I monitor and check various networked systems. Part of the process is 
to export the information (reports, columnar format) as type .CSV. 
Then using OO I import the file. OO will bring up the Text Import 
screen which I will verify/select

Then
and process the column information. With V3.2 I would have a clean 
identifier slate. With V3.3 it appears that I have the previous 
iteration of import specification. I realized this more when I was 
performing a similar import which kept returning wrong information. I 
finally modified the first record and added a massive number of "x" 
characters. When I processed the file, I was able to locate - and 
delete/remove - the somewhere in history defined column indicators.
I have searched help ... for information on clearing the delimiter 
marks but have not found anything - yet.

Thoughts?

I have had other problems with ver 3.3 and finally resorted to 
reinstalling ver 3.2.1.  The "improvements" in 3.3 were not acceptable 
for my needs.  Unless the 3.3 import dialog box contains some new option 
that can make your import successful, my only suggestion is to go back 
like I did?

--
-
To unsubscribe send email to users-unsubscr...@openoffice.org
For additional commands send email to sy...@openoffice.org
with Subject: help

[users] Re: Pasting numbers in Calc

[Don Daugherty]

On 6/9/2011 10:45 PM, RA Brown wrote:

Eustace wrote:

When copying from

http://www.invincibleamerica.org/tallies.html

the 4 columns and pasting them in Calc as Unformatted text separated by
Tab, the first column of numbers pastes with some code in the beginning,
so I have to double-click in each cell, then something that resembles a
' (single quote) appears in front of the number, and delete it.

If I try to copy and paste these ' in the search box to have it replaced
with nothing it does not find them.

How can I easily change the column to simple numbers?

emf



Why not just select the "Detect special numbers" box and let Calc do 
its job?




Where is that box?  A new feature of version 3.3 possibly?

--
-
To unsubscribe send email to users-unsubscr...@openoffice.org
For additional commands send email to sy...@openoffice.org
with Subject: help

[users] Re: Pasting numbers in Calc

[Don Daugherty]

On 6/9/2011 6:48 AM, Eustace wrote:

When copying from

http://www.invincibleamerica.org/tallies.html

the 4 columns and pasting them in Calc as Unformatted text separated 
by Tab, the first column of numbers pastes with some code in the 
beginning, so I have to double-click in each cell, then something that 
resembles a ' (single quote) appears in front of the number, and 
delete it.


If I try to copy and paste these ' in the search box to have it 
replaced with nothing it does not find them.


How can I easily change the column to simple numbers?

emf

If this just started happening since the version number went from 3.2.1 
to 3.0 or higher, it may be a feature of 3.0.  I went back to 3.2.1 to 
get rid of this very undesirable (for me at least) behavior.  If you're 
willing to introduce 4 new columns to paste the data into, and use  
VALUE() formulas in the regular columns to get the values obscured by 
the small apostrophes, you may be able to get satisfactory results.  
Like you, I had no luck using Search/Replace to get rid of the apostrophes.

--
-
To unsubscribe send email to users-unsubscr...@openoffice.org
For additional commands send email to sy...@openoffice.org
with Subject: help

[users] Re: Open Office Calc

[Don Daugherty]

On 3/25/2011 2:56 AM, Marcello Romani wrote:

Il 25/03/2011 08:30, Pat Collett ha scritto:

Hi Marcello,
Downloaded OO3.3 and the problem seems to be over. Thank you so much.
Now I have another problem maybe you can help with:
I have a large file (8.5Gb) on Corel Quattro Pro 8 consisting of 2
"workbooks" with many links between them. As this software is no longer
supported and I'm afraid it may crash, I want to transfer it all to Open
Office Calc.
Is there a way to transfer it all while maintaining the links? "Cut and
Paste" does not save the links.
Pat
I used Quattro-Pro for many years but had to give that up due to 
frequent crashes of key files.  The conversion is not a slick 10-second 
process but it can be done.


For me the beginning was to Save AS (from Quattro-Pro) in Excel (.xls) 
format.  (I believe it was Microsoft Excel v5/v7 -- the other choice in 
my version of QPro is Microsoft Excel 97, which I think is older.)


This can then be imported into OOo Calc and saved in .ods format.

Now the tedious part begins -- converting things that were not exported 
well enough by QPro.  The biggest challenge I faced was that my macros 
didn't transfer to anything useful.  I simply had to deal with the issue 
involved in some entirely new way.


Good luck.  Write again if you have specific questions I might address 
-- like function correspondences.


--

To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: sy...@openoffice.org with Subject: help

[users] Re: Open Office Calc

[Don Daugherty]

On 3/23/2011 1:12 PM, Pat Collett wrote:

Using Calc on Windows Vista.
It keeps locking, shutting down and then having to recover in the 
middle of using the file (about 2.4Mb)

It's got to where I can't work on the file.
What's up and what can I do?
Pat.
patna...@telkomsa.net 
If 2.4 MB is the file-size in  .ods  format, it's actually much larger 
in working format (.ods files are zipped for storage; their uncompressed 
size when in memory can e much greater.)  Here are some of the things 
I've considered in trying to reduce the size and complexity, and hence 
the frequent "bombing":
1.  Am I trying to do too many loosely related things in one spreadsheet 
file?  If so maybe I should use separate spreadsheets for the different 
tasks -- a Divide and Conquer strategy.  The first step is to use Save 
As to create a second version, say for Task B; if there are additional 
tasks, say C and D, then repeat the Save As process to create additional 
versions of the original.  Then, with the last Saved-As version, delete 
sheets not needed for that Task.  Next reopen the original version, 
which will now be used only for Task A, and delete sheets not needed for 
Task A.  Repeat this process as needed to pare each version down to just 
what is needed for the corresponding  task.
2.  Are there pages (sheets) whose content will never change but which 
contain many formulas?  If so I convert those sheets to all values via 
the following steps.   On a page to be converted, press Ctrl+End, then 
Shift+Ctrl+Home to select the entire sheet, then Ctrl+C to copy, then 
Shift+Ctrl+V to invoke the Paste-Special dialog box.  The first time you 
do this you would probably need to uncheck the Paste all box; then 
uncheck Formulas, Notes and Objects, and check Text, Numbers and Date & 
Time; then click OK (or press Enter) to complete the conversion


I hope this helps

--

To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: sy...@openoffice.org with Subject: help

[users] Bug in OOo Calc 3.3.0

[Don Daugherty]

In previous versions of Calc, including 3.2.1, which I still have on one 
computer, linked web-sites and inserted csv files, containing data 
tables that involve dates, numbers and text, the dates come in as dates, 
the numbers as numbers and the text as text.  In 3.3.0 the dates come in 
looking like dates but in the edit window they each are revealed to be 
prefixed with a single quote, which renders them as text rather than 
values.  This is VERY undesirable.  It  requiires adding extra columns 
to each table and using VALUE() functions to recover the dates and then 
recoding all references to the data in the tabletable -- a lot of work 
for no apparent reason.  Can we get this "improvement" fixed?  Are there 
any security issues involved in my simply reverting to version 3.2.1?

--

To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: sy...@openoffice.org with Subject: help

[users] Re: Calc- reference a bunch cells into 1 cell?

[Don Daugherty]

On 2/18/2011 2:56 PM, . wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

My spreadsheet has a tiny monthly calendar on it that has 1 cell for
each day.  So, it has 7 columns across and 8 rows down.  When you select
the cells it has a range like this-  A1:G8

I want to put the contents of cells A1 to G8 into 1 cell on different
sheet in the same file.

It can be done by;

- - Selecting and Copying A1:G8

- - Pasting it into Writer (at this point it appears like a graphic that
you can rescale)

- - Copying it from Writer and pasting it into the single cell using
Paste Special->  GDI metafile


That works well but there's got to be an easier way.
The basic approach I'm suggesting is to concatenate the 56 cells (along 
with end-of-lines) to give the contents of the target cell.

The formula used is:
* Long Line Begins *
=A1&B1&C1&D1&E1&F1&G1&H1
&A2&B2&C2&D2&E2&F2&G2&H1
&A3&B3&C3&D3&E3&F3&G3&H1
&A4&B4&C4&D4&E4&F4&G4&H1
&A5&B5&C5&D5&E5&F5&G5&H1
&A6&B6&C6&D6&E6&F6&G6&H1
&A7&B7&C7&D7&E7&F7&G7&H1
&A8&B8&C8&D8&E8&F8&G8
* End of Long Line 
Explanation: cells A1:G8 contained the Name of the Month, Sun, Mon, 
etc., and the Day-numbers, 1-31, as appropriate, but formatted as text 
with a single quote, zero or one space, the number, and two more spaces. 
 Cell H1 contained an End-of-Line, created by pressing F2, ctrl+Enter, 
Enter.  The resulting calendar cell was formatted Courier New, a 
fixed-width font.


Since in practice the number wouldn't naturally be formatted as text, 
the practical implementation I suggest is to generate a second matrix 
somewhere -- I put mine in I1:O8 -- and use formulas to produce the 
formatted text.  Assuming the first two rows and the last are text 
already, the formula '=A1 (for I1) serves to simply copy directly.

For I3:O7 the formulas read like this (for I3)
=RIGHT(" "&TEXT(A3;"#")&"  ";4)

The formula for the target cell is similar to that given above, except 
that it references the cells in the new matrix (I1:O8) and the 
end-of-line cell, which has been relocated to I9.  (Incidently, to 
remind me to not over-write or delete that cell, I color the background 
Red.)  Here is the formula.

* Long Line Begins *
=I1&J1&K1&L1&M1&N1&O1&$I$9
&I2&J2&K2&L2&M2&N2&O2&$I$9
&I3&J3&K3&L3&M3&N3&O3&$I$9
&I4&J4&K4&L4&M4&N4&O4&$I$9
&I5&J5&K5&L5&M5&N5&O5&$I$9
&I6&J6&K6&L6&M6&N6&O6&$I$9
&I7&J7&K7&L7&M7&N7&O7&$I$9
&I8&J8&K8&L8&M8&N8&O8
* End of Long Line 
I can send a copy of the spreadsheet by private email if you wish.


--

To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: sy...@openoffice.org with Subject: help

Re: [users] Calc- remove Trace Dependents

[Don Daugherty]

On 2/4/2011 2:05 PM, . wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I hit Shift- F5 so I can Trace Dependents.

How do I remove the Trace Dependent lines as they're all over my sheet?
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJNTFwPAAoJEF4giPX/zzyYfOMH/i+/b5ayfbPlFtU05TWK2ANI
itaM+5fBc+WmeaRPNWvGX6GaZARAtdpsuemNY8pHE83bgyOqGAGbQZ51SQ6wzcBu
PpB9kDvxoK9Qenkn8v9hBy+3PJVbnzvA8JbaQi7N4Mrf7z1H6mRHxfC1FLpaLvLc
SopHWdI0Hb11R6D7PXxAvhr6PWCvmGv95b+XrbbQLNBs4GL/GjMI72CbYXm8uPlZ
pFOwegQLwGWEjAbauBLUdq1XdFIJfMTmRM/H0LR2nkipVQAGggW046PNJrqEu2+p
AAn1g/sqZ0l8wCq4hLPUYAdV4tm+0us7hDcI+hG/jpkZgT2kk/nfEv98nz5HTIs=
=8R3P
-END PGP SIGNATURE-


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org




Tools > Detective > Remove All Traces
Or from the keyboard:
Alt+T  Alt++D  Alt+A

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Calc IF function?

[Don Daugherty]

On 1/26/2011 9:27 AM, Sigrid Carrera wrote:

Hi,


2011/1/26 .


  I figured it out.  Due to very poor explanation at this link-
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_IF_function

the author fails to mention one important part; the necessity of putting
the  = sign before the function.







On 01/26/2011 10:04 AM, . wrote:


[...]



  You didn't read my post.

  Which post?

I got an empty email from you.

The only text that was contained in your mail was the subject line. So no,
there was nothing to read from you.
If you had it in an attachment, afaik attachments aren't allowed to the
maillist.



Sigrid

Ditto here; the original email contained nothing in the body, and in the 
atttachment section it showed a file attachment signature.asc

I avoid opening attachments without a clue as to what it's about.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Re: reporting bug with openoffice calc

[Don Daugherty]

On 11/20/2010 5:42 PM, John Connelly wrote:
Please ignore my last couple messages.  I created another macro (which 
does exactly the same thing) and called it ExampleSpreadsheetMacro2.  
This time I saved it in the location you wanted it to be in.  Attached 
is the spreadsheet with the macro... I hope :)


John


-Original Message- From: RA Brown
Sent: Saturday, November 20, 2010 4:38 PM
To: users@openoffice.org
Cc: jwconne...@live.com
Subject: Re: [users] Re: reporting bug with openoffice calc

On Sat Nov 20 2010 14:43:09 GMT-0800 (PST)  John Connelly wrote:
My "Standard" folder was actually under "My Macros", not under 
"OpenOffice.org Macros".  I'll attach a pic of this, just so you can 
see if mine is different that it is supposed to be.  Anyway, the 
macro is named ExampleSpreadsheetMacro.  When I right click on it, it 
does not give me an option to "copy" it as you suggested.  How do I 
attach a macro (by itself) to an email?  Sorry, I'm probably a lot 
less capable than your group.




To paste a copy get to the screen you took the shot of.  Highlight the
macro like in the shot.  Click the  Edit  button.  There you will see
the text that makes up the script.  Highlight the text, just like you
would in Writer, click on the Edit Menu then Copy.  Then paste that into
the message like this:

--Start of script
sub Main
rem 
--

rem define variables
dim document   as object
dim dispatcher as object
rem 
--

rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem 
--

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$F$20"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem 
--

dim args2(7) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ByRows"
args2(0).Value = true
args2(1).Name = "HasHeader"
args2(1).Value = true
args2(2).Name = "CaseSensitive"
args2(2).Value = false
args2(3).Name = "NaturalSort"
args2(3).Value = false
args2(4).Name = "IncludeAttribs"
args2(4).Value = true
args2(5).Name = "UserDefIndex"
args2(5).Value = 0
args2(6).Name = "Col1"
args2(6).Value = 1
args2(7).Name = "Ascending1"
args2(7).Value = true

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())


end sub

- End of script

This I did by recording the macro as you suggested.  The only thing is I
do not now how to attach it the button.  But running this does as you
ask.  I see no problem with the cells not changing.

Andy


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
I believe the failure of the macro is not due to a bug in the program, 
but a failure of the recording process to record key steps, namely the 
selection of the columns to be sorted.  To get a properly functioning 
macro you will need to construct one that explicitly selects only cols A 
& B, performs a sort, then selects only cols C & D, performs a sort, 
then selects only cols E & F, and performs a sort. This requires more 
fundamental knowledge than the Macro Recorder provides.  Try Googleing 
"OOo Calc Macros".



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Re: reporting bug with openoffice calc

[Don Daugherty]

On 11/19/2010 2:25 PM, Barbara Duprey wrote:

On 11/19/2010 1:40 PM, John Connelly wrote:

I would like to report a bug with Openoffice Calc.
When using a macro to sort several pairs of columns it seems that the 
software doesn’t sort them independently.  It may be easier to see 
what I’m talking about by checking out the spreadsheet I’ve created 
and attached specifically to show the bug.
If I’ve reached the wrong person, please let me know who I can 
contact about a bug with OpenOffice Calc.

Thank you.
John Connelly


John, the folks on the users list can check this out, that's usually 
better than going directly into bug reporting. I'm not familiar enough 
with Calc and macros to help you with this, but I'm sure somebody on 
the list can. If you are not subscribed to the list, let me know and 
I'll tell you how you can follow the discussion there.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

The spreadsheet came  through but its Standard Module contains no Macros.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Calc graph

[Don Daugherty]

 On 8/21/2010 2:28 PM, Marcello Romani wrote:

James ha scritto:

 I have 50 rows in a spreadsheet and I created a line graph.
Whenever I add a row, I need to  edit the DataRang/Series in the graph.
I can't set the DataRange to a high number because then the labels on
the Y-axis have blank labels and the graph looks squished.
Is there a way to create a graph that only uses data up to the first
blank row?

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org



If you insert a row before the last one instead of after it, the data 
range gets updated automatically. For example, you coud insert it just 
before the last row.


HTH

Marcello
I find this sort of approach works well.  I just make the "last line" a 
dummy that is background-colored differently and contains a reminder 
that "It" should be pushed down when new data is to be added


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Changing form in spreadsheet

[Don Daugherty]

On 7/14/2010 3:19 PM, Larry DeBlaay wrote:

Why is it that when I type in the letters CNA on my spreadsheet, no matter
what I do, it changes it to can.  What am I doing wrong?

   

It sounds like you have automatic spell-check  turned-on.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] macro

[Don Daugherty]

On 6/12/2010 4:00 AM, Harrie Borgers wrote:

L.S



A novice's requires

Where can I read more about:  args(0)

   
This looks like something you found in  macro you recorded in Calc.  I 
don't know that you can read much about it that can be generalized.  I 
think it's just a variable name created by the macro recorder, and that 
you're pretty much limited to changing the value assigned to it. For 
example, here is a macro I generalized from a recorded one:


sub GoLeft( optional X as variant )

rem --
rem define variables
dim document   as object
dim dispatcher as object
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
If IsMissing( X ) Then
   args6(0).Value = 1
   Else
   args6(0).Value = X
   EndIf
args6(1).Name = "Sel"
args6(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args6())

end sub

The lines starting"args6(0).Value = " are the ones that show 
generalization.  Everything else is just used verbatim.  I think you 
would need to be a Sun systems programmer, or someone with an equivalent 
knowledge level to do other sorts of things.

How to call - in basic - a second spreadsheet with variable name
   
What do you mean by "call"; do you mean read the contents of a 
particular cell or cells?

How do I read out  the value of cell(3,4) for calculation
   
This involves use of a number of functions and procedures.  I have 
written a set of these that meet my needs; they are based on Andrew 
Pitonyak's book and other publications, which I would urge you to get if 
you haven't already.  Be prepared for some computer-science level stuff, 
though.  I could send you my library by private email if you decide you 
want to pursue this.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] downloading stock quotes to open office spreadsheet?

[Don Daugherty]

On 4/26/2010 8:29 AM, rfand...@rochester.rr.com wrote:

I presently use Excel for my financial data. Excel has a template that allows 
the importing of stock quotes from MSN to Excel.
I'm hoping that Open Office has something similar. ANy suggestions??
   
I do this OOo Calc by going to a cell where I want to display the data 
(I started a fresh sheet in my spreadsheet file).  Then I click 
Insert>Link to External Data and type in a link like 
http://finance.yahoo.com/q?s=VMFXX.
I find that the resulting data occupies a pair of columns of variable 
row-number.  To create a table of linked data I place links side-by-side 
and use hlookup to access the various entries.  To this I start all 
links in row-2. leaving row for manually entered names, like WMFXX, to 
access the data.


Hope this can meet your needs.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Problem with sum in Calc

[Don Daugherty]

On 3/3/2010 1:36 PM, AG wrote:

JOE Conner wrote:

On 3/3/2010 10:52 AM, AG wrote:
11.73 + 7 + 15.75 + 24.68 + 17.5 + 21.18 + 20.65 + 17.85 + 19.25 + 
23.1 + 12.6 + 10.5 + 23.8 
TOOLS -> OPTIONS -> OPENOFFICE.ORG CALC -> CALCULATE -> Right Panel, 
do you have PRECISION AS SHOWN checked on one of the machines?


Joe Conner, Poulsbo, WA USA



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Joe

Cheers for the rapid response.  Neither of our machines had that 
option enabled.  However, even after enabling the "precision as shown" 
the error persists on my partner's version of Calc.


Thanks

AG

From my reading of Help, you wouldn't want that option enabled anyway 
since it would tend to result in more roundoff's.  I'm thinking that JOE 
wasn't recommending it as a cure but as the possible problem.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Problem with OpenOffice.Org Calc 3 that I can't find a solution for.

[Don Daugherty]

Abraham wrote:

I have the latest update for Open Office 3. I am very pleased with it and I
am also fast learning how to use it. I am 


E-mailing this contact because, as the subject implies, I need a direct
contact and response to solve this problem.  If I am writing to the wrong
contact, I apologize and I would greatly appreciate your navigation of this
e-mail to the proper contact.

 


Both Lotus and Excel and have a function, Paste Special, which will change a
cell's content from calculations to Value Only (pure numbers or percents).
They can do this for columns, rows or areas of cells in which the content
consists of mixes of numbers and the calculations of numbers, percents (%),
currency, etc, using data from other cells. 

 


Open Office also has Paste Special but I can't get as complete and easily
done a transition to Value Only.  

 


Right now I have a column in an OpenOffice.Org Calc spreadsheet with a mix
such as the one described above. When I scan the column and click on Calc
Paste I get a list of options none of which I can apply to the WHOLE column.
All I can do is convert individual common sets to Value only, one at a time.
And, as I previously said, I have the same problem for mixes in rows and
areas of cells.

Are you saying the following process doesn't do what you want?
1.  Select the cells in the desired area, column, or row
2.  Copy via Ctrl+C or equivalent
3.  Press Shift+Ctrl+V to bring up Past-Special
4.  In the left-hand column, only Text, Numbers, and Date&Time should be 
checked.

5.  In the center column, only None and Don'tShift should be dotted.
6.  Press Enter or click OK.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Search in Calc

[Don Daugherty]

joe g wrote:

I am using Search in Calc and it's telling me Search Key Not Found.
I'm using OOo 3.1.1
Could anyone help pl?

Two questions:
1.  Can you tell us in an EXACT example what you are searching for, e.g. 
"prune juice", or "=A1+B5", etc.
2.  In selecting a column, say column-C, are you clicking on the "C" 
heading the column or are you highlighting all the relevant cells, say 
from C1 to C999.  In some operations, the latter seems to be required, 
and I seem to remember finding that Search is one of them.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Datapilot error in Calc.?

[Don Daugherty]

James Elliott wrote:
I am president of an organisation and I am using a membership 
spreadsheet sent to me by my predecessor, Peter,  (who I cannot 
contact for the moment to help me solve this problem).


I have 4 new members to add ... i.e. I need to use 4 rows at the 
bottom of the spreadsheet to enter their data.

I have entered 2, but when I try and enter #3 I get this error message:

"You cannot change this part of the Datapilot table"

Peter did have the word "Filter" in that cell, and below it was a 
table consisting of a short list of members he had obviously sorted 
according to some criterion or filter, but I wasn't interested in this 
filtered list so I deleted it all to make room for more records to be 
added.


It looks like Peter was using Datapilot (which I do not understand 
yet) to do something and even though I have cleared those cells of all 
visible content there is obviously some non-visible content or 
reference to those cells that is stopping me from entering data into 
them..


How do I get out of this please? ... so that I can use these 
'Datapilot cells' to enter membership records.


Many thanks,  James.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org


Try pushing the "next" 2 lines down by clicking Insert/Rows.  Maybe this 
will expand the area and now mess-up the Pivot Table.




-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Spreadsheet update automation

[Don Daugherty]

James E. Lang wrote:

I have a five sheet spreadsheet. The contents of the first (1st) and
second (2nd) of the sheets are neither used nor changed in any manner
in this task.

I receive a text file as an attachment to an e-mail at least once a
week. The text file contains updated integer and date information for
the third (3rd) and fifth (5th) sheets. I can rather easily fully
automate everything that does not directly involve the spreadsheet. My
problem is how to get calc to automatically accept input data, perform
the spreadsheet update, print the defined print ranges in the third
(3rd) and fourth (4th) sheets, and save the updated spreadsheet. There
is data to be printed from both the 3rd and 4th sheets that is
calculated using the updated information. 

My
questions are:
1.  Does "updating" mean replacing the data in sheets 3 and 5, or adding
data to sheets 3 and 5?

replacing
2.  Do the print ranges need to be redefined each time, or do they 
remain

predefined?

no
I assume "no" means that the print ranges DO NOT need to be redefined 
each time.  That certainly simplifies the task.

3.  After you've responded to  these questions, if it looks to me like I
might have something to offer, would you be able to provide a copy of 
the

spreadsheet file and an email sample ('sanitized' if anything
confidential or proprietary is involved)?

yes
I guess the next step then is for you to send the sample files directly 
to my email address, daughe...@ku.edu, and not through a reply to the 
list, since normally attached files are stripped befores replies are 
distributed to the list membership.  (I am a regular list subscriber, in 
case there was some confusion on that point.)  I'll look at them and see 
if my ideas can be applied to your need.

Don


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Spreadsheet update automation

[Don Daugherty]

James E. Lang wrote:


 Not for the faint of heart!

I have seen no related discussions and this could very well be off 
topic for this list so feel free to direct me to a more appropriate 
list where I will gladly post the remainder of this message.


I have a five sheet spreadsheet. The contents of the first (1st) and 
second (2nd) of the sheets are neither used nor changed in any manner 
in this task.


I receive a text file as an attachment to an e-mail at least once a 
week. The text file contains updated integer and date information for 
the third (3rd) and fifth (5th) sheets. I can rather easily fully 
automate everything that does not directly involve the spreadsheet. My 
problem is how to get calc to automatically accept input data, perform 
the spreadsheet update, print the defined print ranges in the third 
(3rd) and fourth (4th) sheets, and save the updated spreadsheet. There 
is data to be printed from both the 3rd and 4th sheets that is 
calculated using the updated information.
When this was posted a few days ago, I assumed that either (1) someone 
else would give you a usable response or (2) it involved things way 
beyond my areas of competence.  Since so far there haven't been many 
takers, let me at least say a something and ask a few questions.


I routinely update certain spreadsheets using new data from the web.  My 
questions are:
1.  Does "updating" mean replacing the data in sheets 3 and 5, or adding 
data to sheets 3 and 5?
2.  Do the print ranges need to be redefined each time, or do they 
remain predefined?
3.  After you've responded to  these questions, if it looks to me like I 
might have something to offer, would you be able to provide a copy of 
the spreadsheet file and an email sample ('sanitized' if anything 
confidential or proprietary is involved)?




-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

[users] Hi-Jacked Threads

[Don Daugherty]

First of all, is this message the beginning of a properly started "New Thread?" 
 It was generated in Thunderbird by clicking Write and addressing it to 
users@openoffice.org

I ask this question because, while I have apparently never hi-jacked a thread, and I know 
this has been discussed before on this list, I still don't "get-it."

How does the message obtained by using Reply and starting a fresh Subject 
differ from one obtained by using
Reply and changing the Subject?  The Reply-To header in most if not all 
messages on the list contains users@openoffice.org, the same address I used for 
the present message.

I, and I suspect many joining this list, don't understand how changing the subject is not "Starting a New Thread."   
On one list I have been on for years, this is a recommended way of avoiding being guilty of the offense.


Does a Reply message have hidden baggage that distinguishes it?



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Wordperfect and Quattro Pro importing

[Don Daugherty]

Philippe Faure wrote:

Hello,

I was wondering if anyone has heard of how the importing of 
Wordperfect and Quattro Pro files?


I haven't been able to find anything.

thanks

Philippe


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org


I still have QuattroPro so I re-save .qpw files as MicroSoft Excel v5/v7 
.xls and then import into OOo Calc.  Many times this works perfectly, 
but in complex cases some "fixing up" is needed.  Find/Replace can often 
take care of a bunch of similar errors all-at-once, with the help of RegExp.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] problem writing a simple macro

[Don Daugherty]

Jean-Michel Courat wrote:

In a spreadsheet,
A1 = 1
A2 = 2

Starting  in A3, when doing with keystrokes
+
arrow up (going to A2)
+
arrow up (going to A1)
Enter

I obtain 3 in A3

When recording the macro the same way, I also obtain 3 in A3; great !!

But when executing the macro, I obtain a message error 523 in A1.

What am I doing wrong ?
Recording macros doesn't work very well, because it doesn't record what 
you probably really want recorded, which is something that adapts to new 
situations.


I did what you described.  Here is part of the resulting macro
rem --
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "StringName"
args6(0).Value = "=A1+A2"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args6())
rem --

As you see it simply enters the original formula.

To create a more useful macro, you need to actually write one.

It sure would be nice if we had a robust macro-recorder in OOo that 
would do a lot of the "writing" for us.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Interval syntax when using Frequency in Calc

[Don Daugherty]

Petter Wingren-Rasmussen wrote:

Hi!

My first post here and I'm fairly new to Open office. Couldnt find an answer
to this simple question in the FAQ, built in help or mailing list archives
so I'm posting here.

What I want to do is to make a Frequency table where I can control the
intervals exactly.
The only thing I've figured out how to do is something like this:

Values:Frequency:
1500345
3000762
6000342
9000211

Where the frequencies as far as I understand it is calculated through what
value is closest. I would like to control those calculated intervals exactly
however, as in:

Values:
0-1999
2000-2599
2600-6000
  

6000



What syntax should I use for this to work? So far I've tried < and > signs
and a:b and a;b without getting it to work..
I haven't used Frequency() yet myself but my looking at help tells me 
that the function is an Array Function involving three columns, say A, B 
and C though I don't think they have to be adjacent since they are not 
the same height .  Column A might contain 1000 rows of raw data.  Column 
B contains upper bounds on the sorting intervals.  In your example it 
appears these would be 1999, 2599, 6000, and a user-friendly label 
">6000".  Column C would contain the 4 resulting frequencies, 
corresponding to: Data<=1999, 19996000proceed as follows:

1.  Get the data arranged in columnar form.
2.  Setup the column of interval upper bound
3.  Select, .e.g., click on the top cell, press Shift and arrow down to 
select the proper number of cells (4 in the example.), type 
"=Frequency(", select the data, type a semicolon, select the interval 
limit cells (including the user-friendly label), type the closing 
parenthesis,  THEN press Shift+Ctrl+Enter.  The function wizard may be 
easier, but I've never tried it.

I hope this makes sense.  I seemed to work for the simple case I ran.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Row number of last non-blank cell in column?

[Don Daugherty]

Mark Knecht wrote:

Hi,
   I wonder if anyone knows of a function I might use to find the row
number of the last non-blank cell in a column?

   I have data that I paste in which comes from another source.
Depending on what the other source is doing the data pasted in is
different in length. I need to automatically determine when this data
ends.
  
I know this is an older thread, and that you may have been able to 
handle your needs by using some of the ideas discussed by others.  In 
case you are still interested, I have something of possible use.


After several false starts, I have been able to write a Macro function I 
call LastCell.  What it does is to produce the identity of the cell that 
is would be reached by pressing Ctrl+End.  This represents the 
intersection of the last column containing any entries with the last row 
containing any entries.  This is something I need to know at times.


If I read you correctly, what you want is different.  My "last row", 
which can be calculated via Row(LastCell(...)), is >=  your "row number 
of the last non-blank cell in a [specified] column."


To find yours would require a few embellishments but I think it wouldn't 
be difficult.


A limitation is that I can only make LastCell work (or so it seems, at 
the moment at least) if the macro is stored in the spreadsheet file 
itself rather than in a library in MyMacros.  This would require either 
copying a macro module into each file to be processed, or creating a 
reusable "template" file into which you could import a data file to be 
processed.


Iif you still have a need and this could work for you, let me know 
whether your "Last Row" is in fact different than mine.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] insert row in Calc

[Don Daugherty]

James E. Lang wrote:

I cannot access that link. Are you sure it's valid?

I have the same problem.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] insert row in Calc

[Don Daugherty]

Gene Young wrote:


Try this link;

http://extensions.services.openoffice.org/project/CalcEasyToolbar 

That works for me!  Thanks.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

[users] Can't find Basic Macrobar anymore

[Don Daugherty]

As I returned to macro programming after some time away  from it, I find 
I can no longer access the Macro toolbar in the Basic IDE editor. 
Everything I was accustomed to seeing on the fourth line of the editor, 
i.e. immediately below the Title bar, the Menu bar, and the Standard 
toolbar with icons for New, Open, Save, Print, etc., was missing: The 
Current Library drop-down menu and all the icons for Compile, Run, 
Step-into, etc..


I am able to make visible the Current Library drop-down menu by clicking 
View>Toolbars>Dialog; this promptly disappears after I use it to change 
Libraries.


At the time of my return and prior to discovering the above problem, I 
ran into some problems that led me to uninstall and re-install OOo 
3.1.0.  I can't say whether that was when I lost access to the Macro 
toolbar.  It could have been a week or so earlier when I went from OOo 
3.0.1 to 3.1.0.


Has anyone else had this experience, or does anyone else know of a 
"hidden" setting I need to adjust?  Do I need to revert to OOo 3.0.1?


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Calc: Writing My Own Spreadsheet Function

[Don Daugherty]

Can you attach an example file so I can have a look? Something is
obviously wrong somewhere… If the file works for me, the fault is
somewhere else, I would guess.

Johnny Rosenberg
  


Johnny:
Did you get the email I sent to gurus.knu...@gmail.com with a sample 
file attached?

Don

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Help!! Accidentally deleted a macro!!

[Don Daugherty]

John Jason Jordan wrote:

The problem now is trying to figure out the maze of how macros are
stored in OOo. I tried the Help and I have never read such a confusing
mess. It talks about libraries and modules and lots of other stuff as
though I know what they are. Or as if I care. All I want is every macro
I have ever created to be available in any document on my computer.
I was gone for the weekend so this may be too late to be of any help.  
Figuring out where macros are located and copying from one library to 
another can be accomplished in at least one way.  Here is the one I know:
1.  Open one or more files that do or might contain macros of interest 
to you.

2.  Click Tools > Macros > Organize Macros > OpenOffice.org Basic
3.  Here you will a window named "Macros from", and in that window ia a 
tree-structure with several levels.  I'm not sure of the official 
terminology so I'll call the highest level "SuperLibraries.  These look 
like 3-D rectangles with names like "My Macros" and "OpenOffice.org 
Macros'"   There will also be a SuperLibrary with the name of one of the 
files you have open.
4.  SuperLibraries  contain Libraries.  If a SuperLibrary is not open 
there will be a "*" in front; if it is open there will be a "-" in front 
and the Libraries will appear below as
yellow-gold boxes with a "+" or "-" in front; a "+" means that Library 
is not open but does contain Modules of Macros.  To open a SuperLibrary 
or Library either dbl-click it or click the "+".
5.  Click on any Module of interest.  The macros it containswill be 
listed in the the box entitled Existing macros in  : 
6.  Once you find the Library, Module and Macro of nterest, click the 
Edit button on the far right.
7.  This brings up a window whose title will be of the form: 
"SuperLibrary.Library - OpenOffice.org Basic"  Across the bottom of 
this  window will be a series of tabs with the various module-names on 
them.  Below the third line of the main window is a Basic text-editing 
window containing the code for the macros(s) in the module.  Doubtless 
you've been here before since you've already written macros.
8.  On the 3rd line of the main main window, just above the tsxt-editing 
window is a drop-down menu-box.  This is the key to moving code from 
library to library.  Clicking the arrow for the drop-down box gives you 
direct editing access to the other SuperLibraries and Libraries.
9.  My approach therefore is to copy the text (using Ctrl+C) in one 
module, use the drop-down box to get to the desired new location, create 
the appropriate blank Module/tab there, and paste-in the text.

10.  There may be more elegant approaches, but this at least works.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Calc: Writing My Own Spreadsheet Function

[Don Daugherty]

Johnny Rosenberg wrote:

I don't know what to say… I pasted the text to the Macro edit thing
and it worked, even when I removed all the comments.

OpenOffice.org 3.1.0
Ubuntu 8.10

Did you check your macro security settings? Mine is set to medium.

Johnny Rosenberg

Hi Johnny
I don't know what to say either, other than "thanks for trying."
I'm running OpenOffice.org 3.1.0, Windows XP SP3.
My macro security is also set  to medium.
When  I test the function by using the following test macro, located in 
the same library,

Sub Main
print WahooFunc()
End Sub
I get the expected results, "I am in WahooFunc.", but when I enter 
"=WahooFunc()" in the spreadsheet, I get "#VALUE!" as a cell message and 
"Error: Wrong data type" in the status-bar.


Am I correct in understanding that "=WahooFunc()" works for you, 
yielding "I am in WahooFunc." in the cell display?

Don


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Writer: Creating Plain Vanilla Document

[Don Daugherty]

Richard and Brian:  Thanks guys -- those ideas solve my problem.  Now I 
can have my cake and eat it when I want to.

Don


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

[users] Calc: Writing My Own Spreadsheet Function

[Don Daugherty]

I've attempted to write my own spreadsheet function and got no results 
returned -- just the message "#VALUE!" and a status-bar message "Error: 
Wrong data type"


I then implemented Andrew Pitonyak's WahooFunc (shown below with a 
number of lines commented out to make it ultra simple and easy to 
proofread.)
When I enter "=WahooFunc()" into a spreadsheet cell, I again get the 
samemessages as above.  The spreadsheet seems to be finding the function 
because if I mis-enter the function, say as "= WahoFunc()", the cell 
message becomes  "#Name?" and the status-bar message becomes "Error: 
Invalid name"


Function WahooFunc(Optional x) As Variant
Dim s$
s = "I am in WahooFunc.  "

'If IsMissing(x) Then
'  s = s & "No Argument was passed."
'ElseIf NOT IsArray(x) Then
'  s = s & "Scalar argument (" & CStr(x) & ") is type " & TypeName(x) & "."
'Else
'  s = s & "Argument is an array (" & LBound(x, 1) & " To " & UBound(x, 
1) &_

'  ", " & LBound(x, 2) & " To " & UUBound(x, 2) & ")."
'EndIf

WahooFunc = s
End Function

Ideas anyone?

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

[users] Writer: Creating Plain Vanilla Document

[Don Daugherty]

I have what seemingly should be the default, or at least a trivial 
task.  Creating a document with simple formatting, i.e. one with a some 
standard font and point size, left alignment, and no other features that 
I do not explicitly invoke. 


I have tried the folllowing:
1.  Use Ctrl+N to create a new text document
2.  On line one type  "Title" followed by a two carriage-returns.
3.  On line 3 type "1." followed by Tab, followed by "Item 1" and a 
carriage-return.
   What immediately happens is that line 3 is indented by 0.25 inch 
(which appears to be about half the distance to the first tab-stop in 
the ruler bar), and  "2." automatically appears in line 4, 
indented so that it lies directly underneath the "1."


It seems that the program "knows" that I want some outline type of 
numbering introduced.  The program is WRONG!  I want and need to set my 
own document style, which does not match this apparently "default" behavior.


Thinking that the default document template simply needed to be changed, 
I followed the instructions in Help to create a new  template and set it 
as the default.  This was  not successful.  Either the  unwanted 
"Bullets and Numbering" behavior is hidden in my supposedly clean new 
template, or the behavior is indemic to the program itself.


Help!

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] OOo Writer: saving and opening documents

[Don Daugherty]

Any suggestions regarding the interruption to work when auto-saving in 
the background?


My guess is that it's not possible.  I think the program needs to 
"freeze" the current content while saving in order to get a consistent 
save.  What one might really wish for is a faster save so that the 
interruption is briefer.  Maybe the developers could find ways to move 
in that direction, but I don't think zero delay is possible.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] How to get current cell in Basic Macro

[Don Daugherty]

(Chen Hao) wrote:
> When I use the MS-Excel , I offen use "Ctrl+;" to insert the current date to
> a cell or after some strings in a cell.
> But i find OOo-Calc did not supply this function or I haven't found it.
>
> So i want to write a Macro to do this , but there are two problems , and i
> think maybe someone could give me a hand.
>
> 1. how to get the current selected cell with basic ? So that i could modify
> it .
> 2. how to change the path that saving the user-defined macros? I offen
> forgot to backup those things in C-disk when i reinstall my Windows OS >_<
>
> Thanks very much!
>
> Chen Hao 
Andrew Pitonyak has several publications that I've found useful for
problems like this. One is available via
http://www.pitonyak.org/AndrewMacro.odt
Section 6.5.2 gives a sample macro that gets the current cell.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Calc: insert a date string into a cell - not a function, not a format.

[Don Daugherty]

Harold Fuchs wrote:

On 18/05/2009 19:34, Brian Barker wrote:

2009/5/14 Nobody Noname:
I want to insert a string into a cell using keystrokes. I'm sorry 
this will seem overdone, but I want people to know I've tried to 
research this on my own.

[...]
What I want is to do is insert into the active cell a string which 
represents 'now', ...


From: Harold Fuchs 
Date: Mon, 18 May 2009 10:06:42 +0100
Sorry to reply to my own post but the simple formula 
=IF(A1<>"";IF(B1<>"";B1;NOW());"") entered into cell B1 puts a 
non-changing timestamp in B1 if A1 is/becomes not empty but sets B1 
empty if A1 is/becomes empty. Sorry but, so far, I can't get B1 to 
change if and only if A1 changes.


Oh. To make this work you need to check the "Iterations" box at 
Tools>Options>OpenOffice.org Calc>Calculate. If you don't do this 
then B1 will show Err 522 - Circular Reference - because the formula 
in B1 refers to B1.


Thanks to Winfried Rohr, author of the above mentioned extension, 
for this extra goody.


At 08:05 18/05/2009 -0700, Nobody Noname wrote:
Thanks, Harold. I appreciate that you have considered my request to 
be worth much of your time :-) .


I'm pretty certain that the recursive formula which you/Winfried 
Rohr have offered is a formula that will recalculate when I press 
F9. I believe that when I made my first post I mentioned that such a 
solution would not be acceptable: "...nor do I want any other 
function which will change when F9 is pressed." Further, I titled my 
post with the words "insert a date string into a cell".


The reason for this limitation is this: I will use in spreadsheets 
whatever solution is successful, those current and future 
spreadsheets may need to have several cells calculated; why generate 
a spreadsheet in a condition where certain cells must be calculated 
yet the cells with date strings must NOT be calculated?


That would represent a major hurdle, in my opinion.


May I say that I am surprised that you should reject this suggested 
solution to your problem without even taking the trouble to test it?  
(It would take only a moment.)  Yes, I'm sure it recalculates when 
you ask for that to happen, but it doesn't *change* - which is the 
important point.  And then you even quote in detail various phrases 
from your earlier message!


When you ask a question here, people may try to solve what they 
perceive is your problem, even if that involves a technique you 
hadn't thought of.  Or to offer their best effort, even if it does 
not exactly match your conditions, especially where - as here - there 
may simply not be a solution in the terms you specify.  This is 
surely helpful, not something to be dismissed?


Incidentally, I've experimented with this really rather clever 
solution, and find that you can simplify the formula a little.  To 
generate the current date and time in a previously empty cell, use

 =IF(xx="";NOW();xx)
- where "xx" is the identifier of the cell in question.  As already 
explained, you need to have iteration enabled.

I trust this helps.  (Oh, but you are welcome to reject it!)

Brian Barker


There's one thing here I don't understand: I enter the formula (either 
version) into A1 and get a "now" timestamp. I now wait several 
minutes. I now drag/copy A1 across or down. The values produced do 
*not* reflect the later time. Instead all the new cells get the same 
time as was in the original A1. The same happens if I copy A1 and 
paste into another cell. The new cell gets the old time. In both cases 
the formula is correctly adjusted to refer to the new column(s)/row(s) 
but the value produced reflects the earlier (A1) time. I've tried both 
Paste and Paste Special.


Any ideas? Oh: OOo 2.4.1 on Win XP Pro


I believe that's the beauty of the formula.  It executes as soon as it 
is entered -- and immediately overwrites itself with the time stamp for 
that moment in time.  Since the formula has now been replaced by a 
value, it no longer changes!



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] SCALC - Features not found

[Don Daugherty]

subratan...@punjlloyd.com wrote:

Dear Mr. Daugherty,

Thanks the matter nicely solved with macro. However please provide more 
information about MACRO, if possible and its varient usages.
I'm not sure there's a lot more that I can tell you about the macro 
features -- It's too new to  to me also -- but there are many very 
knowledgeable people on this mailing list. 
What I can tell you is:
1.  The Macro recorder feature works well for operations that do NOT  
require recording what  is done in any dialog boxes.
2.  Macros can be written by the user.  This requires knowing or 
learning Star Basic, which is similar to but not identical to Visual 
Basic.  The help system provides some useful information but needs 
updating -- the discussion of GoSub is one example.  I could not make it 
work as described; Pitonyak, in "OpenOffice.org Macros Explained", 
recommends against its use.  I would certainly concur.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] SCALC - Features not found

[Don Daugherty]

subratan...@punjlloyd.com wrote:

Hello,
I am a new user of OOo and recently I have installed in 16 PC all are 
using XP as OS.
Previously we ware using MSO and few features found and extensively used 
by our users are missing.


In Excel (Ctrl+D) it copies the contents of the above cell. now this is 
not possible? (in calc It show a list and only text are displayed) 


With Regards,

SUBRATA NATH  -  ( ISD / AS-01 / PLL )
We shall over come, some day, deep in my heart , I do believe, that we 
shall over come some day...
  
I simply use up-arrow, Ctrl+C, Down-arrow, Ctrl+V to do this when 
needed.  However, you could record a macro (or write one if necessary), 
and then assign it to Ctrl+D. 


To record the macro, do this:
1.  In Calc, with any spreadsheet loaded, find an area where you can 
execute the process by hand.

2.  Click Tools/Macros/Record Macros
3.  Execute the steps I indicated above: Up-arrow, Ctrl+C, Down-arrow, 
Ctrl+V

4.  Click Stop Recording
5.  A box called "OpenOffice.org Basic Macros" should appear.
6.  Double-click "My Macros"
7.  Click on "New Library".  Type a name, like "Calc" and click OK  (If 
you have previously created a library, you might want toskip this line 
and use it.)
8.  Click on the library (Calc or whatever you have decided on) and 
click on "New Module".  Type a descriptive name like CtrlD, and click OK.

9.  Click Save

To make the assignment, do this
1.  Click Tools/Customize
2.  In the Category box scroll down to OpenOffice.org Macros, locate the 
macro ("My Macros" will here simply be "user") and click on it

3.  In the "Shortcut keys" box scroll to Ctrl+D and click on it
4.  Click Modify and click OK

Hope this is clear and  works for you.


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

[users] Macro Programming Questions

[Don Daugherty]

Question 1:

How should I change the following macro so it that successfully accesses 
a function and a subroutine in a different library?
It's not clear to me that my "path" is correct or that my use of square 
brackets is done in a way the solves the problem of spaces in the "My 
Macros & Dialogs" portion of the "path."


sub Main
rem --
rem define variables
 Dim SheetName$, Addr$
rem get sheet name
 SheetName$ = [soffice.My Macros & Dialogs.Calc.CellOps.Sheetname]
print SheetName$
' goto address appropriate to sheet -- default= A1 (0,0)
 Addr$ = "A1"
 If Sheet$="Fund Ratings" Then
 Addr$ = "E9"
 EndIf

 Call [soffice.My Macros & Dialogs.Calc.CellOps.SelectAddr( SheetName$, 
Addr$ )]


end sub

Question 2:

Where in the macro library structure should a Basic macro-function be 
located in order for it to be used directly in a spreadsheet cell, ala 
the Wahoo function in Andrew Pitonak's book "OpenOffice.org Macros 
Explained", p. 368?


Question 3:

Is there a different list that I should have submitted the above 
questions to?  I'm sure that many, many OOo users have no interest in 
such matters.




-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] How can I make a hotkey for a macro?

[Don Daugherty]

Hongsheng Zhao wrote:

Dear openoffice Users,

I've just installed a customized macro for openoffice, when I want to perform 
this macro, I must do this following steps:

1- Go to the this location: tools | macro | run macro 


2- Select my macro and then click the execute button.

Is there any way to assign a hotkey to this macro?  


Regards,
  

Sure, just do the following steps:
1.  Click Tools/Customize and click the Keyboard Tab
2.  In the Category Box click the "+" in front of "OpenOffice.org 
Macros" (scroll down to nearly the bottom of the list), locate your 
macro and click on it.
3.  In the Shortcut Keys Box, scroll through the list of keys and select 
one to use as your "Hotkey" (preferably one that shows no currently 
assigned function -- F3 or Shift+F3 for example. Click on your choice.

4.  Click on the "Modify" key and then click OK.

-
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org

Re: [users] Making the result of a Calc function permanent

[Don Daugherty]

John W Kennedy wrote:

I have been asked a Calc question to which my immediate thought was to
reply: "No, that is not the way spreadsheets work". On reflection, I 
began

to wonder if the following was possible.


I think you were right at the very beginning: this is not how
spreadsheets work.


Well, yes and no. The function has existed in most historic 
spreadsheets, going all the way back to VisiCalc. But I notice that I 
cannot find it in Numbers '08 (from Apple's iWork suite) at all, and 
OOo's way of doing it is a little on the arcane side.



I concur with the latter.  QuattroPro 10 has this under
Edit/Convert to values and it's a very handy thing to have.  But 
QuattroPro has problems I'll not go into in this forum.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] Calc Refuses to Save

[Don Daugherty]

Twayne wrote:
I've tried hard to make Writer and Calc and work work for me, I really 
have, but this one has me baffled.


XP Pro SP2 OOo 2.4.1

Calc, two sheets in use, first sheet 55 rows long, two columns, a few 
Notes:
   I added data to two columns, the data in columnA being a URL, and 
clicked Save as I have learned to do when I add data slowly to a 
program.  It's been open for a couple hours total.


First, it warned me there were Notes; I said OK

Then it refused to save, givieng no logical reason other than it would 
not save.


I deleted the most recent entry, tried Save again, still refused to 
save.


Deleted another row, same thing.

Ctrl-Z'd that data back in and copied it all into Excel before I lost 
it; very hard to reproduce. Yes, it gets archived but only nightly.


Closed/reopened Calc and Calc's memory of the file in Recent Documents 
didn't exist.  Prior to this it has been there (for about two weeks) and 
was not pushed out by opening new documents.


Located, opened the .ods file; now it's read-only.  Closed, reopened 
again:
   Still not in Recent Documents list, not read only and actually seems 
to be working OK now.


Any thoughts what happened?  Is there some relationship to that Notes 
message box?  Any guesses why it did this and what odds are it will do 
it again?


I plan to continue now using Excel, since I know it won't do things like 
that, or think so anyway, and I'll keep the Calc file around for 
fiddling with.


TIA for your thoughts/advice

Twayne




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




The refusal to save is, I think, directly related with the Read-only 
status.  I believe you either need to use SaveAs with a new fulename or 
remove the read-only status, say by using Windows Explorer or some other 
file manager: locate the file, right-click on it, select properties, 
locate the read-only status box and uncheck it.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] Another quick calc question

[Don Daugherty]

Jerry Feldman wrote:

On one sheet I have a monthly column of numbers where only the end of
the year is populated (for 50 years), as an example
  A   B
7/31/2007   0
8/31/2007   0
9/30/2007   0
10/31/2007  0
11/30/2007  0
12/31/2007  123.456
 .
 .
 .
What I would like to do is to create another column that has 1 year for
each row:
X  Y
12/31/2007  123.456 (=A6)
12/31/2008  456.678 (=A(6+12))

I'm not sure how to set up a formula is column Y that will reference
the end of year numbers. 



My preference would be to simply use, assuming you start columns X and Y 
in row 2, for example, Y2=vlookup(X2;$A$1:$B$999;2)


However,to more closely match what you have suggested you want to do, 
you could use three columns, X, Y, and Z.  Col X would contain the 
December dates, as you have illustrated, Cell Z2 and Y2 would contain 
the following entries: Z2 would read ="B"&TEXT(12*ROW(X2)-18;"###") and 
Y2 would read =Indirect(Z2).  Actually, you could eliminate column Z and 
have Y2 read =Indirect("B"&TEXT(12*ROW(X2)-18;"###"))


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] OpenOffice Calc - Convert Number to K/M/B

[Don Daugherty]

WANG, Xiaoyun wrote:

Hi, gurus,

Is there an easy way to convert a number in a cell to a rounded value with
suffix of 'K', 'M', 'B'?
For example: 1,000 -> 1.0K; 1,111 -> 1.1K; 1,234,567 -> 1.2M

Thanks in advance.

Unless there is a format code somewhere that does it (I haven't found 
one,) the only thing I know of that would give this 'appearance' would 
be to insert an extra column next to the column of cells containing the 
numbers you want to 'format' and make it show the text equivalent of 
what you want.  For example if column C contains the actual data, Insert 
a blank column to the right of C, (pushing the original D and above one 
letter higher).  Then enter (using row 10 as an example) into cell D10 
the following formula:


=IF(C10<1000;C10;IF(AND(C10>=1000;C10<100);TEXT(C10/1000;"###.#")&" 
K";TEXT(C10/100;"###.#")&" M"))


Recognize that D10 does not contain the actual value of C10, so you 
would still need to refer to C10 for any subsequent calculations.  You 
could, however, hide column C if you wanted to.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] Calc: how to set defaults like column width

[Don Daugherty]

Hi,
I'm new to Calc so I haven't tried this yet, but searching Help for 
"defaults" and choosing "documents" seems to reveal how to do what you 
want.  It refers to creating a template(or modifying an existing one) 
 , saving it, and then going through another step to make that template 
the default for all future new documents in that program.

Don


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] Calc will not open QuatroPro spreadsheets.

[Don Daugherty]

Philip Walsh wrote:
I am using version 2.4 of OpenOffice.org. My problem is when using Calc
it will not open QuatroPro files.  I spent 8 hours searching your guides
and blogs with no finds on QPW.
All atempts to add a new extensions lead to a dead end.
**
I'm facing a similar problem.  I still use QPW but I'm working on a 
conversion procedure.  It's not ready for "prime-time" but I've had 
reasonable success on one fairly complicated file, and I'm next going to 
try it on another one to see if I've covered most of the issues.


I assume you've already tried saving it in QPW as an Excel file (*.xls) 
and importing that into Calc.  My spreadsheet was fairly complicated and 
Calc didn't like it.


My approach involves a multi-step process involving a Quattro macro, 
several NoteTab Pro macros (Clips as they are known in that program), 
and one in OOo Calc.
1.  My QPW macro disguises all functions as text before exporting as an 
Excel v5/v7 xls file, which Calc has no problem with.
2.  In Calc I then save the xls file as html which can be edited in 
NoteTabPro -- a powerful, inexpensive text editor you may be familiar with.
3.  Using NoteTabPro I then run two "Clips." The first cleans up a lot 
of garbage inserted in the process of disguising the QuattroPro 
functions as text, converts from QPW functions to Calc functions, and 
re-saves, on a Page-by-page basis, in html form.  The second launches 
Calc, and "types" commands into Calc causing it to import the individual 
page files in the proper order.

4.  A Calc macro then restores original page names.
5.  Finally one global find and replace Calc operation undisguises the 
functions.


If you're interested in the details let me know.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] need help setting up open office, so i can complete my spreadsheets

[Don Daugherty]

Bonnie Tharp wrote:
I would like you to explain to me how i setup open office, so i can get into my math 24 and complete the spreadsheets in the cd. i have tried several different settings and i can not seem to open up my spreadsheets, so i can put other calculations into the columns and it is caculated for me. Could you please help me. If their is a phone number i can call to get help or can you help me over the internet. 


What is the file format of your existing spreadsheet files, Excel 
(.xls), or what?


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] Need some help with Calc and data lookups

[Don Daugherty]

Don Daugherty writes again:
I see that my use of tabs has screwed=up the appearance so let me 
re-format what I just sent;


Jon,
Like you, I'm new to Calc, and more experienced users may have better
suggestions, but here is an idea I've used in another spreadsheet:
1.  In the first sheet, insert a new column to the left of the first
column which I think is actually your State column rather than your City
column. I'll call it StateCity for illustration
The resulting table would then look like:
A  B C   D   E
1 StateCity  State CityHotelCar
2codenver  88   23
3camenlo   90   45
4nbdenver  1000 12

In cell A2 enter =B2&C2, and copy into A3 and A4 so that the strings
codenver, camenlo, and nbdenver appear in A2, A3 and A4.
Then you can do a table lookup based on column A which is sort of the
"AND" of B and C.
Hope this is clear and that it is helpful.
Don


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] Need some help with Calc and data lookups

[Don Daugherty]

Jon,
Like you, I'm new to Calc, and more experienced users may have better 
suggestions, but here is an idea I've used in another spreadsheet:
1.  In the first sheet, insert a new column to the left of the first 
column which I think is actually your State column rather than your City 
column. I'll call it StateCity for illustration

The resulting table would then look like:
A   B   C   D   E
1   StateCity   State   CityHotel   Car
2   co  denver  88  23
3   ca  menlo   90  45
4   nb  denver  100012

In cell A2 enter =B2&C2, and copy into A3 and A4 so that the strings 
codenver, camenlo, and nbdenver appear in A2, A3 and A4.
Then you can do a table lookup based on column A which is sort of the 
"AND" of B and C.

Hope this is clear and that it is helpful.
Don

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] My problem is saving my documents on my flash drive and opening them in emails

[Don Daugherty]

Angela Weathers wrote:

I am not sure which verison I am currently using because I am not at my
computer, however I am having problems saving the work I do on open office
and reopening it. How am I suppose to save it so that it will appear normal
on my flash drive. And the same goes for my email-if I email a friend an
attachment I made with open office writer when they try to open the
attachment it is all crazy and not legible. Please help

Sincerely
Angie Weathers

  
You might try saving in MS Word (.doc) or perhaps even plain ASCII 
format before you attach it.   The default .odt format looks weird 
(hearts between words and letters) when viewed  in some programs.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [users] [moderated]

[Don Daugherty]

James Knott wrote:

magdalena tosteson wrote:
i was responding to the lengthy questionnaire but i am paying for the 
use of the project.  it was NOT a freebie
AND i got open office thinking that i could IMPORT pdf files which 
has not been the case so far.  if this is not a feature of the 
software, i have wasted my hard earned dollars!!


I don't know where you got OpenOffice from, but it's a free download 
from www.openoffice.org.  There are many vendors who will charge you 
for it.  As for PDFs, OpenOffice can create, but not import or edit them.



For reading PDF's you might like the free Foxit reader 2.2.  I prefer it 
to the free Adobe Reader


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[users] New Calc user Questions

[Don Daugherty]

1.  Am I writing to the best list for issues concerning the use of Calc?

2.  My immediate concern is Macros for Calc.  I have no experience with 
VBA, which seems to be the language used for OpenOffice macros, but I 
have worked in old-fashioned BASIC, Pascal, Clip (the macro language 
used in NoteTab), and the macro language in QuattroPro.  I've looked 
through the Calc help system for version 2.3 and the one in version 2.4 
appears to be the same though I haven't searched there as diligently.  
I've also downloaded Microsoft's Visual Basic Express Edition, hoping to 
learn some things there (which I have, but nothing specific to my 
immediate needs.)


What I'm looking for is procedures, functions and/or subroutines that 
allow the macro to interact with Calc itself.  Specifically, I want to 
(among other things) have the macro get data from a specified cell, 
manipulate it, and then do something to the spreadsheet.  All the 
functions etc I've able to find seem to have nothing to do with 
interacting with the spreadsheet loaded into Calc.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]