macro pasting text instead of formula

Joel_Madero · December 10, 2014, 2:04am

Hi There,

I have a macro that used to work and now it's not any more, trying to
diagnose what's going on. Code snipit:

            'takes text value from QIF entries and pastes them into
column I, values are pasted as text
            if (dateOption = "DD/MM/YYYY") then
               pasteValue =
mid(currentCellValue,5,2)&"."&mid(currentCellValue,2,2)&"."&mid(currentCellValue,8,4)
               pasteSheet.getCellByPosition(8,pasteRow).String = pasteValue

               'takes text values from column I and converts them to a
date, which is then placed in column B
               pasteValue = "=DATE(VALUE(MID(I" & pasteRow+1
&",7,4)),VALUE(MID(I" & pasteRow+1 &",1,2)), VALUE(MID(I" & pasteRow+1
&",4,2))"
               pasteSheet.getCellByPosition(1,pasteRow).String = pasteValue

             end if

the trouble is with these lines:

pasteValue = "=DATE(VALUE(MID(I" & pasteRow+1
&",7,4)),VALUE(MID(I" & pasteRow+1 &",1,2)), VALUE(MID(I" & pasteRow+1
&",4,2))"
pasteSheet.getCellByPosition(1,pasteRow).String = pasteValue

The problem is that the formula is being pasted as text (with a ' in
front) so it's not actually performing the formula function which I
need. I know it makes sense that it's being pasted as text it's a STRING
. . . just not sure how to get the formula (or at least the value that
it carries) into the value "pasteValue" so that it's pasted as a date
(which is what it's doing). I know this seems really weird but it's
transforming a QIF file to a spreadsheet format which then has nifty
details (such as moving averages, graphs, etc . . .).

Suggestion really welcome as this is related to my finances and
currently my macro is pretty broken because of this. Thanks!

Best,
Joel

Villeroy · December 10, 2014, 10:04am

pasteSheet.getCellByPosition(1,pasteRow).String = pasteValue

Well, you set the string of a cell.
Set the formula instead.

Joel_Madero · December 10, 2014, 5:23pm

Hi All,

Well, you set the string of a cell.
Set the formula instead.

So I just started from scratch because I had a ton of if statements
before, decided to go to switch to make it faster but I'm hitting the
same type of problem.

Document:
https://drive.google.com/file/d/0B5S-XtOUGGH5UmpBdnIzUUM3OFE/view?usp=sharing

Click "Create Ledger" and you'll see the macro begin.

Problem: Look at sheet "Ledger" under header "Date" (B7:B10). At first I
thought those were right but they are not. Convert those to date, you'll
see that all of them are 12/10/14.

I literally have no clue where those are coming from. They should be:
B7: 06/26/2009
B8: 06/26/2009
B9: 06/27/2009
B10: 06/28/2009

(all taken from RawData sheet).

If you look at the macro (Sub fillData) you'll see that the pasteValue
gets Date(year,month,date), furthermore you can see that year, month,
date all get the correct values (uncomment the "print" values in the
switch).....

Terribly confused at this point, suggestions welcome.

Best,
Joel

Villeroy · December 10, 2014, 8:33pm

This is because you hack upon something you do not understand.
1) First of all you need to understand (almost) each and every aspect of
the spreadsheet component. Otherwise there is nothing to be programmed.
2) Then you need to be a fairly competent programmer in order to
comprehend this extraordinary complex office API.
3) You need to install an object inspector in order to find all methods
and properties you need.

Again, you set the (pseudo-)property String of a cell. Instead you
should set the cells Formula (pseudo-)property.
Regarding your second question: Spreadsheets do not know any "date
values". All values are doubles unless they are text. Dates, times,
booleans, currencies, percent are just formatted doubles.
Strings representing US-dates (M/D/Y) do not serve any purpose.
A cell object has a string, a value and a formula at the same time. The
number format has absolutely nothing to do with it.

Joel_Madero · December 10, 2014, 8:43pm

Well - this is why I came to the list - I'm not a developer by trade,
this is a hobby and just a single macro I'm working on. I don't have the
time to read hundreds of pages of documentation for a hobby as I do in
fact have a trade, and it eats up almost all of my time.

I'll try to figure out what you mean by what you said - worst case I'll
just give up the macro and stick with the limited abilities of kmymoney
to do graphs and what not.

Joel

Villeroy · December 10, 2014, 9:44pm

give up the macro and stick with the limited abilities of kmymoney

to do graphs and what not.

Amen!

Villeroy · December 10, 2014, 9:55pm

I'll try to figure out what you mean by what you said - worst case I'll
just give up the macro and stick with the limited abilities of kmymoney
to do graphs and what not.

At least we know know waht you are talking about.

https://docs.kde.org/development/en/extragear-office/kmymoney/details.database.html

