Access/Visio VBA (A97 SR2+V2K)

Topic

New Reply

Hi

I am developing an Access database which links up with various Visio Office layout diagrams.

I have managed to use VBA to read all the Custom properties set up on the shapes for the PC’s and use them to update the tblPC table in the database.

However, I have got a problem in trying to update the Custom properties using VBA. Here is the code I am using :

‘
‘—————————————————————
‘Initialise Visio Variables
‘—————————————————————
Set objVSApp = New Visio.Application
objVSApp.Visible = False
Set objVSDoc = objVSApp.Documents.Open(strFileName)
Set objVSLayers = objVSApp.ActivePage.Layers
Set objVSShps = objVSApp.ActivePage.Shapes
DoEvents
‘
‘—————————————————————
‘Initialise Access Variables
‘—————————————————————
strSQL = “SELECT * FROM tblPC WHERE tblPC.FileName ='” & strFileName & “‘;” Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) Set rstTable = CurrentDb.OpenRecordset(“tblVisioProps”, dbOpenSnapshot) ‘
‘—————————————————————
‘Loop through all the shapes on the table & update the
‘Visio diagram with the values
‘—————————————————————
rst.MoveFirst
Do Until rst.EOF
Set objVSShp = objVSShps(rst!ShapeID)
intRowCount = objVSShp.RowCount(Visio.visSectionProp)
rstTable.MoveFirst
For intCellCount = 0 To intRowCount – 1
If IsNull(rst(rstTable!FieldName)) Or Len(rst(rstTable!FieldName)) = 0 Then
‘Do nothing
Else
Set objVSCell = objVSShp.CellsSRC(Visio.visSectionProp,
intCellCount, 0)
objVSCell.Result(Visio.visNone) = rst(rstTable!FieldName)
End If
rstTable.MoveNext
Next
rst.MoveNext
Loop

The line where it fails is :

objVSCell.Result(Visio.visNone) = rst(rstTable!FieldName)

It comes up with a type mismatch 13 error. The field is a text(255) field, and the value to be inserted is ‘Compaq’. Any ideas on how to get this one to work ?

All help much appreciated

Thanks

Nick

Reply | Quote

Replies

Visio will link to an Access back end. Unfortunately, the two programs don’t necessarily think the same way when it comes to data. Somehow, Result is not the property name I would expect to use to set a value, although it’s been years since I tried to exchange data between the two apps and much has changed in Visio since then. Are you sure you’re dealing with the right Visio property?

Reply | Quote

Charlotte

Whether I have got the correct porperty or not is a good question. I have been delving through help files, newsgroups, Google, everything I can find on the net, but I have not found the right answer yet

I have tried using :

objVSCell = rst(rstTable!FieldName) Type mismatch again
objVSCell.Formula = rst(rstTable!FieldName) #Name error even with quotes around it, and an equals sign
objVSCell.Value = rst(rstTable!FieldName) Object does not support this property or method

If you can suggest any more, I’d be happy to try them

Thanks

Nick

Reply | Quote

I don’t even have Visio on my current machine at home since I have mislaid the original Disk 1 and the upgrade won’t install on my newer machine without an installed version. Maybe someone else can help.

Reply | Quote