Thursday, 27 November 2008
Connecting to SAGE using VBA
I'm often asked if Sage Data Objects can be used with VBA (Visual Basic for Applications), for embedding into excel spreadsheets, and the like. The answer is of course, yes. As with all development with VBA, you loose the luxury of Intellisense, so development is alot harder than when using strongly-typed languages like VB.NET.
But in a sense, it could be considered somewhat easier than languages that, one could say, are too strongly typed, such as C#. With it's refusal to support Duck-typing, that is almost vital in many instances to avoid the use of reflection.
The below example can be used to print a list of customers registered in Sage. The below code snippet should be saved as "customers.vbs", then it can be run at the command line by typing "cscript customers.vbs".
Datapath = "C:\Line50\ACCDATA\"
Set SDOEngine = CreateObject("SDOEngine.5")
Set WS = SDOEngine.Workspaces.Add("Sage")
WS.Connect Datapath, "manager", "", "Sage"
Set srCustomer = WS.CreateObject("SalesRecord")
WScript.StdOut.Write srCustomer.Fields("Account_Ref") & Vbcrlf
Loop until srCustomer.IsEof