How to change a visio shape text to the content of a range stored in a variant?

[Michael Ziegler]

Hello I'm trying to store the contents of a excel range into a variable and then add the content of the range to the text of a shape in visio. If I use a position like cel (1, 1) it adds the content of the position to the name but I need to add the entire content of the range for the active worksheet to the shape name. Here is a snippet of the code.

Dim cel As Variant


    cel = Range("A5:A85")




    For i = 2 To sht_count - 1


        With Worksheets(i)                       


            Worksheets(i).Activate               




                Set node = application.ActiveWindow.Page.Drop(Application.DefaultRectangleDataObject, aoffset, boffset)


    
                node.Text = ActiveWorkbook.Worksheets(i).Name + cel

I need to be able to display on the shape the entire content of the variant instead of one of the positions. Thanks

 

[Fluff]

Maybe something like this

Sub MichaelZiegler()
   Dim ary As Variant, Txt As String
   ary = Range("A5:A85").Value2
   Txt = Join(Application.Transpose(ary), ", ")
End Sub

This will put the data into a comma separated text string

 

[Michael Ziegler]

Maybe something like this

Sub MichaelZiegler()
   Dim ary As Variant, Txt As String
   ary = Range("A5:A85").Value2
   Txt = Join(Application.Transpose(ary), ", ")
End Sub

This will put the data into a comma separated text string

Thanks

 

[Fluff]

You're welcome