Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2008
    Location
    Wayland, Massachusetts, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read from Excel file, in Visio using VBA (Visio 2003 Standard SP2, Vis

    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

  3. #3
    New Lounger
    Join Date
    Apr 2008
    Location
    Wayland, Massachusetts, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read from Excel file, in Visio using VBA (Visi

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read from Excel file, in Visio using VBA (Visi

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •