-
May 16th, 2018, 08:18 AM
#1
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
-
May 16th, 2018, 09:48 AM
#2
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?
-
May 16th, 2018, 10:52 AM
#3
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!
-
May 16th, 2018, 01:07 PM
#4
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.
-
May 17th, 2018, 05:09 AM
#5
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.
-
May 20th, 2018, 04:39 PM
#6
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
-
May 20th, 2018, 06:38 PM
#7
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!
-
May 21st, 2018, 04:29 AM
#8
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
-
May 21st, 2018, 06:03 AM
#9
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.
-
May 21st, 2018, 06:07 AM
#10
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
-
May 21st, 2018, 09:45 AM
#11
Re: Problem setting workbook object
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|