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!
I added the line:
ReplyDelete"UID=manager;PWD=password;" & _
after the driver parameter, which populates the log-in form that Sage displays, leaving the user to confirm only.
Good idea.
ReplyDeleteFor 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.
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?
ReplyDeleteI can replicate error 1004 if I specify an ODBC driver that does not exist on my system e.g. {Sage Line 50 v22}
ReplyDeleteHave you checked the exact spelling of the driver in your 32 bit Data Source Administration program?