Import text as separate text fields

[ncnewbie]

I've got kind of a unique challenge and I haven't been able to figure out how to overcome it.  Hopefully someone here can help me...

I am doing space planning in Visio: I'm importing a .PNG map of a floor plan into Visio 2013, then I'm choosing "Text" (Ctrl+2) and typing the name of an employee.  I'll then drag that Text field to the office where the employee might sit, then I'm copying that Text field, renaming it to the next employee, then dragging it to the office where that next employee might sit.  Here is a video showing that process: http://screencast.com/t/UzjDYAxf

This works fine for small planning projects, but for large planning projects, I would like to be able to import the employee names from a spreadsheet, CSV, .txt file, etc. then drag those names to the proper location on the floor plan.  I'm having trouble figuring out how to do that.

When I try to select multiple cells in Excel and paste them into Visio, they all come in as a single text box, like this: http://screencast.com/t/ub7pxbBLDblT  I found a VBA macro that I can use to create Text Boxes in Excel, but when I select more than one Text Box and paste them into Visio, they come in as a single object that can't be separated, like this:  http://screencast.com/t/FIeMv8Lr7lI

So, what I'm hoping to find is a way to take multiple cells of Excel text, or a CSV or txt file, and paste or import it into Visio as individual Text fields that I can move around to various spots on the floor plan, as shown in the video at the end of the first paragraph.

Any insight would be most appreciated.

[Yacine]

I'd suggest that you use Visio's data binding capabilities.
Here's a small video to get you started: https://www.youtube.com/watch?v=ZSeLZ0ip_oI
So basically you're going to make a master, consisting of the text shape, you're already using, but instead of displaying plain text, it would display the value of a custom property ("Name").
Having saved this master either in stencil or the document stencil, select it, then just drag any row from the data window on the drawing area and visio will insert that selected master as template with the desired data (the name).

[wapperdude]

If you're not interested in maintaining a link to the Excel file, you can do simple copy / paste.   This might be a simple, quick solution.

I opened up a Visio drawing (V2007), and tiled it to left half of the screen.  Next, opened an Excel file, (Office 2010), tiled it to right half of screen.  This way, I can see both.

In Visio, I drew a rectangle, and placed it at random on the page.  Next, I went to Excel selected a populated cell and did copy.  Back to Visio, selected the rectangle, did paste.  Viola!  Text from Excel cell is now in the rectangle.  Then, I de-selected everything, and did a straight paste.  Visio creates a textbox and puts the Excel contents in it.  Once pasted, just grab with cursor and move to desired location. 

NOTE:  you can create some text in Visio, highlight (select) with the text tool, copy, then go to Excel window, select a cell, and paste text into the cell.  Again, no linking with this method.

Wapperdude

[Yacine]

@Wayne, clever! Like it very much.

[wapperdude]

Not nearly as nice as the video you you linked to...no pun intended, but, with V2007, I don't have that capability.  Must admit, that's  really nice...not enough to get me out of retirement though.  So, no real need to upgrade.

Wapperdude

[Yacine]

That's not a feature that was implemented after 2007, but rather in pro instead of standard.

[wapperdude]

Ah.  From the video, seemed to suggest V2010.  But, I only have standard anyways.  😵

W.

[miless2111s]

Building on the tile idea there seem to be two methods that have similar results
Copy and paste
As Wapperdude said with the simplification that having drawn the rectangle or text box there isn't any need to re-select when returning to Visio as it is already selected.
This method will result in the text boxes being in the same place on the page (where you originally drew it)

drag and drop
As before tile, draw a text box or rectangle and go across the excel and grab the cell to be transferred.  If you just drag it across the tiles into Visio it will be moved (i.e. disappear from excel) which might be useful if working your way down a long list.  It will also be dropped into the original location.  If you don't want it to Move then press control when doing the drag to copy.
Then deselect the text box in Visio (click on a blank space) and then repeat.  This time the text will be moved / copied to where you drop it which might be useful in your case.

Hope that helps

Miles

[ncnewbie]

If you're not interested in maintaining a link to the Excel file, you can do simple copy / paste.   This might be a simple, quick solution.

I opened up a Visio drawing (V2007), and tiled it to left half of the screen.  Next, opened an Excel file, (Office 2010), tiled it to right half of screen.  This way, I can see both.

