将Excel连接到Visio

Question

我正在尝试让Visio从Excel读取数据，并根据电子表格上的信息在Visio上创建绘图。我找到了这个例子，它使用Access和C#来完成我想要完成的任务。有人能帮我吗？我刚开始只是在excel上创建到visio的连接，但我已经被卡住了。Visio一直给我一个错误"Unable to connect to data“。这是我到目前为止所知道的

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")

Answer 1

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