Viewpoint can deliver information to another application using Microsoft's OLE DB architecture. Basi- cally, OLE DB defines a series of rules that govern the interactions between one application that supplies data (like Viewpoint) and another application that wants to acquire data (like Excel). By setting proper- ties and using appropriate methods, Viewpoint can be "called" to run a view and return results to:

  • An Excel spreadsheet, or multiple tabs (sheets) in an Excel spreadsheet
  • (Excel 2007 and above–32-bit only)A Word document (Word 2007 and above)
  • Any other OLE DB compliant requester

Background

When an OLE DB request is made to Viewpoint, the necessary connections to the target IBM Power Systems (System i, iSeries, AS/400) host will be made. Depending on IBM i Access configuration op- tions, the user may be prompted to sign on to the host computer. If the SEQUEL object being run is a run-time prompted one, a prompt window will appear unless all the required variables are supplied with the request. Once the prompt is completed, the indicated view, table, or script will be run. If the SEQUEL object is a view or table, the results will be made available to the requesting document via an OLE DB rowset.

Viewpoint Samples

Making a request to Viewpoint with Excel or Word involves some simple macros. We have included a sample spreadsheet (OLESample.xls) and a sample document (OLESample.doc) in the Viewpoint direc- tory that you can use as a starting point. The files have been digitally signed and saved as "Recommend- ed Read-only" to ensure their integrity. You can familiarize yourself with the macro code by opening the sample documents and navigating to the macros by using the Tools \ Macro \ Visual Basic Editor (or Developer \ Macros in Excel 2010) from the menu. This will start the VB Editor so you can see and run the macros.

When creating your own OLE DB documents, make sure that the Microsoft ADO has been properly added to your environment by choosing Tools \ References from the Visual Basic Editor. Select the item named 'Microsoft ActiveX Data Objects 2.x Library' if it is not already selected.

We have documented and included the macro code below for your reference.

Using Viewpoint via Microsoft Excel—OLESample.xlsm

The following code runs the DDCUSNO view from the SEQUELEX library. The DDCUSNO view is a run-time prompted view that prompts the user for a sales region before returning results for that region. All of the code to retrieve data is stored in the GetData macro. The macro is set to be run automatically through its inclusion in the Document_Open subroutine. The GetData subroutine is listed below. You can include it in any Excel spreadsheet that needs to make Viewpoint requests.

Public Sub GetData()
On Error Goto ErrOut:
Dim myConn As ADODB.Connection 
Dim myRS As ADODB.Recordset 
Set myConn = New ADODB.Connection 
myConn.ConnectionString = "Provider=SEQUEL ViewPoint;" 
myConn.Open
Set myRS = New ADODB.Recordset 
myRS.Open "SEQUELEX/DDCUSNO", myConn 
Range("A2:G100").ClearContents Range("A2").CopyFromRecordset myRS 
Exit Sub
ErrOut:
MsgBox "ErrOut: " & Err.Description 
End Sub

If you want to tailor the subroutine, you can change the view name by changing the line that reads:

myRS.Open "SEQUELEX/DDREGON", myConn
Note: Without a system qualifier, the default system specified in your Viewpoint Options (Tools\ Viewpoint Options from the Explorer) is used. The following format qualifies a system:
myRS.Open “XXXXX/SEQUELEX/DDREGON”, myConn (where XXXXX is your Power i)

You can also run a script (no results will be returned).

If a run-time request is made without all the necessary variables supplied, Viewpoint will prompt the user for the missing values.

If you want to supply values and circumvent the prompt, you can pass them on the Open request by us- ing the /setvar command line switch. For example, to run the view and set the run-time variable value for &regon to 20, use a request like this:

myRS.Open "SEQUELEX/DDCUSNO /setvar((&regon 20))", myConn

The line 'Range("A2:G100").ClearContents', will clear all data from the range of cells A2 through G100. The next line 'Range("A2").CopyFromRecordset myRS' will copy the data that was retrieved in the myRS.Open command to the spreadsheet starting at cell A2. This completes the macro and data transfer from Viewpoint to the spreadsheet. The CopyFromRecordset method is only available with Excel2000 and later versions. Because of this, our sample workbook includes a Workbook_Open macro that tests the version and displays a warning message if the sample will not work.

Load multiple Excel Tabs—OLESAMPLE2.xlsm

Using a slightly modified version of the macro above you can write SEQUEL results to multiple work- sheets in an existing spreadsheet. Each worksheet can be customized with sophisticated formatting, graphs, formulas and color schemes, and more importantly, the customization only needs to be done once. The spreadsheet can be updated automatically with current data, requiring no further changes. It doesn’t matter if you run one view or a dozen, and place those results in a single worksheet or many unique worksheets, the Spreadsheet customization needn’t change.

This sample below places data into three separate worksheets (Customers, Sales and AR).

