Open Excel Workbook From Visio VBA

[SteveM]

I'm trying to open an Excel Workbook from Visio 2007 VBA:

Private Sub CommandButton1_Click()
Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim thisPath As String

thisPath = ThisDocument.Path
thisPath = thisPath & "Tester.xlsx"

Set xlWB = xlApp.Workbooks.Open(thisPath)

End Sub

The MS Excel 12.0 Object Library was added as a Reference. However
executing the script yields the error message:

Automation Error Library Not Registered

So I'm apparently missing another Reference Library. Can anybody tell
me what that could be? Or perhaps point me to some code where Excel
is called from Visio?

Thanks,

SteveM

 

[JuneTheSecond]

Did you added a link to the Excel object libraly?
By menu Tools/References/Excel xx.x object libraly.
--
Best Regards.

JuneTheSecond
Now, visual calculation is more visual.
http://www.geocities.jp/visualcalculation/english/index.html

 

[SteveM]

Did you added a link to the Excel object libraly?
By menu Tools/References/Excel xx.x object libraly.
--
Best Regards.

JuneTheSecond
Now, visual calculation is more visual.http://www.geocities.jp/visualcalculation/english/index.html

JTS,

Yeah. Still no go.

Any other ideas?

SteveM

 

[John... Visio MVP]

I'm trying to open an Excel Workbook from Visio 2007 VBA:

Private Sub CommandButton1_Click()
Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim thisPath As String

thisPath = ThisDocument.Path
thisPath = thisPath & "Tester.xlsx"

Set xlWB = xlApp.Workbooks.Open(thisPath)

End Sub

The MS Excel 12.0 Object Library was added as a Reference. However
executing the script yields the error message:

Automation Error Library Not Registered

So I'm apparently missing another Reference Library. Can anybody tell
me what that could be? Or perhaps point me to some code where Excel
is called from Visio?

Thanks,

SteveM

What references do you have? Does it include OLE Automation?

John... Visio MVP

 

[SteveM]

What references do you have? Does it include OLE Automation?

John... Visio MVP

John,

Yeah, these Libraries are referenced:

VBA for Applications
Visio 12.0 Type Library
OLE Automation
Office 12.0 Object Library
Forms 2.0 Object Library
Excel 12.0 Object Library
Office Runtime 1.0 Type Library

I tried running the code from Access and got the same error.

hmm...?

SteveM

 

[AlEdlund]

as a note from the side, I get nervous when some basic checking isn't done.
You might try testing with something like this

Private Sub CommandButton1_Click()
testxlsload
End Sub

Private Sub testxlsload()

On Error GoTo ErrHandler

Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim thisPath As String

thisPath = ThisDocument.Path
thisPath = thisPath & "Tester.xlsx"

Set xlWB = xlApp.Workbooks.Open(thisPath)

Exit Sub

ErrHandler:

MsgBox Err.Description

End Sub

 

[AlEdlund]

Here some code that I use to open an excel file..
al

Sub OpenAndReadExcelWorkBook _
(ByVal strExcelFile As String, _
ByRef clsSearch As clsMyGraph)

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim strVertex As String
Dim strSideA As String
Dim strSideB As String
Dim intRow As Long
Dim strStart As String
Dim strEnd As String
Dim strSearch As String


On Error GoTo OpenAndReadExcelWorkBook_err

Set xlApp = CreateObject("Excel.Application")
'xlApp.Visible = True
'xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open(strExcelFile)
' open an existing workbook
' example excel operations
Set xlWS = xlWB.Worksheets("Vertex")

' get the search type, default to depthfirst
If xlWS.Cells(1, 1).Formula <> "" Then
strSearch = xlWS.Cells(1, 1).Formula
Else
strSearch = "depthfirst"
End If

' if strstart is empty then take the first vertex
If xlWS.Cells(1, 2).Formula <> "" Then
strStart = xlWS.Cells(1, 1).Formula
Else
If xlWS.Cells(3, 1).Formula <> "" Then
strStart = xlWS.Cells(3, 2).Formula
End If
End If

If xlWS.Cells(1, 3).Formula <> "" Then
strEnd = xlWS.Cells(1, 3).Formula
Else
strEnd = ""
End If


clsSearch.initClass ThisDocument, strSearch, 20, 0
Dim blnResponse As Boolean

With xlWS
intRow = 3
While xlWS.Cells(intRow, 1).Formula <> ""
strVertex = xlWS.Cells(intRow, 1).Formula
clsSearch.addVertex strVertex
intRow = intRow + 1
Wend
End With

Set xlWS = xlWB.Worksheets("Edge")
With xlWS
intRow = 3
While xlWS.Cells(intRow, 1).Formula <> ""
strSideA = xlWS.Cells(intRow, 1).Formula
strSideB = xlWS.Cells(intRow, 2).Formula
clsSearch.addEdgeLabel strSideA, strSideB
intRow = intRow + 1
Wend
End With


xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application

Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
Exit Sub

OpenAndReadExcelWorkBook_err:

Debug.Print "OpenAndReadExcel " & Err.Number & " " & Err.Description

Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

End Sub

 

[SteveM]

as a note from the side, I get nervous when some basic checking isn't done.
You might try testing with something like this

Private Sub CommandButton1_Click()
    testxlsload
End Sub

Private Sub testxlsload()

    On Error GoTo ErrHandler

    Dim xlApp As New Excel.Application
    Dim xlWB As Excel.Workbook
    Dim thisPath As String

    thisPath = ThisDocument.Path
    thisPath = thisPath & "Tester.xlsx"

    Set xlWB = xlApp.Workbooks.Open(thisPath)

    Exit Sub

ErrHandler:

    MsgBox Err.Description

 End Sub

Al,

Thanks. I get the same error message (code: 2147319779)

I tried re-registering Excel but that did not work.

I think VBA sees the Excel library because when I write an Excel
Object line like:

Set xlWB = xlApp.Workbooks...

The dot extensions pop up the VBA parameter list for the functions.

Man, I'm lost...

SteveM

 

[AlEdlund]

One thing I noticed when running your code was that Excel is not opening
visible (the default). Since your testing is also failing when you try to
drive it from Access my first step would be to repair office (reinstall).
al

 

[SteveM]

One thing I noticed when running your code was that Excel is not opening
visible (the default). Since your testing is also failing when you try to
drive it from Access my first step would be to repair office (reinstall).
al

Al,

Thanks much for your insights. They are very helpful.

First of all, I had installed Visio 2010 Beta in parallel with Visio
2007. Which also installed a bunch of Office 14 components in
parallel with Office 12. I removed Visio 2010 and ran Glary registry
cleaner. That got rid of the original error message.

But as you noted above, an Excel instance does open with the fix. But
it is not visible. So the next thing for me to work on.

SteveM

 

[AlEdlund]

If you notice in the message I sent with the example code, the two lines
below creating the Excel application object there are two commented lines

'xlApp.Visible = True
'xlApp.ScreenUpdating = False

uncomment them
al