?
Solved

Fill the data in excel to the visio from the template that i have.

Posted on 2008-10-28
13
Medium Priority
?
867 Views
Last Modified: 2012-06-27
hi,

Fill the data in excel to the visio from the template that i have.
Its a related Q from the Scott's solution
Pull data from Excel to visio and save thenm to html pages.

Regards
Sharath
0
Comment
Question by:bsharath
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 22822684
Sharath,

It will be a few days, but I'll let you know when I have something to look at. In the meantime, I'll need two things from you:

1) A template with fields boxes arranged as you want them (there's no hurry -- I can test with the default layout generated by my macro).

2) A spreadsheet that contains 5-10 rows of data. Please change names, employee numbers and any other personally identifiable data before uploading.

Scott
0
 
LVL 11

Author Comment

by:bsharath
ID: 22822736
Ok Scott i shall prepare them and post in a couple of days... :-)
0
 
LVL 11

Author Comment

by:bsharath
ID: 22840822
Scott one Q... Can i have the page size bigger that A4?
As the fields to arrange is very difficult with the available space...
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 11

Author Comment

by:bsharath
ID: 22842217
Please omit the above comments. I reduces the colums to make it perfect on what is required to to that.

Attached is the vsd that has the details.
Can you please make it look good when a print is taken with the lines straigh all size to match each other.
All in a line to match the grid lines.

Hope they resize as per the data in the cells so names or email addresses differ as they may be big. In such cases adjust accordingly.


Template.txt
0
 
LVL 11

Author Comment

by:bsharath
ID: 22852435
Scott i want the html to be names as the NTlogin. So can use them to be mailed.

Html file names as the colum that has the Ntlogin.
0
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 22866953
Very busy next couple of weeks but I will get back to this....

Regarding making the template look neat, I'll leave that up to you, largely because I don't know how you would like things aligned. You can use Visio's align function to ensure that everything is perfectly straight. Just select a group of shapes, the choose Shapes/Align from the main Visio menu. It will probably take some experimentation to get exactly what you want (undo is extremely use if you align things in a way that doesn't look good), but you can make things line up any way that you'd like. The new macro I'll provide  will fill in the text boxes on any template built from the first macro, so you'll always be able to move things around in the template and then run the "fill-in-the-blanks" macro again.

Re lining the arrows up with the boxes: If an arrow has come unglued from the shapes at either end, just select the arrow and you'll see either one or two green squares at the ends; green means that end is not glued; red means it is glued. For any that aren't glued, just drag the green square into the center of a rectangle and when the rectangle has a red outline, let go of the mouse -- now the end of the line will be red and it will be glued to the exact center of the rectangle.

You are correct that the text boxes will resize to accommodate the text. After we look at some pages created from the test data you send, you may need to move things around a bit, but we won't know that until we see you data on the pages.
0
 
LVL 11

Author Comment

by:bsharath
ID: 22867271
Ok Scott thanks
0
 
LVL 31

Accepted Solution

by:
Scott Helmers earned 2000 total points
ID: 22916810
The attached code doesn't create the html pages yet but it does create a Visio page for each row of Excel data using the template you provided.

Please let me know whether you would like any formatting changes: bold vs regular text, font size, size of text boxes, etc.

You never sent a workbook with additional sample data as I had requested above so I've just tested this with the workbook containing one row of data that you provided in the previous question. BTW, the code assumes the NTLogon is in column L and it processes spreadsheet rows until it encounters the first blank NTLogon.
Write-Excel-data-to-shapes-v1.txt
0
 
LVL 11

Author Comment

by:bsharath
ID: 22917021
Scott thank U

When i run the macro it asked me for the xls when selected it created the sheets with the NTlogins but no data got filled
0
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 22917097
It does work with the sample data that you sent in a previous question (I'be attached it here). It should work with any file that has exactly the same column names -- that how the data is matched to empty text boxes.
Excel-Sample1.xls
0
 
LVL 11

Author Comment

by:bsharath
ID: 22922038

Thank U Scott worked perfect...
Just realized that my data is in row 2 and has few more colums at the end newly added and a spell mistake in one of the headers
After changing all the 3 i got it working

I want the Size of the Data to be 1 size smaller that the headers at present its too small.

Posting a new Q for the Saveas Html now...

Will the macro not work if the header data is changed?
If i add some more colums at the end of the last colum will it now work.

0
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 22922496
The macro will work with additional columns of data. It's written so that it reads across the header row until it reaches the last used column. However, if you've added columns since you created the template, you'll need to rerun the first macro to create title and text box shapes for the new columns.

Re font sizes: the title boxes have 8 pt text and the data boxes are 4 pt. Would you like the data boxes to be 7 pt or 6pt?
0
 
LVL 11

Author Comment

by:bsharath
ID: 22922519
Data boxes to 7 would be fine...
I have added the new colum in excel but will not need them in Visio. So you can go ahead with the next step on saveas
THX
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically someone asks me whether there’s a way to automatically convert all of the pages in a Visio drawing to PowerPoint slides. There have even been a few times when I’ve wanted to do that myself but I never really had enough incentive to figu…
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month11 days, 3 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question