method open of object workbooks failed (Excel2003)

Topic

New Reply

Does it help if you use:

Sub test()
        Dim xlApp As New Excel.Application
        xlApp.Visible = True
        xlapp.automationsecurity = msoautomationsecuritylow
        Dim docTemplate As Excel.workbook
        Set docTemplate = xlApp.Workbooks.Open("K:Blobr085.xls")
'    Application.Workbooks.Open ("K:Blobr085.xls")
End Sub

Reply | Quote

Replies

No, but thanks Rory
It opens the file, but fails still on the Set docTemplate statement.
I tried inserting On Error Resume Next, which hurdles the error stop, but still leaves the docTemplate unassigned.

I then tried “ignoring” the first assignment, and, since the workbook was already open, trying to set docTemplate to the ActiveWorkbook.
(repeatedly checking Task Manager each issue at my regular hourly rate!)
No good. docTemplate is Nothing.

Sub test()
    Dim xlApp As New Excel.Application
    xlApp.Visible = True
    xlApp.AutomationSecurity = msoAutomationSecurityLow ' Rory
    Dim docTemplate As Excel.workbook
    On Error Resume Next ' Chris
    Set docTemplate = xlApp.Workbooks.Open("K:Blobr085.xls")
    Set docTemplate = xlApp.ActiveWorkbook ' Chris
'    Application.Workbooks.Open ("K:Blobr085.xls")
End Sub

I think I’ll try this at home tonight on my own system: Set Excel Macro Security=Medium and try to circumvent that.
There are some very weird things afoot in this client’s site setup.

Reply | Quote

What happens if you use GetObject and pass the workbook path?

Reply | Quote

(Edited by chrisgreaves on 10-Feb-09 06:53. Forgot to add ” Dim myWB As Object”)

>What happens if you use GetObject and pass the workbook path?
How come I am here and you are not? (grin!)

The “Dim myWB As Excel.Workbook” fails, but the ” Dim myWB As Object” succeeds.
I’m going to implement this skeletal code in the full application first thing in the morning, and will report back then.
Thanks, Rory, for (a) your insight and ( making me search the Lounge for “GetObject”

Sub Test2()
    ' post 758,145
    Dim xlApp As New Excel.Application
    xlApp.Visible = True
    xlApp.AutomationSecurity = msoAutomationSecurityLow ' Rory
    ' post 596,142 and post 758,145
    Dim myWB As Object
'    Dim myWB As Excel.Workbook
    Set myWB = GetObject("K:Blobr085.xls")
    MsgBox myWB.Author
End Sub

Reply | Quote

>What happens if you use GetObject and pass the workbook path?
This works in Word/Excel2000.
In both tests at home, the MsgBox was astutely hidden behind the applications.
In this (second) test I had to respond to the Enable/Disable prompt.

Sub Test2()
    ' post 758,145
    Dim xlApp As New Excel.Application
    xlApp.Visible = True
'    xlApp.AutomationSecurity = msoAutomationSecurityLow ' Rory
    ' post 596,142 and post 758,145
    Dim myWB As Object
'    Dim myWB As Excel.Workbook
    Set myWB = GetObject("B:Blobr085.xls")
    MsgBox myWB.Author
End Sub

Reply | Quote

Confirmation sought; solution unlikely (unless Rory is around ….)
The client site is locked ridiculously tight. Macro security can not be set to Low, even though I’m being paid to write macros. Duh!
I can open a workbook from within Excel2003 VBA, but can not open the same workbook from within Word2003 VBA.
The snippet below fails in Word VBA with a run-time error 1004.
The commented line is pasted directly from the Excel VBA, and it works just fine in Excel/vba.

This works at home on a Word/Excel/2000 system.
“automationsecurity ” is not available in Excel 2000

Sub test()
        Dim xlApp As New Excel.Application
        xlApp.Visible = True
'        xlApp.automationsecurity = msoautomationsecuritylow
        Dim docTemplate As Excel.workbook
        Set docTemplate = xlApp.Workbooks.Open("B:Blobr085.xls")
'    Application.Workbooks.Open ("K:Blobr085.xls")
        MsgBox docTemplate.Author
End Sub

Reply | Quote