Using a database, it would be possible to connect LibreOffice to the
kMyMoney's underlying database and query any record set you want from
that database. The record sets could be used to fill Base reports and
spreadsheets without intermediate export/import files and with no silly
Basic code.

Joel_Madero · December 10, 2014, 10:09pm

I extracted the kmymoney file and the info in it seems to be incredibly
conveluted, I'd be surprised if I could just bring it into base without
additional problems. Perhaps I'm terribly wrong though.

Best,
Joel

P.S. I'm trying to use this as a learning experience (without having to
become a basic professional)....just saying, this is supposed to be a
friendly/encouraging environment.

Villeroy · December 10, 2014, 10:31pm

I extracted the kmymoney file and the info in it seems to be incredibly
conveluted, I'd be surprised if I could just bring it into base without
additional problems. Perhaps I'm terribly wrong though.

Terribly wrong, indeed. You do not bring anything "into Base". You just
connect a Base file to an existing database by means of a vendor
specific database driver in order to use the connected database in
LibreOffice. No conversion. No translation. No import. No data copy.

You establish the connection, see the connected database in Base (which
does not store any data), build some meaningful queries and dump the
data into Calc or Writer.
To some extent this is development work too as outlined in the linked
kMyMoney manual. By default kMyMoney stores all data in some file which
is not a relational database and therefore is not accessible by external
tools. First of all you would have to reconfigure kMyMoney to store all
data in a relational database of your choice.

P.S. I'm trying to use this as a learning experience (without having to
become a basic professional)....just saying, this is supposed to be a
friendly/encouraging environment.

Without any knowledge you jump right into your own little software
project. Reading books or attending classes is the first step. Then you
may be able to handle .qif files and relational databases. "Learning by
doing" does not work with things like these.

pitonyak · December 10, 2014, 11:14pm

I can see many things that you have done wrong. Here are a few comments....

Your first problem... replace

pasteSheet.getCellByPosition(1,k).value = Date(year,month,day)

with

pasteSheet.getCellByPosition(1,k).value = DateSerial(year,month,day)

DateSerial returns a date built from the component parts, Date does not.

Also, you probably want to set the format to use a Date. I won't bother with a complicated specified Numeric format for this, but will provide this simple example:

Dim oFormats
oFormats = ThisComponent.NumberFormats
Dim aLocale As New com.sun.star.lang.Locale
pasteSheet.getCellByPosition(1,k).value = DateSerial(year,month,day)
pasteSheet.getCellByPosition(1,k).NumberFormat = oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale)

Now for a few other comments.

You have this big select case for some things. Consider this:

Day month and year area already defined as type integer. This means that those big case statements can be removed as follows:

                month = mid(currentCellValue, 5,2)
                year = mid(currentCellValue, 10,2)
                If (year < 10) Then
                  year = year + 2010
                ElseIf (year < 99) Then
                  year = year + 2000
                End If
                day = mid(currentCellValue, 2,2)

    Dim oFormats
    oFormats = ThisComponent.NumberFormats
    Dim aLocale As New com.sun.star.lang.Locale
                pasteSheet.getCellByPosition(1,k).value = DateSerial(year,month,day)
                pasteSheet.getCellByPosition(1,k).NumberFormat = oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale)

Hope this helps.

AndrewMacro.odt is a good place to find specific focused examples.
OOME_3_0.odt contains numerous examples, but is more focused towards learning.

Joel_Madero · December 11, 2014, 12:13am

Hey Andrew,

First - thanks for giving explicit examples of where I went wrong
I'll study your examples and learn for the future.

Second - as for the switch, I was actually trying to conceptualize
abstracting more and doing it per digit but I need to think about it a
lot more. Thanks for the pointer for the simple if. If I can abstract
then I can have one switch that takes care of day, month, and year.

Lastly, thanks for the pointer to the other examples. I certainly learn
by doing (I even have some patches submitted to the code...completely
learned by doing/guess and check/looking at examples).

Warmest Regards,
Joel

pitonyak · December 11, 2014, 1:49am

Hey Andrew,

First - thanks for giving explicit examples of where I went wrong
I'll study your examples and learn for the future.

Sometimes you need to specifically spelled out, I get that.... and your welcome.

Second - as for the switch, I was actually trying to conceptualize
abstracting more and doing it per digit but I need to think about it a
lot more. Thanks for the pointer for the simple if. If I can abstract
then I can have one switch that takes care of day, month, and year.

When possible, at least in other programming languages, I would simply provide the string along with a format specifier and perform the entire conversion in one shot. I don't know off hand if you can do a conversion by removing the "D". Certainly this might fail with two digit years. I managed to damage a bunch of data by allowing LO decide how to convert a two digit year.

Lastly, thanks for the pointer to the other examples. I certainly learn
by doing (I even have some patches submitted to the code...completely
learned by doing/guess and check/looking at examples).

Simple examples are a good way to start. Your entire set of macros is very ambitious for a person who does not do this professionally.