Copy Excel Range of cells to Visio (2013) using VBA code

[bcham87]

I appreciate help with the following question. I have an Excel range of cells (let's call it "Table A") and I would like to copy that Range/Table into Visio using VBA.


Without VBA - I can copy the range in Excel, go to Visio - Insert - Chart - go to Sheet1 tab - Paste

Using VBA - I am a novice so this is what I think it should look like:

Set AppVisio = CreateObject("visio.Application")
AppVisio.Visible = True

AppVisio.Documents.AddEx "basicd_u.vst", 0, 0
        Worksheets("Sheet1").Range("TABLEA").Copy _
        AppVisio.ActiveWindow.Page.Add.Paste

 

[pbornemeier]

Try

Sub PasteExcelRangeToVisio()

    'Much taken from http://visguy.com/vgforum/index.php?topic=816.0
    '// Add a reference under Tools > References to:
    '// "Microsoft Visio 14.0 Type Library"
    
    Dim xlSheet As Excel.Worksheet
    Set xlSheet = Excel.ActiveSheet
    
    '// Start Visio and open a blank doc:
    Dim visApp As Visio.Application
    Set visApp = CreateObject("visio.application")
    '// Or get a running Visio:
    'Set visApp = GetObject(, "visio.application")
    
    Dim visDoc As Visio.Document
    Set visDoc = visApp.Documents.Add("")
    
    '// Get the first page in the document:
    Dim visPg As Visio.Page
    Set visPg = visDoc.Pages.Add
    Dim visShp As Visio.Shape
    
    Dim xlRng As Excel.Range
    
    Set xlRng = Range("TableA")
    
    '// Copy the Excel object to the clipboard
    xlRng.Copy
    
    '// Paste it into Visio as worksheet:
    visPg.PasteSpecial 49162, False, False   '49162 = Excel Worksheet
    
    '// The pasted object will be the last object in the shapes's collection
    '// Add range name to pasted grid
    
    Set visShp = visPg.Shapes(visPg.Shapes.Count)
    visShp.Text = xlRng.Name.Name  'Uncomment this to have name added below pasted range

End Sub