Greentree Excel add-in: Caveats with gtAPInvoice() and gtAPInvoiceLine()

This thread is now closed to new comments.
Some of the links and information provided in this thread may no longer be available or relevant.
If you have a question please start a new post.
Experienced User DigitalStefan
10 Posts
Experienced User
United Kingdom
Experienced User

10Posts

6Kudos

0Solutions

Greentree Excel add-in: Caveats with gtAPInvoice() and gtAPInvoiceLine()

This is a longer-than-I'd-like write-up of my experience creating our first, simple Excel tool to post data into Greentree.

 

Our first project was to provide a way to analyse and post a monthly, multi-line invoice from our telecoms provider from Excel into Greentree. An initial problem was finding the correct documentation for the Excel add-in, to which our support partner pointed me in the direction of a help file. We don't have a help file, as the help documentation moved to MYOB's website during our mid-2018 upgrade of Greentree. I managed to find it there under the 'Reporting' topic.

 

The second issue I then immediately ran into is that none of the Excel functions provided by the add-in have the usual pop-up context help that you expect to see when you're typing in a formula. This wouldn't be so bad, except for the fact that functions such as gtAPInvoiceLine() have 16 parameters, only a few of which are optional and none of which can be fully omitted. This makes development and maintenance more difficult than it should be.

 

Issue number three is that if you don't exactly enter each parameter correctly and using the exact data type expected, you risk receiving an opaque and unintuitive #VALUE! error. Some parameter errors give you descriptive feedback, but if you for instance omit the tax value in gtAPInvoiceLine() (you know, because you expect the tax value to be auto-calculated based on the tax type), up pops '#VALUE!'. (hint: Just enter zero as the tax value, it is then auto-calculated when posted).

 

The last annoyance is that there appears to be no exposure of the add-in menu actions within VBA. To actually post an invoice, you must interact with the add-in menu. It would have been nice if I could have included a button on the worksheet to perform the same function, but with perhaps a final validation check of the data prior to actually posting. Whilst gtAPInvoice() and gtAPInvoiceLine() do some validation, you get no warning that you're trying to post into a closed accounting period, for instance. Instead, you get a dialog box pop-up with an ugly error message. Not ideal if you're developing a spreadsheet designed to be distributed to users not already familiar with the add-in and its idiosyncrasies.

 

To maintain some level of sanity during development, I found it helpful to put invoice data within an Excel Table. "Format as Table" is not, as my boss thought, just pretty formatting. Adding the gtAPInvoiceLine() function within the table meant I was looking at:-

=gtAPInvoiceLine(rngAPInv,[@Co],[@[GL Account]],[@Net],0,[@Narration],"Exclusive",,,,[@Net],,,[@Tax],,[@Tree])

Instead of:-

=gtAPInvoiceLine(rngAPInv,$B10,$C10,$G10,0,$I10,"Exclusive",,,,$G10,,,$H10,,$D10)

One last little 'gotcha' is that the final invoice as posted into Greentree appears with its individual lines in a random order (it might not be random, but I couldn't immediately see the logic).

4 REPLIES 4
Ultimate Partner Mike_James
4,325 Posts
Ultimate Partner
New Zealand
Ultimate Partner

4,325Posts

1,897Kudos

0Solutions

Re: Greentree Excel add-in: Caveats with gtAPInvoice() and gtAPInvoiceLine()

Hi @DigitalStefan , thanks for posting your experiences and learnings. (I don't have anything to do with Greentree products, but one thing I would say is that picking a first project that writes transactions from Excel is not a simple task in any programming environment). The learning curve you have climbed is to be expected, and you have done very well to get it all together, and write it up so clearly for the benefit of others.


Regards, Mike (mike@datawise.co.nz)
DataWise Limited (www.datawise.co.nz), developers of:
DataWise Report Writer - Custom Reporting from MYOB programs
(Including AccountRight Classic/Live, and exo Payroll)

Experienced User DigitalStefan
10 Posts
Experienced User
United Kingdom
Experienced User

10Posts

6Kudos

0Solutions

Re: Greentree Excel add-in: Caveats with gtAPInvoice() and gtAPInvoiceLine()

Thanks Mike,

 

Hopefully there are other Greentree users out there who might find this useful. Perhaps all other Greentree users have large budgets for partner support and training, hence why there’s not a lot of free information available.

Experienced User DigitalStefan
10 Posts
Experienced User
United Kingdom
Experienced User

10Posts

6Kudos

0Solutions

Re: Greentree Excel add-in: Caveats with gtAPInvoice() and gtAPInvoiceLine()

On poking around a little further, it is possible to call the procedures within the Excel add-in, meaning I can add some functionality to remove the need to interact with the add-in menu.

 

I'll write this up a little later.

Experienced User DigitalStefan
10 Posts
Experienced User
United Kingdom
Experienced User

10Posts

6Kudos

0Solutions

Re: Greentree Excel add-in: Caveats with gtAPInvoice() and gtAPInvoiceLine()

Looks like my VBA was a little bit rusty, which explains why I thought I couldn't call functions from the add-in from my code.

 

However, it's very straightforward.

 

Four steps:-

  1. Open VBA editor.
  2. Open 'Tools' menu.
  3. Select 'References'.
  4. Tick "GreentreeExcelAddin".References.png

You're now ready to be able to call add-in functions from your project.

 

The add-in is pasword protected, so you can't go and look at the code. You can, however, look at the functions and methods available by selecting "GreetreeExcelAddin" within the project browser tree and then presssing F5. Up pops a list of interesting things.

 

What I did was add...

Private Sub Workbook_Open()
    Call GreentreeExcelAddin.UserSetup
    Call GreentreeExcelAddin.Refresh
End Sub

Within my project. This pops up the login dialog upon loading the spreadsheet. I haven't yet figured out how to query the add-in to determine if the database is already open. 99% of the time though, it won't be (for our use-case anyway).

 

I then added a button within the worksheet and attached it to...

Public Sub btnPost()
    Call GreentreeExcelAddin.UpdateAPInvoices
End Sub

... which is very straightforward, but I will flesh this out to do some data validation of the invoice prior to posting, as I want to avoid error pop-ups from the add-in.

Didn't find your answer here?
Try using advanced search to find a post more easily Advanced Search
or
Get the conversation started and make a new post Start a Post