CODEWITHSUNDEEP
excelvbavisio
Codingrookie
Codingrookie

Reputation: 43

Connecting Excel to Visio

I'm trying to have Visio read data from Excel and create a drawing on Visio based on the information on the spreadsheet. Found this example that uses Access and C# to do what i'm trying to accomplish. Can anybody help me with this? I started by just creating the connection on excel to visio and i'm already stuck. Visio keeps giving me an error "Unable to connect to data". Here's what I have so far

Const excelFileName As String = "<C:\Users\Documents\Book2>"

Public Sub DrawVisio()

Dim doc As Visio.Document
Set doc = ActiveDocument

Dim cmd As String
Dim conString As String
Dim drs As Visio.DataRecordset

conString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "User ID=Admin;" & _
             "Data Source=" & excelFileName & ";" & _
             "Mode=Read;" & _
             "Extended Properties=""HDR=YES;IMEX=1;" & _
             "MaxScanRows=0;Excel 12.0;"";" & _
             "Jet OLEDB:Engine Type=34;"
    cmd = "select * from `Sheet1$`"
    Set drs = doc.DataRecordsets.Add(conString, cmd, 0, "Sheet1")

Upvotes: 1

Views: 2722

Answers (1)

OldSchool1948
OldSchool1948

Reputation: 11

Dim xlWorkBookName As String
xlWorkBookName = "WorkbookName.xlsm"

Dim xlApp As Excel.Application
Set xlApp = CreateObject("excel.application")

Dim pathExcel As String
pathExcel = Visio.ActiveDocument.Path & xlWorkBookName

Dim XlWrkBook As Excel.Workbook
Set XlWrkBook = xlApp.Workbooks.Open(FileName:=pathExcel)
xlApp.Visible = True

Dim XlWrkSheet As Excel.Worksheet
Set XlWrkSheet = XlWrkBook.Sheets.Item("WorksheetName")
XlWrkSheet.Activate

Upvotes: 1

Related Questions