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")
srCustomer.MoveFirst
Do
WScript.StdOut.Write srCustomer.Fields("Account_Ref") & Vbcrlf
srCustomer.MoveNext()
Loop until srCustomer.IsEof
WS.Disconnect

Posting a stock transaction in VB.NET

In order to manage stock levels within Sage, it is often necessary to record the movement of stock in an out of the company, by way of Stock Transactions.

Stock levels are managed automatically by Sage from the result of Sales or Purchases. However, these are not the only stock movements that can happen. Stock levels can be increased (Adustment In) with deliveries and returns. Stock levels can be reduced (Adustment Out) with non Invoiced sales and losses. Stock levels can also be reset using a stock-take.

The following code creates an Adustment In (i.e. delivery) of 2 units of the first product found. At a cost price of £1.

Dim objStockPost As SageDataObject50.StockPost
objStockPost = ws50.CreateObject("StockPost")
Dim objStockRec As SageDataObject50.StockRecord
objStockRec = ws50.CreateObject("StockRecord")
objStockRec.MoveFirst()
objStockPost.Header.Fields("Stock_Code").Value = objStockRec.Fields.Item("Stock_Code").Value
Dim objStockTran As SageDataObject50.StockTran
objStockTran = objStockPost.Items.Add()
objStockTran.Fields.Item("Stock_Code").Value = objStockRec.Fields.Item("Stock_Code").Value
objStockTran.Fields.Item("Date").Value = DateTime.Now.ToString("MM/dd/yyyy")
objStockTran.Fields.Item("Type").Value = SageDataObject50.StockTransType.sdoAI
objStockTran.Fields.Item("Quantity").Value = CType(2, Double)
objStockTran.Fields.Item("Cost_Price").Value = CType(1, Double)
objStockTran.Fields.Item("Reference").Value = "Test Ref"
objStockTran.Fields.Item("Details").Value = "Test Transaction"
' Update the StockPost object
Dim bFlag As Boolean
bFlag = objStockPost.Update()
If bFlag Then
MessageBox.Show("Success")
Else
MessageBox.Show("Failure")
End If