Results 1 to 7 of 7
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: method open of object workbooks failed (Excel2003)

    Does it help if you use:
    <pre>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</pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: method open of object workbooks failed (Excel2003)

    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.
    <pre>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</pre>

    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.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: method open of object workbooks failed (Excel2003)

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

    Microsoft MVP - Excel

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    method open of object workbooks failed (Excel2003)

    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.

    <LI>I'm guessing that the fiendish "Disable macros/Enable macros/More Info" message box is the culprit.

    Tech support say they cannot set my security Low because it's out of their hands, all done by some mindless computer in Texas. (I have nothing against Texans, some of my worst enemies are Texans, but I'm building up a resentment against <img src=/w3timages/censored.gif alt=censored border=0> Texan computers.)

    And yes, I used Task Manager to remove all previous instances of Excel.exe prior to re-running my test.


    <pre>Sub test()
    Dim xlApp As New Excel.Application
    xlApp.Visible = True
    Dim docTemplate As Excel.workbook
    Set docTemplate = xlApp.Workbooks.Open("K:Blobr085.xls")
    ' Application.Workbooks.Open ("K:Blobr085.xls")
    End Sub</pre>


  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: method open of object workbooks failed (Excel2003)

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

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


  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: method open of object workbooks failed (Excel2003)

    <P ID="edit" class=small>(Edited by chrisgreaves on 10-Feb-09 06:53. Forgot to add " Dim myWB As Object")</P>>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"

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


  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: method open of object workbooks failed (Excel2003)

    >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.

    <pre>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</pre>


Posting Permissions

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