Create Visio pages from Template (1st page) and add text from large spreadsheet

[Zodiac69]

I have an Visio file where page one is a "Template" - All the pages will have the exact same layout.
Each page has 50 points where the text need to be placed based on the spreadsheet.
I need to create 100 identical pages.

What is the best way to approach this from VBA instead of the "Manual" way?

[wapperdude]

There are basically two methods: one add/create all the pages 1st and then loop thru the pages and add Excel data; or, 2nd, add Excel data to each page after it is added.  I prefer the first method as it only requires calling the Excel file once.

To add the duplicate pages, the following code uses the duplicate page method.  The number of pages is hard coded and must be set before running the macro.

A 2nd macro will be needed to loop thru all the pages, inserting the Excel data as needed for each page.  This macro is not provided here.

Code Select Expand


Sub DupPg1()
'To preserve page order, duplication proceeds with each succeeding page
    Dim PgName As String
   
    For I = 1 To 5                'Change the 5 to number of desired pages less 1.  Page 1 already exists.
        PgName = "Page-" & I
        ActiveDocument.Pages.ItemU(PgName).Duplicate
    Next

End Sub


[Zodiac69]

Hi wapperdude

Thank you, that worked, but only once...
I presume that "Page-1" index number changed after i ran my "loop" so ActiveDocument.Pages.ItemU(1).Duplicate did not work when i ran the macro again.
Also the new pages were in the wrong order - last to first - the way the 1st page was duplicated.

To fix that, i did the following:

PgName = "XXX_YYY-" & i
        ActiveDocument.Pages.Item("Page-1").Duplicate  'use the 1st page name
        ActiveDocument.Pages.Item("Page-" & i + 1).Name = PgName ' rename the page
        ActiveDocument.Pages.Item(PgName).Index = i + 1 ' set the page index to have them in the correct sequence


On the 2nd macro, reading an excel file with 45 columns and 100 rows, i am still stuck.
1. How do i open the file and load the data - B2:AT101
2. How do i then loop row for row?

I have been trying to look at examples online, but i am not finding what i am looking for.
Might even be looking at the correct example, but with my limited VBA knowledge, i am might be staring at the solution...

[Surrogate]

On the 2nd macro, reading an excel file with 45 columns and 100 rows, i am still stuck.
1. How do i open the file and load the data - B2:AT101
2. How do i then loop row for row?

What do you meas as "load the data - B2:AT101" ?

[Zodiac69]

Hi Surrogate

I have one excel spreadsheet with "text" from B2 to  AT101.
Each row, lets take the 1st row (2) B2:AT2, has text i want to place on the Visio sheet.
Each row represent one Visio Sheet.

I can create sheets (Page) with the code supplied by wapperdude.
I can "manually" place text in the correct x/y locations on the sheet.

What i am trying to do is, this might be where i am missing something..,
1. Read the spreadsheet into an "Array"
2. loop thru the array, row by row, and place the text on the Visio sheet.
3. Create the next page,place next row text on the sheet until the last row.

I should then have 100 pages in my Visio files. Each page layout is identical except for the text.
The part i don't understand is:
a. How to load the spreadsheet cells B2 to AT101 into an "array"
b. Once the data is in an "array", do you just loop sequential thru the 4,500 cells one by one? 

[Surrogate]

2. loop thru the array, row by row, and place the text on the Visio sheet.

More than 10 years ago I wrote and published code that arranged text blocks looks like table.
I hope you will find it useful

The part i don't understand is:
a. How to load the spreadsheet cells B2 to AT101 into an "array"
b. Once the data is in an "array", do you just loop sequential thru the 4,500 cells one by one?

You can read Excel's range

Code Select Expand

Sub nn()
Dim rng As Range
Set rng = ActiveSheet.Range("B2:AT101")
Dim arr(100, 46) As Variant
For r = 1 To 100
For c = 1 To 45
    arr(r, c) = rng.Cells(r+1, c+1)
Next c
Next r
End Sub

[Zodiac69]

Hi Surrogate

Thank you for the code.
I am running into an error - it seems that "Range" is not available.
"Dim rng As" Range is not available to me.

I am using "Microsoft Visio Plan 2"
VBA is 7.1

[Surrogate]

I am running into an error - it seems that "Range" is not available.
"Dim rng As" Range is not available to me.

Visio have not this object, it is Excel's object. I wrote my code in Excel, right now i have not Visio here...
You need add few lines for run this code from Visio App.

Code Select Expand

Sub nn740()
Dim ea As Object ' Excel.Application
Dim ew As Object ' Excel.Workbook
Dim es As Object ' Excel.Sheet
Dim rng As Object ' Excel.Range
Set ea = GetObject(, "Excel.Application")
Set ew = ea.ActiveWorkbook
Set es = ew.ActiveSheet
Set rng = es.Range("B2:AT101")
Dim arr(100, 46) As Variant
For r = 1 To 100
For c = 1 To 45
    arr(r, c) = rng.Cells(r + 1, c + 1)
Next c
Next r
End Sub

[Zodiac69]

Hi Surrogate

Thank you, did a basic test with "Debug" to print the cell values, and this is working for me.
I shall now work this into my "basic" code and test.

[wapperdude]

@Zodiac69:   The macro I provided was, conceptually, once and done.  The presumption was only Page-1 as present. If it is run a 2nd time, then, things would get messy.  Haven't tried it, but what seems likely., using a 5 page example.
First run:  adds pages 2 thru 5, in proper order. 
Second run: 
@count = 1, grabs 1st page, duplicates it, duplicated page is inserted between page 1 and 2, and has name Page-6
@count = 2,  grabs Page-2, by name, not by position, duplicates it, placing it after Page-2, and before Page-3, and has name Page-7
etc
etc.

At least, that is how I believe it would work. Will try it later.  But, glad you were able to get that aspect working. 

Another approach to your scenario would have been:  manually duplicate page 1 and move this duplicate to be last page, noting its name.  For the ppresent example, that would've been Page-6.  Now, edit the macro, such that the counter begins with 6, not 1, and change end count, to say 9.  Run macro.  That should add pages 7 - 10 as duplicates of Page-6, and preserve the order.

ED:  Tested and confirmed that the above is true.  Attached, the 5 page example with 3 consecutive macro runs.

[Zodiac69]

Hi wapperdude / Surrogate

Thank you for all your patience and assistance.
With your help i have seen "How To" and it has put me on a path forward.

I can now work on refining the macro's and clean-up the messy parts i have been using.