Creating over 50 pages from data in Excel Document

[cstewart28]

I need to create network maps for over 50 IP video cameras.  I have all the data in the Excel document, but need to figure out how to pull it out since it is not really tied to a specific shape.  I have 3 different network maps that I have put out as background pages.  I then made 3 templates with text and pictures where I need the information filled in at. 

I made a macro that copies all the data from one of the templates to a new file and then adjusted the macro to work on a button click action.  Its crude but it works. 

I need help figuring out how to get the Excel data into the correct items.  Each item is a column in the Excel files, CameraID, CameraDescription, etc. So I need to connect to the excel file and based on the connection query like where background equal 2 (using the template with background 2).  Make a new page for each new row based on CameraID.   


   Dim strPage As String
    strPage = "1002"
   
    Dim CameraID As String
    CameraID = strPage
   
    Dim CameraDescription As String
    CameraDescription = "CCTV #21 - Raceway Body/Eng.Ctr"
   
    Dim Location As String
    Location = "Body Shop"
   
    Dim IDF As String
    IDF = "IDF Cabnet"
   
    Dim SWNumber As String
    SWNumber = "Switch Number"
   
    Dim PortNumber As String
    PortNumber = "23"
   
    Dim IDFCabnet As String
    IDFCabnet = "Cabnet 2"
   
    Dim PatchPanel As String
    PatchPanel = "Patch Panel"
   
    Dim PatchPortNumber As String
    PatchPortNumber = "23"
   
    Dim PIQEVNumber As String
    PIQEVNumber = "QEV1131AGYDGS"


Thanks for any help, I have attached the vsd file, so you can see what I'm trying to do.

[Yacine]

Hi cstewart,
It is not clear how your scenario is supposed to work. Create all three pages for each row, or is a specific template linked to a type of row. I couldn't find a matching column in your data.

Anyway, your problem is to extract the fields of the different columns of each row and distribute them over the drawing.
You could do this by using simple and honest VBA code.
I opted however for a tool I wrote some time a ago (http://visguy.com/vgforum/index.php?topic=6318.msg25957#msg25957).
This simplifies the extraction of the data.
One could also have used annotation shapes.
The key is to have one shape associated to one row in the data table.
I showed the process in a small video on youtube (https://youtu.be/Y9nH38RrMhE)

Next steps:
Either you create the pages manually as shown in the video, or you write a macro to iterate over all the rows of the data table, allocate a new value to the "data container", duplicate the template page, rename it.
You'll get all the necessary commands, if you record a macro while doing these steps manually.
I checked that all the commands are really displayed in the macro.

HTH and best regards,
Y.