In Visio, I drew a rectangle, and placed it at random on the page.  Next, I went to Excel selected a populated cell and did copy.  Back to Visio, selected the rectangle, did paste.  Viola!  Text from Excel cell is now in the rectangle.  Then, I de-selected everything, and did a straight paste.  Visio creates a textbox and puts the Excel contents in it.  Once pasted, just grab with cursor and move to desired location. 

NOTE:  you can create some text in Visio, highlight (select) with the text tool, copy, then go to Excel window, select a cell, and paste text into the cell.  Again, no linking with this method.

Wapperdude

Thank you very much for your response.  The problem with this method is that I have around 490 names to copy and paste into Visio (across 5 different floor plans.)  It would simply take too long to copy and paste each name from Excel.

I think the linking to a text field as suggested by Yacine may work for me, but I'm still trying to figure that out.

[miless2111s]

I've cobbled together some code based on another code set from this forum:

Code Select Expand


Sub PasteExcleCellsToVisio()
'modified from ORIGINAL CODE FROM http://visguy.com/vgforum/index.php?topic=816.0
  Dim xlSheet As Excel.Worksheet
  Set xlSheet = Excel.ActiveSheet
 
  '// Add a reference under Tools > References to:
  '// "Microsoft Visio 12.0 Type Library"
 
  '// Start Visio and open a blank doc:
  Dim visApp As Visio.Application
  Set visApp = CreateObject("visio.application")
  Dim visDoc As Visio.Document
  Set visDoc = visApp.Documents.Add("")
 
  '// Get the first page in thd document:
  Dim visPg As Visio.Page
  Set visPg = visDoc.Pages.Add
 
  Dim visShp As Visio.Shape
  For Each c In xlSheet.Range("names").Cells
    'If Abs(c.Value) < 0.01 Then c.Value = 0
    c.Copy
    '// Paste it into Visio:
    visPg.Paste
    '// The pasted object will be the last object in
    '// the shapes's collection
    Set visShp = visPg.Shapes(visPg.Shapes.Count)
  Next c
   
End Sub

Insert this into your excel sheet
Set the range "names" to cover only the names you want - it will error out if it finds a blank cell
run code which will open a new Visio document and stick all the names on top of each other
Drag one name out from the rest and then distribute the others between that point and the point you drag the first name to
copy into your floor layout

Hope this helps

Miles

[ncnewbie]

Thanks so much for your kind response.  Certainly never would have guessed someone would have written code for me...

Have a great day!

[miless2111s]

Thanks so much for your kind response.  Certainly never would have guessed someone would have written code for me...

Have a great day!

my pleasure - people on this site have helped me a lot so it's only fair I pay back into the community

[wapperdude]

Perhaps a little late, but I took an alternative approach...running the code from the Visio file.

The code asks you to browse for the desired Excel "source" file.  Once that's selected, it asks you to select the desired cells from the Excel file. Then, it places each cell entry into a separate Visio shape on the active page.  Finally, it closes the Excel file.

HTH
Wapperdude

Code Select Expand


Sub MyMac()
    Dim XlApp As Object
    Dim XlWrkbook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    Dim rng As Range
    Dim fNameAndPath As Variant
     
    Dim vsoCharacters1 As Visio.Characters
    Dim visSel As Visio.Shape
    Dim ptX1 As Double
    Dim ptX2 As Double
    Dim ptY1 As Double
    Dim ptY2 As Double
    Dim dltX As Double
    Dim dltY As Double
   
    Set XlApp = CreateObject("Excel.Application")

    fNameAndPath = XlApp.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS, (*.XLSX), *.XLSX, (*.XLSM), *.XLSM", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    XlApp.Workbooks.Open FileName:=fNameAndPath
    Set XlWrkbook = Workbooks.Open(fNameAndPath)
    Set XlSheet = XlWrkbook.Worksheets("Sheet1")
    XlApp.Visible = True
   
    Set rng = XlApp.InputBox("Select a range", "Obtain Range Object", Type:=8)

