Dropped Pennies: July 2013

Friday 19 July 2013

DSN-less ODBC connection for Sage

Sometimes you would prefer to have a DSN-less ODBC connection to your Sage data.

In Excel 2003, if you execute the following vba snippet, you'll get a DSN-less connection to your Sage demonstration company and a QueryTable containing the company name of the data at that location.

ActiveSheet.QueryTables.Add( _
Connection:="ODBC;" & _
"Driver={Sage Line 50 v18};" & _
"DIR=C:\ProgramData\Sage\Accounts\2012\DemoData\accdata", _
Destination:=Range("A1"), _
Sql:="select Name from Company").Refresh

You should edit the snippet where it is highlighted in yellow to reflect your Sage version (e.g. Sage 2010 = v16, Sage 2011 = v17, Sage 2012 = v18, Sage 2013 = v19, etc ...)

Once the QueryTable is created, you can edit the datapath in the Connection Dialog to access other company data and edit the query to access other tables.

Happy browsing!

Thursday 18 July 2013

Sage ODBC schema

I've found this to be a very useful resource. Respect to Rick!

http://rickvause.com/post/23345383645/sage-line-50-odbc-schema#disqus_thread

Tuesday 16 July 2013

Sage Charts of Accounts by Business Type

Maybe I'm missing something but I'd have thought it would have been relatively easy to see details of the various Charts of Accounts supplied as standard with Sage accounting products. I haven't been able to find any so I went through the process of taking a backup and then completely destroying my company data to load each of the twelve charts of accounts in succession and recording the results.

I hope you will find the contents of the attached PDF useful in deciding which Chart of Accounts, if any, will be best for you.

Monday 15 July 2013

Changing the font of OpenCart mail communications

Opencart has a "newsletter" email facility to communicate with customers. I recently used it for the first time and discovered that, whilst in drafting mode the font of the email text had looked something like Arial (acceptable to me), the text of the email was Times New Roman (I just can't like this font).

There then ensued a search for how to control the default font type of the outgoing email html. I don't want to have to hand code the source HTML for every communication.

I eventually found how to do it and share it here in the hopes that it will save someone else the long and frustrating search I had to locate something I would have hoped would be easily found online. Not so!

Locate the file admin/controller/sale/contact.php.
Go to line 230 in that file. The line reads: $message .= ' <body>' . html_entity_decode($this->request->post['message'], ENT_QUOTES, 'UTF-8') . '</body>' . "\n";
Change the text: '<body>' to '<body style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000;">' .
Save the file

The full line should read: $message .= ' <body style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000;">' . html_entity_decode($this->request->post['message'], ENT_QUOTES, 'UTF-8') . '</body>' . "\n";

Your communications should now be in the font of your choice by default.

Dropped Pennies

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel