Results 1 to 11 of 11

Thread: Problem setting workbook object

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Problem setting workbook object

    I'm using this code to open a workbook and set it as an object:

    Code:
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:="\\netshares\abc\myDatabase.xlsx")
        If Err.Number <> 0 Then
            Stop
        End If
        Stop
    On three different computers I get the following behavior:

    1) Code stops at the stop after the End If, object is set correctly

    2) Code stops at the stop after the End If, object is NOT set

    3) Code stops at the first stop (error encountered)

    All three have the same version of Excel (2016), and the same OS.

    Any thoughts on why I might be seeing this behavior?

    Thanks!

    Bryce

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Problem setting workbook object

    Outcomes 1 and 3 are expected behavior if you 1 - have access to the shared folder and file or 3 - do not have permission to access the share or file or the share or file does not exist.

    Outcome 2 is the strange one. On a computer where you get outcome 2, can you access the shared folder by pasting the path into Windows Explorer? If yes, can you see the file?

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Problem setting workbook object

    For #3, I can access both the folder and the file "manually," but get the error when running the code.

    For #2, I can also access the file manually.

    I'm really stumped!

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Problem setting workbook object

    What error number or text are you getting on #3?

    Is Excel already open when you try this?

    Try this and post back results:

    Code:
    Workbooks.Open Filename:="\\netshares\abc\myDatabase.xlsx"
    Last edited by jdc2000; May 17th, 2018 at 09:39 AM.

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Problem setting workbook object

    I don't have the error in front of me right now (my co-worker ran it yesterday and showed me), but I think was a "91" error.

    Yes, Excel is already open when it's run

    I have asked the guy to try it after changing the code as you suggested, but he's out today Will post back when I hear.

    Thanks for the suggestion.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Problem setting workbook object

    is the workbook supposed to open into a visible or not visible instance of exel?

    if not visible, test with visible to see if any dialogs are shown
    try opening into a new instance
    check if opening the file by double click it opens with any restriction, like read only or shared

    some of these things are hard to diagnose, especially on remote computer
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Problem setting workbook object

    The workbook should open into the visible instance from which the code is running. I try stepping through the code on the "unmanned" computer, but it generates the error mentioned. Thanks for the reply!

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Problem setting workbook object

    does the workbook fail to open, or just the object is not set?

    much as i do not like using active anything, by any chance would this work?
    Code:
    wb = Workbooks.Open Filename:="\\netshares\abc\myDatabase.xlsx"
        Set wb = activeworkbook
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Problem setting workbook object

    I believe it fails to open at all in scenario #3 above. I did have the guy test it in scenario #2 (his own laptop), and it DOES open the workbook in that case. Will try what you suggest in post 8 today. Thanks again.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Problem setting workbook object

    if the workbook is open and the object is still not assigned to the variable, then try a new variable
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Problem setting workbook object

    will give that a shot

Posting Permissions

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



Click Here to Expand Forum to Full Width