将 Excel 连接到 Visio

Question

我正在尝试让 Visio 从 Excel 读取数据并根据电子表格上的信息在 Visio 上创建绘图。找到此示例，该示例使用 Access 和 C# 来完成我想要完成的任务。有人可以帮我解决这个问题吗？我首先只是在 excel 上创建与 visio 的连接，我已经卡住了。Visio 一直给我一个错误"无法连接到数据"。这是我到目前为止所拥有的

Const excelFileName As String = "<C:UsersDocumentsBook2>"
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