Pasted Excel Object doesn't display all info

[Wheatley20]

Hello,

I'm trying to insert some data from an Excel table into a Visio drawing.  However, when I perform the Paste Special action it doesn't show all of the data that I selected.  It is zoomed in just a part of table.  I've also tried pasting as picture.  I've tried making the rows/columns a smaller font and dimension...all result in the same.  The Excel doc and Visio doc have the same page dimensions.

Thanks,

Chris

[Yacine]

Select the table, right click and choose "edit" (not "open"). resize the table by dragging the points at the middle of the rectangle sides. This way you can change the number of rows and columns displayed.
Scrolling in the table will also determine which region of the sheet is displayed.

[Wheatley20]

Thanks, Yacine.  This helped.  But, it still doesn't work very well as there is seemingly no relationship to the rows/columns visible in the "edit" window and the what will be displayed in the Visio workspace.  It takes a lot of wrestling with it.  Is there a setting or preference that could be affecting this?

Best,

Chris

[Yacine]

Yes, but so "esoteric" that I haven't investigated it by myself yet.

[wapperdude]

The code below allows you to search for an Excel file, open it, select desired worksheet, and then the desired range of cells.  It will then paste the table into a new page in open Visio document.  It will also set the page orientation to match the table orientation.

Because I used standard MSGBOX and INPUTBOX, when Excel file opens, merely choose the desired worksheet, then, find the msbbox and say OK.  The worksheet will then go full screen, and ask to identify the cell range.  I believe it must be contiguous.  Then hit OK again and presto, it's in Visio.

But, unlike normal method as Yacine describes, this will only have the cells selected.  Need something different, re-run.  Note, re-running will clobber existing table.

Wapperdude

Code Select Expand


Sub MyMac()
'
' The macro is now interactive.  Once the used cell range has been copied
' Visio page, macro asks to save any changes to Excel or not, before exiting.
'
' 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 docPath As Variant
   
    Dim vsoCharacters1 As Visio.Characters
    Dim shpSel As Visio.Shape
   
    Dim setFocus As Long

   
    docPath = ActiveDocument.Path
    Set XlApp = CreateObject("Excel.Application")

SelFile:
    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)
    XlApp.Visible = True
   
' Before selecting cells via InputBox, it is necessary to choose desired WORKSHEET
' in the Workbook.  InputBox just grabs whichever worksheet is active when invoked.
' Simple solution is to use MsgBox.  Bad:  it is always positioned center screen and
' may be hidden.  Custom form would be improved solution.

    MsgBox ("Choose desired worksheet, then press OK")
   
' Once MsgBox is dismissed, must re-focus the Excel window in order to select the
' desired range of cells.

SelCells:
    Dim mySelRng As Range
    AppActivate XlWrkbook.Application
    XlApp.WindowState = xlMaximized
    Set mySelRng = XlApp.InputBox("Select a range", "Obtain Range Object", Type:=8)
    mySelRng.Copy
    XlApp.WindowState = xlMinimized


    'Checks if anything is on the page.  If no page, errors and goes to add page
    'Then makes page active.
    On Error GoTo addPage       'Error if no page named Excel
        Set vsoPage1 = Visio.ActiveDocument.Pages("ExTable")
        Visio.ActiveWindow.Page = vsoPage1
        Visio.ActiveWindow.DeselectAll
    On Error GoTo AddTable      'Error if no table exists, goto add table
        Visio.ActiveWindow.Select ActiveWindow.Page.Shapes.ItemU("Excel_Table"), visSelect
        Visio.Application.ActiveWindow.Selection.Delete
        GoTo AddTable
   
addPage:
    Set vsoPage1 = Visio.ActiveDocument.Pages.Add
    Visio.ActiveWindow.Page = vsoPage1
    vsoPage1.Name = "ExTable"
'    vsoPage1.Background = True
    vsoPage1.PageSheet.CellsSRC(visSectionObject, visRowPageLayout, visPLOSplit).FormulaForceU = "1"
    vsoPage1.PageSheet.CellsSRC(visSectionUser, 0, visUserValue).FormulaForceU = ""
   
    'This code pastes the worksheet items on the active Visio page
AddTable:
    vsoPage1.PasteSpecial visPasteOLEObject
    Set shpSel = Visio.ActiveWindow.Selection(1)
    shpSel.Name = "Excel_Table"
    shpSel.NameU = "Excel_Table"
   
' Set page orientation:
    If shpSel.CellsU("Width").ResultIU > shpSel.CellsU("Height").ResultIU Then
        Visio.ActivePage.PageSheet.CellsSRC(visSectionObject, visRowPage, visPageWidth).FormulaU = "11 in"
        Visio.ActivePage.PageSheet.CellsSRC(visSectionObject, visRowPage, visPageHeight).FormulaU = "8.5 in"
    Else
        Visio.ActivePage.PageSheet.CellsSRC(visSectionObject, visRowPage, visPageWidth).FormulaU = "8.5 in"
        Visio.ActivePage.PageSheet.CellsSRC(visSectionObject, visRowPage, visPageHeight).FormulaU = "11 in"
    End If
   
    shpSel.CellsU("PinX").FormulaU = "ThePage!pagewidth*0.5"
    shpSel.CellsU("PinY").FormulaU = "thepage!pageheight*0.5"
   
' User Prompts:
EndIt:
    If MsgBox("Save Excel file changes?", vbYesNo, "Excel Update") = vbYes Then
        XlWrkbook.Close SaveChanges:=True
    Else
        XlApp.CutCopyMode = False               'Clear the clipboard
        XlWrkbook.Close SaveChanges:=False
    End If
    XlApp.Quit
     
End Sub


[markjpinco]

So I have the same exact problem, with a catch...

This used to work for me with Win7, Visio 2013 Pro, and Office 2013.

My laptop was upgraded to Win10, Office 365, with Visio 2013 Pro. 

Now, Edit and Open do the same thing, open the worksheet in a separate window.  I can't just use the edit mode and drag the window size.

I will try the Macro but am wondering if anyone has been able to fix the edit mode in Win10/Office 365?

Mark

[TwoBeAss]

same problem over here...

edit and open do the same thing, and one the excel is closed for the first time, the cell area will not change..
Any help on that ?

[wapperdude]

Just ran this on Win11 machine.  Works as expected.  Was able to open Excel file, grab a selected range, and code pasted it into Visio.  I was able to grab the entry, move it, resize it. 

You can open the pasted shape, which will open Excel, copy a new region,
and then paste special into Visio.

[TwoBeAss]

yeah, but that means you have to copy paste everytime anything changes in the excel.. that couldnt be the right way to work with worksheets in visio...

[wapperdude]

That wasn't the object of the code.  Use Visio's import data functionality in order to accommodate updating.  See https://support.microsoft.com/en-us/office/refresh-imported-data-ab3668a1-873d-4109-8fa2-532670bc01e2#:~:text=On%20the%20Data%20menu%2C%20click,list%2C%20and%20then%20click%20Refresh.

[markem]

Let me take a whack at this. I just went through a struggle to get Visio 2003 show my Excel Spreadsheet properly. So maybe this can help.

1. I am assuming you are using the Insert->Object... option (at least that is what it is in Visio 2003).
2. Once the option has been chosen, use the "Create from file" option. That will bring up a file dialog. Find your file and load it in,
2a. REMEMBER that an Excel xls file (and I am assuming the newer xlsx is the same) should be vertically oriented. (What I mean is that if you have columns of information, a few blank columns, and then the same kind of information in more columns which is what I have. So an example would be like "ABC  ABC  ABC...". Is not all that good. I wound up putting each set of columns into separate fles.)
3. Double-left-click on the Excel Object you just loaded into Visio.
3a. NOTE that up at the top of the Visio document you will now see the Excel menus. These go away if you click on a Visio object but come back if you again double-left-click on the Excel Object.
4. Now you can select a column or row, go up to the Format menu and select the Column Width or the Row Height.
4a. NOTE that you can also move your mouse pointer to the top of the Excel Object where the "A|B|C..." titles are showing, grab the OR bar (|) and move it left or right to shrink or expand on a column.
5. This allows you to set how large you want a columns or rows to be.
6. Don't forget to do things like Tools->Options->Grids checkbox and deselect it so you don't get all of those grayed out lines.

I hope this helps