'Transfer Excel contents to Visio shapes on active page
'Initial shape location
    ptX1 = 3
    ptX2 = 5
    ptY1 = 3
    ptY2 = 3.5
    dltX = 0.25
    dltY = 0.25
   
    For Each Cell In rng
        Cell.Copy
               
        Visio.ActiveWindow.Page.DrawRectangle ptX1, ptY1, ptX2, ptY2
        Set visSel = Visio.ActiveWindow.Selection(1)
        Set vsoCharacters1 = visSel.Characters
        vsoCharacters1.Begin = 0
        vsoCharacters1.End = 0
        ActiveWindow.SelectedText = vsoCharacters1
        ActiveWindow.SelectedText.Paste
        ActiveWindow.DeselectAll
       
        ptX1 = ptX1 + dltX
        ptX2 = ptX2 + dltX
        ptY1 = ptY1 + dltY
        ptY2 = ptY2 + dltY

    Next
   
    ActiveWorkbook.Close SaveChanges:=False
    XlApp.Quit
   
End Sub


[wapperdude]

Updated the code:  the hunt for the Excel file now begins in the Visio drawing root directory.  Also lists the required library references.

Wapperdude

Code Select Expand


Sub MyMac()
'
' This macro has updated code to begin "hunt" for Excel file beginning with
' Visio drawing root directory.  Old code is still here, commented out.
' It is still possible to search other directories.
'
' The following references are used:
'   Visual Basic for Applications
'   Microsoft Visio Type Library
'   OLE Automation
'   Microsoft Office Object Library
'   Microsoft Excel Object Library
'   You'll have to choose the appropriate versions based upon your installation
'
    Dim XlApp As Object
    Dim XlWrkbook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    Dim rng As Range
    Dim fNameAndPath As Variant
    Dim docPath As Variant
   
    Dim intChoice As FileDialog
     
    Dim vsoCharacters1 As Visio.Characters
    Dim visSel As Visio.Shape
    Dim ptX1 As Double
    Dim ptX2 As Double
    Dim ptY1 As Double
    Dim ptY2 As Double
    Dim dltX As Double
    Dim dltY As Double
   
    docPath = ActiveDocument.Path
    Set XlApp = CreateObject("Excel.Application")
   
    With XlApp.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls, *.xlsx, *.xlsm"
        .InitialFileName = docPath
        .Show
        XlApp.Workbooks.Open FileName:=.SelectedItems(1)
    End With
   
    Set XlWrkbook = XlApp.Workbooks(1)
    Set XlSheet = XlWrkbook.Worksheets("Sheet1")
    XlApp.Visible = True

'    fNameAndPath = XlApp.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS, (*.XLSX), *.XLSX, (*.XLSM), *.XLSM", Title:="Select File To Be Opened")
'    If fNameAndPath = False Then Exit Sub
'    XlApp.Workbooks.Open FileName:=fNameAndPath
'    Set XlWrkbook = Workbooks.Open(fNameAndPath)
'    Set XlSheet = XlWrkbook.Worksheets("Sheet1")
'    XlApp.Visible = True
   
    Set rng = XlApp.InputBox("Select a range", "Obtain Range Object", Type:=8)


'Transfer Excel contents to Visio shapes on active page
'Initial shape location
    ptX1 = 3
    ptX2 = 5
    ptY1 = 3
    ptY2 = 3.5
    dltX = 0.25
    dltY = 0.25
   
    For Each Cell In rng
        Cell.Copy
               
        Visio.ActiveWindow.Page.DrawRectangle ptX1, ptY1, ptX2, ptY2
        Set visSel = Visio.ActiveWindow.Selection(1)
        Set vsoCharacters1 = visSel.Characters
        vsoCharacters1.Begin = 0
        vsoCharacters1.End = 0
        ActiveWindow.SelectedText = vsoCharacters1
        ActiveWindow.SelectedText.Paste
       
' Options:  remove fill and line patterns-> only text is visible
        visSel.CellsU("FillPattern").FormulaU = "0"
        visSel.CellsU("LinePattern").FormulaU = "0"
       
        ActiveWindow.DeselectAll
       
        ptX1 = ptX1 + dltX
        ptX2 = ptX2 + dltX
        ptY1 = ptY1 + dltY
        ptY2 = ptY2 + dltY

    Next
   
    XlWrkbook.Close SaveChanges:=False
    XlApp.Quit
     
End Sub