Public Sub GetData()
On Error GoTo ErrOut:
Dim myConn As ADODB.Connection 
Dim myRS As ADODB.Recordset 
Set sh = Sheets(“Customers”)
Set myConn = New ADODB.Connection 
myConn.ConnectionString = “Provider=SEQUEL ViewPoint;” 
myConn.Open
Set myRS = New ADODB.Recordset 
myRS.Open “sequelex/CUSTSBYSAL”, myConn 
sh.Range(“A3:h100”).ClearContents 
sh.Range(“A3”).CopyFromRecordset myRS

myConn.Close
myConn.Open

Set myConn = New ADODB.Connection 
Set sh = Sheets(“Sales”)
myConn.ConnectionString = “Provider=SEQUEL ViewPoint;” 
myConn.Open 
Set myRS = New ADODB.Recordset 
myRS.Open “sequelex/SALESAVG”, myConn
sh.Range(“a2:o200”).ClearContents 
sh.Range(“a2”).CopyFromRecordset myRS

myConn.Close
myConn.Open

Set my Conn = New ADODB.Connection
Set sh = Sheets("AR")
set myConn.ConnectionString = “Provider=SEQUEL ViewPoint;” 
myConn.Open
Set myRS = New ADODB.Recordset 
myRS.Open “sequelex/ARBYSALNO”, myConn 
sh.Range(“a2:g10”).ClearContents 
sh.Range(“a2”).CopyFromRecordset myRS 
Exit Sub
ErrOut:
MsgBox “ErrOut: “ & Err.Description 
End Sub

To target specific worksheets, the worksheet name can be hard-coded where needed, or a SET statement can be used to define a variable, which is used in place of the text. In the example above the variable sh is used for the worksheet name:

Set sh = Sheets(“Customers”)

which sets the value, and then sh is used on the Range statements:

sh.Range(“A3:h100”).ClearContents 
sh.Range(“A3”).CopyFromRecordset myRS

Using Viewpoint via Microsoft Word—OLESample.doc

This example runs the view DDREGON from the SEQUELEX library and places the results into a table in the word document. All of the code to retrieve data is stored in the GetData macro. The macro is set to be run automatically through its inclusion in the Document_Open subroutine. The GetData subroutine is listed below. You can include it in any Word document that needs to make Viewpoint requests.

Public Sub GetData()
Dim myConn As ADODB.Connection 
Dim myRS As ADODB.Recordset 
Dim s As String
Dim i As Integer
'If uncommented, errors will be ignored 
'On Error Resume Next
'This will skip to ErrOut: when an error is encountered 
On Error GoTo ErrOut:
'This creates the connection to SEQUEL ViewPoint 
Set myConn = New ADODB.Connection
myConn.connectionstring = "Provider=SEQUEL ViewPoint;" 
myConn.Open
'This code runs the view DDREGON in the SEQUELEX library 
'returning the results as a standard ADO recordset. You can 
'change "SEQUELEX/DDREGON" to the library/view you want to open
Set myRS = New ADODB.Recordset 
myRS.Open "SEQUELEX/DDREGON", myConn
If myRS.EOF And myRS.BOF Then Exit Sub '''no records
'To turn this recordset into a table, we need to copy
'all the data into the document with tabs between the fields 
'we'll do this by first putting it into the string variable 's'
For i = 0 To myRS.Fields.Count - 1 
If i > 0 Then s = s & vbTab
s = s & myRS.Fields(i).Name 
Next i
s = s & vbCrLf
s = s & myRS.GetString(adClipString, -1, vbTab)
'We use a bookmark in the document to mark 
'where we want the table to be created
Range(Bookmarks(1).Start, Range.End).Text = s
'This is where all the text we put on 
'the document gets converted to a table
Range(Bookmarks(1).Start, Range.End).ConvertToTable
'Normal Termination 
Exit Sub
ErrOut:
'Show a message box with the Error DescrptionEnd 
Sub MsgBox "ErrOut: " & Err.Description

If you want to tailor the subroutine, you can change the view name by changing the line that reads:

myRS.Open "SEQUELEX/DDREGON", myConn
Note: Without a system qualifier, the default system specified in your Viewpoint Options (Tools\ Viewpoint Options from the Explorer) is used. The following format qualifies a system:
myRS.Open “XXXXX/SEQUELEX/DDREGON”, myConn (where XXXXX is your Power i)

You can also run a script (no results will be returned).

If a run-time request is made without all the necessary variables supplied, Viewpoint will prompt the user for the missing values.

If you want to supply values and circumvent the prompt, you can pass them on the Open request by using the /setvar command line switch. For example, to run the DDCUSNO view and set the run-time variable value for &regon to 20, use a request like this:

myRS.Open “SEQUELEX/DDCUSNO /setvar((&regon 20))”, myConn

Once the record set is opened, the macro copies the data into a table by copying it into the document and then using ConvertTextToTable operation. This completes the macro and data transfer from Viewpoint to the document.

 

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: October 12, 2018