News:

BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

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

Started by AlexHP, October 09, 2015, 06:06:00 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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 ?
Quote from: AlexHP on October 09, 2015, 06:06:00 PM
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
Quote from: Surrogate on October 01, 2015, 01:10:01 PM
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

#4
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.
Yacine

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.
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

Quote from: AlexHP on October 12, 2015, 02:56:48 PM
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...

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
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!  ;D ;D  ;D

Alex...

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 374 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1227KB.
Tokens: post-login.
Cache hits: 14: 0.00193s for 26,766 bytes (show)
Cache misses: 3: (show)
Queries used: 15.

[Show Queries]