Read from Excel file, in Visio using VBA (Visio 2003 Standard SP2, Visual

Topic

New Reply

Visio 2003 Standard SP2, Visual Basic 6.3, Excel 2003 Pro SP3

This site highly recommended by a co-worker, first post, rather lenghty, sorry.
Posting to both Visio and VBA, and also contains an association with Excel, so here goes.

Background/problem: What I want to do is be able to open an Excel spreadsheet, and read in either one line at a time, or an entire table into Visio. I am trying to follow the documentation on MSDN. The Visio 2002 pages have a lot of detail, with examples but I am not having any luck implementing the code examples. The Visio 2003 MSDN reference is very high level and says to use the help that comes with Visio. So I am combining the examples from 2002 online and those embedded in 2003 help, and I have not gotten very far. The following code extract is how far I get before the VBA fails:

Sub dbXLInputs()

Dim wrkspcODBC As Workspace
Dim dbsOV As Database
Dim dbsOVtemp As Database
Dim rstNodes As Recordset
Dim prpLoop As Property

Dim qdfNew As QueryDef
Dim fldLoop As Field

‘ Create an ODBCDirect workspace.
Set wrkspcODBC = CreateWorkspace(“ODBCWorkspace”, “admin”, _
“”, dbUseODBC)
‘ Workspaces.Append wrkODBC

‘ Open read-only Database object by entering only the
‘ missing information in the ODBC Driver Manager dialog
‘ box.
‘ MsgBox “Opening OV7 Excel file …”
Set dbsOV = wrkspcODBC.OpenDatabase(“D:OV2.input.samples.xls”, _
dbDriverPrompt, _
True)
‘ 3rd param values dbDriverNoPrompt, dbDriverPrompt, dbDriverComplete or dbDriverCompleteRequired

‘ Have tried with and without this command, with no change in behavior
Set qdfNew = dbsOV.Connection.CreateQueryDef(“OVNodes”)

‘ Create and append new Field objects for the new
‘ TableDef object.
‘ With qdfNew
‘ ‘ The CreateField method will set a default Size
‘ ‘ for a new Field object if one is not specified.
‘ .Fields.Append .CreateField(“Node”, dbText)
‘ .Fields.Append .CreateField(“Description”, dbText)
‘ .Fields.Append .CreateField(“LinkToNode”, dbText)
‘ End With

‘ Open the record set as a …
‘ I have attempted to make this call each of the type values, dbOpenDynamic,
‘ dbOpenDynaset, dbOpenSpanshot and dbOpenForwardOnly
‘ All the examples us a Name, 1st arguement, that either does not appear prior
‘ to this call or is related to an .mdb file. The help says that using the ODBC
‘ interface is the way to access Excel files.
Set rstNodes = _
dbsOV.OpenRecordset(“OVNodes”, dbOpenDynamic, dbReadOnly)

‘ The OpenRecordSet gives me a 3001, invalid arguement error,every time
‘ I have tried what seems like every combination of the arguments that are valid
‘ for an ODBCDirect workspace (non Jet workspace)
‘ …

End

Assumptions for using this approach: Visio provides a wizard to create org charts from an Excel spreadsheet, so I was attempting to us the same mechanism to write my own Add-On.

Reply | Quote

Replies

Welcome to Woody’s Lounge!

Depending on what exactly you want to do, it might be easier to use Automation to start an instance of Excel, open the workbook and read from it, or to use ADO instead of DAO.

If you search the VB/VBA and Access forums for Excel.Application, you’ll find examples of automating Excel from another application. If you set a reference (in Tools | References) to the Microsoft Excel 11.0 Object Library, IntelliSense will work for Excel objects too.

Here is a rather basic example:

Dim xlApp As New Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsh As Excel.Worksheet
Dim r As Long
Dim m As Long

Set xlWbk = xlApp.Workbooks.Open((“D:OV2.input.samples.xls”)
Set xlWsh = xlWbk.Worksheets(1)

‘ Last filled row in column A
m = xlWsh.Range(“A65536”).End(xlUp).Row
‘ Loop through rows
For r = 2 To m
Debug.Print xlWsh.Range(“A” & r)
Next r

xlWbk.Close SaveChanges:=False
xlApp.Quit

Reply | Quote

Hans,

Thank you very much. The code snipit ran smoothly.

Fairly new to working with VBA and had the idea that I had to be programming in the language (context) of the Application in which I was running, i.e. Visio. I knew VBA was its own language, but this experience makes that very clear. It works with all the MS applications and the intent is to make life easier for those of us who want to program across Applications.

Thanks again,
Taylor

Reply | Quote

Our moderator WendellB has a short introduction to Automation on his website: Automation 101. It also contains useful links.

Reply | Quote