How Do I Create a Table and Show/Hide Rows?

[AlexHP]

So here's what I am trying to do...

I want to have a couple of tables with 50-75 rows containing information referencing items elsewhere in the document. Using VBA, I will make certain selections which will end up hiding certain rows in the table. Then if I reset the table using VBA it will show all the rows. I know I can do this in Excel (I already have this set-up and copy/paste those tables into Visio) but do not want to continue using the Excel tables in the Visio document. Can anyone help me out? Thank you in advance...

Alex...

[Surrogate]

Alex, where you want get data for these table ?

I want to have a couple of tables with 50-75 rows containing information referencing items elsewhere in the document.

if you want collect properties of some shapes, may be Shape Reports can be useful

If as segments you can use Shape Reports feature

[Noisy Cricket]

Yep.... sounds like shape reports can help you there.

[AlexHP]

Actually, I will be filling the rows manually, with information that doesn't change, and only need to be able to hide the rows that don't apply to the particular document. These tables will be in a template document that will be modified for each new document that uses it. So I am setting up standard generic items in the document (it's a wiring diagram) that can be hidden when they don't apply. I would rather not reference another document or source so we don't have to worry about losing the other document. This is why we want to eliminate using an Excel table being inserted into the document. Thank you.

Alex...

[Yacine]

Sorry to come back to the excel solution. An Excel table inserted in a drawing doesn't need to reference an external file.
The advantage of using Excel stays, that you don't need to implement by yourselves all the comfort functions excel provides already.

[AlexHP]

Thanks, Yacine. I was thinking that would end up being the solution but I was hoping to use something native to Visio. That being said, how do I reference the table and show or hide the table rows? I know how to do this in Excel but I would assume that there is more to it here... this being Visio after all...  Thanks!

Alex...

[AlexHP]

OK, folks, so if I use an Excel table in Visio and want to hide or show rows using VBA, what is my code going to look like? I still can't figure out how to properly reference this item and use something like ".Rows("3:3").Hidden = True" to hide the row. Please help!!! Thank you.

Alex...

[Surrogate]

are you have fixed excel range, for example N rows x M columns ?
and for some conditions rows can hide ?

[AlexHP]

Yes, the rows and columns would be fixed. And then there would be some condition or conditions which would cause specific rows to show or hide. I can come up with the logic for the conditions. I just need help figuring out how to show or hide the rows based upon these certain conditions. Any help would be great. Thanks!

Alex...

[Surrogate]

This code generated table for russian bill of material.

Code Select Expand

Sub Draw_Table()
' this code originally posted at post http://visio.getbb.ru/viewtopic.php?f=15&t=233
Dim Mast As Master
Dim ololo As Shape
Dim ooo As Window
Dim x As Integer
Dim y As Integer
Dim r As Shape
Dim cn As String
Dim rn As String
Const rh = 0.31496063 ' высота строки 8 мм в дюймах
Dim xc(9) As Single
xc(0) = 0
xc(1) = 0.787401575 ' ширина столбца 20 мм в дюймах
xc(2) = 5.905511811
xc(3) = 8.267716535
xc(4) = 9.645669291
xc(5) = 11.41732283
xc(6) = 12.20472441
xc(7) = 12.99212598
xc(8) = 13.97637795
xc(9) = 15.5511811
n = 1
Dim rect As Shape
Dim rw As Shape
Dim rs As Selection
Dim piny As String
piny = "=0 mm"
Set ooo = ActiveWindow
For y = 1 To 30 ' Цикл по созданию 30 строк
Set rs = Nothing
Set rw = ActiveWindow.Shape.DrawRectangle(0, 0, 0, 0) ' рисуем прямоугольник нулевой ширины и высоты
rnm = "pos" & y ' определяем имя текущей строки как pos + номер строки
rw.Name = rnm ' присваиваем полученное имя строке
ActiveWindow.Selection.ConvertToGroup ' преобразуем шейп в группу
rw.OpenDrawWindow.Activate ' входим внутрь данной группы
Set ooo = ActiveWindow

For x = 1 To 9 ' начинаем заполнять строку шейпами-столбцами
tn = y & "." & x '  имя текущего шейпа-прямоугольника, состоит из номера строки.номера в строке
tx = xc(x - 1) ' левая координата прямоугольника
bx = xc(x) ' правая нижняя координата прямоугольника
ty = 0 + rh * (y - 1) ' нижняя координата прямоугольника
by = 0 + rh * (y) ' верхняя координата прямоугольника
Set rect = ooo.Shape.DrawRectangle(tx, ty, bx, by) ' рисуем прямоугольник с полученными ранее координатами
rect.Style = "None" ' отключаю стили
rect.CellsSRC(visSectionObject, visRowXFormOut, visXFormLocPinY).FormulaU = "Height*1" ' ставим привязку PinY прямоугольника по верхнему краю шейпа
rect.CellsSRC(visSectionParagraph, 0, visSpaceLine).FormulaU = "8 mm" ' делаем расстояние между строками текста прямоугольника 8 мм
rect.AddSection visSectionUser ' добавляем секцию User
rect.AddRow visSectionUser, visRowLast, visTagDefault ' добавляем строку в секцию User
rect.CellsSRC(visSectionUser, 0, visUserValue).FormulaU = "=user.row_1.prompt*INT(TEXTHEIGHT(TheText,Width)/8 mm)*8 mm" ' забиваем значение в поле Value
rect.CellsSRC(visSectionObject, visRowText, visTxtBlkVerticalAlign).FormulaU = "=if(height=8 mm,1,0)" ' забиваем значение в поле Value
rect.CellsSRC(visSectionObject, visRowText, visTxtBlkTopMargin).FormulaU = "0 pt" ' ставим нулевой отступ сверху в тексте шейпа
rect.CellsSRC(visSectionObject, visRowText, visTxtBlkBottomMargin).FormulaU = "0 pt" ' ставим нулевой отступ сверху в тексте шейпа
rect.Name = tn ' присваиваем шейпу полученное ранее имя, состоит из номера строки.номера в строке
rect.Text = y & "." & x ' вписываем в шейп текст, который состоит из номера строки.номера в строке
rect.CellsSRC(visSectionUser, 0, visUserPrompt).FormulaU = "if(strsame(shapetext(thetext),"" ""),0,1)" ' забиваем значение в поле Prompt
Next x
Dim tex As String
ooo.Close
Application.ActiveWindow.Selection.UpdateAlignmentBox ' производим выравнивание размеров группы, в соответствии с размерами дочерних шейпов
hrow = "=guard(max(sheet." & n + 1 & "!user.row_1,sheet." & n + 2 & "!user.row_1,sheet." & n + 3 & "!user.row_1,sheet." & n + 4 & "!user.row_1,sheet." & n + 5 & "!user.row_1,sheet." & n + 6 & "!user.row_1,sheet." & n + 7 & "!user.row_1,sheet." & n + 8 & "!user.row_1,sheet." & n + 9 & "!user.row_1))" ' вычисляем максимальную высоту дочернего шейпа
If n > 10 Then piny = "=guard(sheet." & n - 10 & "!piny-sheet." & n - 10 & "!height)"
rw.CellsSRC(visSectionObject, visRowXFormOut, visXFormHeight).FormulaU = hrow
rw.CellsSRC(visSectionObject, visRowXFormOut, visXFormLocPinY).FormulaU = "Height*1"
rw.CellsSRC(visSectionObject, visRowXFormOut, visXFormLocPinX).FormulaU = "Width*0"
rw.CellsSRC(visSectionObject, visRowXFormOut, visXFormPinY).FormulaU = piny
n = n + 10
Next y
Application.ActiveWindow.SelectAll
ActiveWindow.Selection.Group
End Sub I have code that fill this table datas from excel work book.
There is one condition for hide row - if cells in row have cells without text

[AlexHP]

Surrogate, thanks for the help. Unfortunately, I don't think this will do what I need done. I don't need to import any data. I will have 2 tables with static (non-changing) information in them. I can either insert a blank Excel table or I can use the grid tables from the shapes menu. Either way, I will fill it manually with the data. What I need to be able to do is hide rows that I don't want to see in the particular document based upon certain criteria. So whether I use Excel tables or a grid table, can you (or someone) help me with show/hide commands for the individual rows? Thanks!

Alex...

[Yacine]

Thanks, Yacine. I was thinking that would end up being the solution but I was hoping to use something native to Visio. That being said, how do I reference the table and show or hide the table rows? I know how to do this in Excel but I would assume that there is more to it here... this being Visio after all...  Thanks!

Alex...

Code Select Expand

Sub setRows()
    Dim objXL As Object
    Dim OLEo As OLEObject
   
    For Each OLEo In ActiveDocument.OLEObjects
        If Left(OLEo.ProgID, 5) = "Excel" Then
            If OLEo.Shape.Name = "myXLS" Then
                Set objXL = OLEo.Object
                With objXL.Sheets(1)
                    If .rows("3:3").Hidden = True Then
                        .rows("3:3").Hidden = False
                    Else
                        .rows("3:3").Hidden = True
                    End If
                End With
                Set objXL = Nothing
            End If
        End If
    Next OLEo
End Sub

make sure to rename the excel shape to "myXLS" to get the code running.
I chose for this simple example to hard code it, but you might consider storing this name somewhere else (shape data of the page?)

HTH,
Yacine

[AlexHP]

Yacine, Thank you SO much!! That works great! I modified it a little to work in the application I have written but for the most part I used what you wrote. Great job!   

Alex...