I'm trying to open a file in a folder on every user's desktop. The file exists in a folder called "Resources". I tried to use the code from this posting; http://www.excelforum.com/excel-prog...op-folder.html
but it's not working. My code exists in the "Workbook/Open" module. I'm getting a Run-Time error of 1004, saying it can't find the file. I'd appreciate any help you can provide in trouble-shooting my code.
Thanks,
John
Please Login or Register to view this content.
Last edited by jomili; 09-17-2010 at 10:35 AM.
You've got two path separators at the end of the path.
Entia non sunt multiplicanda sine necessitate
So are you saying I should delete this line?
or are you saying I should add another path separator?Please Login or Register to view this content.
Yes, you should delete that line.
And for consistency,
Please Login or Register to view this content.
I made the changes you mentioned, and am still getting the 1004 error code.
Please Login or Register to view this content.
Okay, I think I've figured it out. Changed code as shown below. Now it works. I don't know if that's the right way to do it, but it works. Any other ideas to make it work right?
Please Login or Register to view this content.
With a bit of trial and error, and ample use of Debug.Print, I was able to get your code woking. It seems like Dir was stripping the path, and without a path you were trying to open the file in the current dir of excel, while the file you need is actualy in the resource folder on the Desktop. I think the error occurs becuase the file isn't where Excel is looking for it.
Please Login or Register to view this content.
Gordon in Rovereto, Italy
A-ha! That would do it.
I put a msgbox to show me what the path was pulling up, and saw that it was being stripped, but I didn't understand how, which was why I came up with my workaround. Yours is a more elegant version, so I'll use that, if you don't mind. Thanks for your contribution.
Hello jomili,
For those who maybe interested, here is another way to open the workbook in the desktop folder using the Shell object.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thanks Leith.
I like that; it does it all in one concise macro, and looks easy enough to adapt to other uses. Thanks for showing me a different way.
Leith,
It looks good, but it didn't work for me right away. I got an error 1004, saying. I corrected by changing "Filename" to "FilePath" in the 'Open the workbook' section. Here's the corrected code:Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by jomili; 09-17-2010 at 08:11 AM.
or
Please Login or Register to view this content.
snb,
I like the brevity of your code, but it doesn't do anything when I load it into a standard module. Stepping through the code I don't see anything happen. It's like a non-event. What am I doing wrong.
In the VBEditor open the window 'Local Variables'.
Step through the code ( F8 )
Look in the Local Variables Window and check whether the value of c01 points to an existing workbook. If not, nothing will happen in the next line (that why the check with Dir(c01) )
SNB,
The Locals window shows the value for C01 is this path:So, it's not pointing to the desktop, hence can't open the file. But why wouldn't it display an error?Please Login or Register to view this content.
You want Specialfolders(4) not 1 for the Desktop.
Everyone who confuses correlation and causation ends up dead.
Thanks Romper, that got it working. Is there a listing somewhere of the Special Folders values?
Now it's opening just fine, but I want to defeat the Read Only warning. I tried it by altering the code below, but my change doesn't seem to affect it. Please advise.
Please Login or Register to view this content.
Never mind; I'm just being dumb. It's all in the punctuation. Not, but insteadPlease Login or Register to view this content.Here's the corrected, very well-behaved code:Please Login or Register to view this content.
Thanks so much to everyone who's participated in this post. I've learned a lot.Please Login or Register to view this content.
Thanks,
John
There's a list on MSDN here, but I'm not sure it's complete. Note: easier to use the names rather than numbers.
To make it more robust (because on my system specialfolders(1) = Desktop)
Please Login or Register to view this content.
RomperStomper, thanks for the info.
SNB,
Thanks for robustifying your macro. I would have gone blindly onward putting 4 and wondering why it wouldn't work for some of my users.
You guys are great, and I truly appreciate all the help you give. Thanks a lot!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks