Reading linked data into variables

[Randy]

I have an Excel 2003 spreadsheet with a simple table of server information:

Name: ServerMail7
IP: 10.124.3.12
etc...

Column A is the list of server names, column B is rack position.

In Visio 2007, I have succeeded in programmatically attaching the
spreadsheet, drawing a server in a particular spot, and autolinking
name-matched data from the spreadsheet to the server shapesheet.

Here's the problem:

Though I've written my VBA to use variables throughout, I currently have
static data hard coded into the variables. For instance:

dim nameServer as string
dim rackPosition as integer

nameServer = ServerMail21
rackPosition = 7

....and then the Sub puts a server image named ServerMail21 at rack position
7 in the diagram.

I have read and tried various uses of datarecordset, getdatarowIDs,
getrowdata, etc. but can't seem to get it to work (such as nameServer =
vsoData.getrowdata).

Can someone please help me out with a simple (wouldn't expect it to be more
than 4 or 5 lines) snippet that will go to the recordset, read row 1 column
1, make nameServer equal that, read row 1 column 2 and make rackPosition
equal that, <do my draw sub>, then go to row 2 column 1 and iterate through
the whole sheet?

I would post my current code and the attempts I've made, but I don't have
the code on my day-job PC.

Thanks for any and all assistance!

 

[Randy]

..

 

[David Parker]

I have an example of using the Link Data to Shapes function to locate shapes
on racks in my new book, if you are interested (
http://www.visualizinginformation.com/chapters/chapter04/chapter.aspx )

Each chapter has downloadable code (if you have the book), but the following
should help you:

Public Sub ReadData()
Dim drs As DataRecordset
Dim iDrs As Long
Dim iCol As Long
Dim iRow As Long
Dim ids() As Long
Dim rData() As Variant

'Need to get the correct DataRecordset
For iDrs = 1 To ThisDocument.DataRecordsets.Count
If ThisDocument.DataRecordsets.Item(iDrs).Name = "Sheet1" Then
Set drs = ThisDocument.DataRecordsets.Item(iDrs)
Exit For
End If
Next
If drs Is Nothing Then
Exit Sub
End If

'Filter by a value in a column
ids() = drs.GetDataRowIDs("[Equip Type] = 'Cable Tray'")

For iRow = 0 To UBound(ids)
rData = drs.GetRowData(ids(iRow))
For iCol = 1 To drs.DataColumns.Count
Debug.Print iCol, drs.DataColumns.Item(iCol).Name, rData(iCol -
1)
Next iCol
Next iRow

End Sub

 

[Randy]

David, thanks - I will dig into this tonight and see if I can make it work.

I actually just ordered your book - I can't find it locally, so used the
'net - if I'd had it this past weekend, my project would be done by now.

Thanks for throwing out the freebie to assist - much appreciated.

I have an example of using the Link Data to Shapes function to locate shapes
on racks in my new book, if you are interested (
http://www.visualizinginformation.com/chapters/chapter04/chapter.aspx )

Each chapter has downloadable code (if you have the book), but the following
should help you:

Public Sub ReadData()
Dim drs As DataRecordset
Dim iDrs As Long
Dim iCol As Long
Dim iRow As Long
Dim ids() As Long
Dim rData() As Variant

'Need to get the correct DataRecordset
For iDrs = 1 To ThisDocument.DataRecordsets.Count
If ThisDocument.DataRecordsets.Item(iDrs).Name = "Sheet1" Then
Set drs = ThisDocument.DataRecordsets.Item(iDrs)
Exit For
End If
Next
If drs Is Nothing Then
Exit Sub
End If

'Filter by a value in a column
ids() = drs.GetDataRowIDs("[Equip Type] = 'Cable Tray'")

For iRow = 0 To UBound(ids)
rData = drs.GetRowData(ids(iRow))
For iCol = 1 To drs.DataColumns.Count
Debug.Print iCol, drs.DataColumns.Item(iCol).Name, rData(iCol -
1)
Next iCol
Next iRow

End Sub

--
David Parker
Microsoft MVP (Visio)
http://bvisual.spaces.live.com
http://www.visualizinginformation.com

.