Managing External Data Sets

Information is stored in the DataConnection object

Try it!

1. 
   Open from the Samples directory Working With DataRecordsets.vsd.
   
2. 
   Use the Data Selector wizard (Data > Link Data to Shapes...) to import the data in Orgdata.xls data from the Samples folder. When the import is finished, the data is displayed in the External Data window.
   
3. 
   Run the macro to display the DataConnection object (View > Macro > InterestingMacros.GetDataConnectionObject). The connection string is displayed in the Immediate window. The code and the connection string are listed below.
   

'Displays the connection string from the DataConnection object

'for the most recently opened datarecordset

Dim vsoDataConnection As Visio.DataConnection

Dim vsoDataRecordset As Visio.DataRecordset

Dim iCount As Integer

iCount = ActiveDocument.DataRecordsets.Count

Set vsoDataRecordset = ActiveDocument.DataRecordsets(iCount)

Set vsoDataConnection = vsoDataRecordset.DataConnection

Debug.Print vsoDataConnection.ConnectionString

End Sub

Sample connection string for Excel data source:

Sample connection string for SharePoint list:

PROVIDER=WSS;DATABASE=http://name.officeisp.net/ShapingFuturez;LIST={05F6AF30-326C-4932-83C1-35BF2015F718};

Sample connection string for SQL Server:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost\sqlexpress;Initial Catalog=WhitePaperSamples

Add a DataRecordset

DataRecordsets.Add

Try it!

1. 
   From the file Working with DataRecordsets.vsd run the macro View > Macro > InterestingMacros.AddDataRecordsetFromConnectionObject. The macro will create two DataRecordsets from the sample data in ORGDATA.xls.
   
2. 
   View the code for this macro. The first time the Add method is used, the connection information is passed as a string. Upon completion of this first Add the DataRecordset object is created and its DataConnection object is established.
   

strConnection, strCommand, 0, "Org Data")

3. 
   The second time the Add method is used the DataConnection object previously created is passed as the parameter to the Add method. This creates another DataRecordset from the same data in ORGDATA.xls.
   

vsoDataRecordset.DataConnection.ID, _

strCommand, 0, "Org Data")

Tip:	Anytime a DataRecordset exists, there will be a DataConnection object containing the connection information for the data used to create the DataRecordset (except for connectionless DataRecordsets). This DataConnection can be used to create more instances of the same data.

DataRecordsets.AddFromConnectionFile

Public Sub AddFromConnectionFile_Example()

'Create a DataRecordset from an .odc file using the

'AddFromConnectionFile method

Dim strFile As String

Dim strName As String

Dim vsoDataRecordset As Visio.DataRecordset

strFile = "C:\Documents and Settings\System Admin\My Documents\" & _

"My Data Sources\Northwind Employees.odc"

strName = "Data from ODC"

Set vsoDataRecordset = _

ThisDocument.DataRecordsets.AddFromConnectionFile( _

strFile, 0, strName)

End Sub

Note:

An .odc file can be created in different ways including using tools in SharePoint and tools in Access and Excel. The .odc file used for this example was created with the Data Connection Wizard in Excel.

Try it! (optional)

1. 
   Use Excel to create an .odc file. This can point to any Excel file or easily accessible database such as Northwind.mdb. Note the path where the .odc file is saved.
   
2. 
   Modify the macro AddFromConnectionFile_Example in the file Working with DataRecordsets.vsd to create a DataRecordset based on the new .odc file just created.
   

DataRecordsets.AddFromXML

Adds a DataRecordset object to the DataRecordsets collection, and fills the resulting data recordset with data supplied in the form of an XML string.

In contrast with data recordsets created by using the Add or AddFromConnectionFile methods, data recordsets created by using the AddFromXML method are not associated with a DataConnection object.

Note:

Because DataRecordsets created using AddFromXML have no DataConnection object, they are referred to as a connection-less data source.

What's more, Visio never refreshes a data recordset you created by using the AddFromXML method automatically, regardless of the setting of the DataRecordset.RefreshInterval property. To refresh the data in such a data recordset, you must call the DataRecordset.RefreshUsingXML method.

Try it!

1. 
   From the file Working with DataRecordsets run the macro View > Macro > InterestingMacros.AddFromXML_Example. The macro will create a DataRecordset from the XML data defined within the macro.
   
2. 
   View the code. Following is the XML data used to create the DataRecordset. Compare this to the VBA code to see how the XML data is put into a string.
   

xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'

xmlns:rs='urn:schemas-microsoft-com:rowset'

xmlns:z='#RowsetSchema'>

rs:number='2' rs:nullable='true' rs:maydefer='true' rs:write='true'>

3. 
   Run the macro GetDataConnectionObject. This will trip the error handler when checking for the DataConnection object and display a message in VBA’s Immediate window.
   

Accessing data in the DataRecordset

When you import data, Visio assigns integer row IDs, starting with the number 1, to each data row in the resulting datarecordset, based on the order of the rows in the original data source. Visio uses data row IDs to track the rows when they are linked to shapes and when data is refreshed. If you want to access data rows programmatically, you must use these data row IDs.

Two important methods for retrieving information from DataRecordsets include GetDataRowIDs and GetRowData. Use GetRowData to retrieve all columns of data within a given row. Use GetDataRowIDs to retrieve a subset of IDs within the DataRecordset that satisfy a given criteria string.

A sample of how to use these methods is included in the following demonstration code.

Try it!

1. 
   Make sure a datarecordset exists and then run the macro GetDataRecords. This macro is a simple example of using GetRowData and GetDataRowIDs and writing the information to the VBA’s Immediate Window.
   
2. 
   Run the macro View > Macro > BuildOrgMacros.DoItAll. This macro builds an organizational chart. It creates a DataRecordset, drops shapes on the page and links them to the data records, drops connectors on the page and glues them between the employee and its Reports_To shape, modifies the data graphic that displays the organizational data, and then lays out and resizes the page.
   
3. 
   Look at the code in the macro AddDataRecordSet. This macro is used by the DoItAll macro. Note that it uses the DataRecordSets.Add method to create the datarecordset. It also sets the primary key field of the datarecordset.
   
4. 
   Look at the code in the macro DropManyLinkedShape. This macro is used by the DoItAll macro and sets up the DropManyLinkedU method to add a shape linked to each record in the datarecordset. Note that the data graphic is added at the same time the shapes are created.
   
5. 
   Look at the code in the macro AddAndGlueConnectors. This macro utilizes GetRowData and GetDataRowIDs to extract the needed information.
   

The key segment of code follows. Each record contains the data for one employee and in that record is the “Reports_To” field which gives us the name of the person they report to.

GetRowData is used to extract the fields from a row in the form of an array of variant data.

The data in the Reports_To field is used to construct a criteria string which is then passed to the method GetDataRowIDs to retrieve the ID of the record within the datarecordset of that employee.

An example criteria string is “Name = ‘Joseph Goldberg’”

Given the ID of this record we use GetShapesLinkedToDataRow to find the shape on the page that is linked to the row in the datarecordset.

This is enough information to give us the “from shape” and “to shape” so that we can add and glue the connectors.

'Get the shape id of the "Reports_To" shape

varRowData = vsoDataRecordset.GetRowData(ShapeRowID)

strReportsTo = varRowData(2)

'Find the datarecordset row that has this "Reports_To" data

'in the "Name" field

strCriteria = "Name = '" & strReportsTo & "'"

lngNameRowIDs = vsoDataRecordset.GetDataRowIDs(strCriteria)

'Find the shape linked to this data row

ActivePage.GetShapesLinkedToDataRow vsoDataRecordset.ID, _

lngNameRowIDs(0), lngShapeIDs

6. 
   The macro ModifyDataGraphicMaster modifies the default data graphic applied to the shapes to add another text callout that displays the employee’s “Title” information.
   
7. 
   The final step is in LayoutAndResize. This macro applies the Layout method to arrange the drawing and resizes the drawing page so that the entire organization chart fits on the page.
   

We will explore the other macros in this file later in this module.

Directory: download
download -> Performance Tuning Guidelines for Windows Server 2008 May 20, 2009 Abstract
download -> Northern England’s set-jetting locations
download -> Bbc archives: Beyond the programme download
download -> Occupational health and safety
download -> Dynatest 3031 lwd light Weight Deflectometer
download -> Forth coming competitive exams
download -> English Olympiad Tasks: 10 th form Аудіювання, 10 клас. Текст
download -> Brush High School Morning Announcements Friday, September 05, 2014 all school
download -> Year 9 The Arts — Music
download -> Years 7 and 8 standard elaborations — Australian Curriculum: Music draft

Download 0.78 Mb.

Share with your friends: