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

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Tuesday, 23 June 2020

Trouble with Time (Text ODBC driver)


I have a text file with Date and Time fields containing the values 01/01/2020,13:00:22

In Microsoft Query the values show as 2020-01-01 00:00:00 and 1899-12-30 13:00:22

When returned from MS Query to appropriately formatted columns in a Query Table they show as 01/01/2020 and 00:00:00

However, if I create a DateTime field as Date+Time it shows as 01/01/2020 13:00:22

How to get the Time to show correctly in the Query Table?

Time + 2 as [Time]

This shows as 13:00:22 in the Query Table. Took me ages to figure this out. I spent ages playing around with the DateTimeFormat option in the Schema.ini to no avail. It seems that if MS Query returns anything prior to 01/01/1900 00:00:00 the time portion will always show as 00:00:00 in Excel



Thursday, 16 February 2017

Excel 2016 version 1612 change

Following the release of the Microsoft Office 365 update to Excel 2016 version 1612 (January 25, 2017), there has been a change in Excel’s default behaviour in relation to PivotTable subtotals when a new field is created following a Group operation. Until now, the new field has been created with the subtotals set to “None”. Now, the new field will be created with the subtotals set to “Automatic”.


I haven’t checked whether the same change occurs with subscription versions of Excel 2013.

Friday, 23 September 2016

Windows 10 Anniversary Update (1607) and problems with Excel

Some Microsoft Windows 10 users have recently been reporting that their Microsoft Excel either crashes or hangs regularly, particularly after the Windows 10 Anniversary Update to version 1607 in August 2016.

The issue can often be traced to printer driver incompatibilities with Windows 10 which affect the correct operation of Excel. Correct operation of Excel can usually be restored by temporarily setting your "Default" printer driver to either "Microsoft Print to PDF" or "Microsoft XPS Document Writer" until you can obtain a Windows 10 compatible printer driver from your printer manufacturer.


HP Laserjet users can find the "Recommended Solution" for their printer model at http://h20564.www2.hp.com/hpsc/doc/public/display?docId=emr_na-c04675396.

Wednesday, 3 June 2015

[Microsoft] [ODBC Text Driver] Too few parameters. Expected:1

I had a complex query that returned the above error intermittently. It turned out that one of the fields was DATE (upper case) as specified in the schema.ini file for the text file. The query referred to Date (proper case). When the query was changed to refer to DATE (upper case) throughout, the intermittent failures stopped.

Tuesday, 7 April 2015

What does PivotCache.OptimizeCache do?

After much searching in vain I finally came across this nugget of an explanation:

PivotCache::OptimizeCache Optimize storage for fields with less than or equal to 255 items

It is buried in the innards of a file explaining changes introduced in Excel 97. Here's the link to the file if you want to see it for yourself: Excel 97 Product Enhancements Guide‎

I presume this means that a different internal indexing mechanism is used in the cache if a byte is big enough to reference all the unique data occurences in a field.

Wednesday, 4 March 2015

Automated email reminders for customers on Sage Line 50 / Sage Instant

The good thing is that, if you already have Excel, this will cost you nothing. The idea goes like this:

  • In the Memo field in the Sage Customer Record, start a dedicated line with something like "#Service date:" and then enter, say, 0630 for the 30th of June each year.
  • Run an Excel macro periodically to identify and automatically email customers where the Memo field indicates that the next service is due soon.

SMS messages are possible too, but you may need to pay for each message sent.

Please drop me a note if you are interested in the Excel file that will facilitate this functionality.



Thursday, 12 February 2015

Import into Sage from Excel to create a multi-line sales invoice in the Invoicing module

The standard response from most is that if you want to create an invoice in the Invoicing module, then the built in import routines do not allow it, so you'd need a 3rd party import program. You can import the invoice as a transaction to the Audit Trail using the Audit Trail Excel import template though.

However, there is a way to create an invoice in the Invoicing module using standard Sage routines - just not using the Excel import templates supplied by Sage. Sage help files outline the process whereby sales invoices can be created in either Sage Line 50 or Sage Instant here. Whilst this process requires Sage 50 Accounts Professional to raise a Purchase Order, instead, we can create a "virtual" Sage 50 Accounts Professional Purchase Order using only Excel.

A summary of the process is given below. In the summary, Company B is the company wishing to create multi-line invoices in the Invoicing module.

  • Company A (or someone in Company B) creates a virtual Purchase Order using Excel and sends it using Transaction email to Company B. Company B receives this as a Sales Order.
  • The Sales Order details are matched and updated.
  • Then Transaction Email automatically creates a Sales Invoice in Sage 50 Accounts / Sage Instant.
  • Company B sends this Sales Invoice to Company A using Transaction Email. Company A receives this as a Purchase Invoice.


The folks at Onion Reporting Software have a free Excel template to create the "virtual" Purchase Order needed for this process.