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

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!


  1. I added the line:

    "UID=manager;PWD=password;" & _

    after the driver parameter, which populates the log-in form that Sage displays, leaving the user to confirm only.

  2. Good idea.

    For those that haven't used connection string password settings before, it is a good idea to always check that the QueryTable SavePassword property is set to True, otherwise Excel doesn't remember the settings! Careful that you're not opening up a security risk with this - once someone is in the Excel file (read only) they'll also know how to access the data in Sage.

  3. When I try this I get either of 2 errors ... in VBA run time I get a popup with an error 400 and when run from the spreadsheet macro I get error 1004 ... Can anyone help with these errors please?

  4. I can replicate error 1004 if I specify an ODBC driver that does not exist on my system e.g. {Sage Line 50 v22}

    Have you checked the exact spelling of the driver in your 32 bit Data Source Administration program?