Open Excel from Publisher VBA

[danuk1976]

Anyone know how to open an excel file from publisher, I've searched the world for this.

This is how I open publisher from publisher

Sub openpub()

' Create new instance of Publisher.
    Dim appPub As New Publisher.Application

    ' Open publication.
    appPub.Open Filename:="C:\test.pub"

    ' Put code here to modify the publication as necessary.

    ' Close the publication.
    appPub.ActiveDocument.Close

    ' Release the other instance of Publisher.
    Set appPub = Nothing


End Sub

thanks

 

[shg]

Set a reference to Excel, then

Sub OpenExcel()
    Dim appXl       As Excel.Application

    Set appXl = New Excel.Application
    appXl.Open Filename:="C:\test.xlsx"
    ' do stuff
    appXl.ActiveWorkbook.Close
    Set appXl = Nothing
End Sub

 

[danuk1976]

Set a reference to Excel, then

Sub OpenExcel()
    Dim appXl   As Excel.Application

    Set appXl = New Excel.Application
    appXl.Open Filename:="C:\test.xlsx"
    ' do stuff
    appXl.ActiveWorkbook.Close
    Set appXl = Nothing
End Sub

Looked good but,,,,,,,,,,,,,,,,,,,I get

Compile error
User defined type not defined for the top line

I am user publisher 2007

 

[RoryA]

As shg said "Set a reference to Excel". In the VB Editor, click Tools-References, then choose the Microsoft Excel 12.0 Object library.

 

[danuk1976]

As shg said "Set a reference to Excel". In the VB Editor, click Tools-References, then choose the Microsoft Excel 12.0 Object library.

I ticked the box for Microsoft Excel 12.0 Object library,,,
I am getting runtime error 438, object does not support this property or method and debugs to the line underlined below

any more advice appreciated


Sub OpenExcel()

Dim appxl As Excel.Application

Set appxl = New Excel.Application

appxl.Open Filename:="C:\test.xls"

' do stuff
'appXl.ActiveWorkbook.Close
'Set appXl = Nothing

End Sub

 

[RoryA]

It should read:

appxl.workbooks.open Filename:="C:\test.xls"

 

[shg]

appxl.Workbooks.Open ...

 

[danuk1976]

It should read:

appxl.workbooks.open Filename:="C:\test.xls"

Thanks guys, something so simple yet had me stumped for a long time

adding this also helps

appxl.Visible = True

 

[shg]

You're welcome.