By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

(Workbooks.Open) with code on shared drives with different lettering in name

atljpe
P: 11
I'm a beginner:
In MS Excel, my code begins with opening a file located within a shared file & drive. My drive begins with the letter "N" ie. N:\ftwntfilp012 where as other users may give it a different letter such as "Z" ie. Z:\ftwntfilp012.
Is there a way to use this code without having to change the lettering each time someone with a different letter before their drive uses this macro?
I hope I'm clear
Thanks for any assistance
atljpe

Expand|Select|Wrap|Line Numbers
  1. ..Sub NEWSEND()
  2. ActiveWorkbook.Save
  3. 'sub openup()
  4.     Workbooks.Open ("N:\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls")
  5.     Sheets("Archive").Select
  6.     ActiveSheet.Unprotect
  7.     Range("B3").Select
  8.     Selection.AutoFilter..
Apr 25 '07 #1
Share this Question
Share on Google+
9 Replies


atljpe
P: 11
Please let me know if my question is not clear
May 1 '07 #2

Dököll
Expert 100+
P: 2,364
I'm a beginner:
In MS Excel, my code begins with opening a file located within a shared file & drive. My drive begins with the letter "N" ie. N:\ftwntfilp012 where as other users may give it a different letter such as "Z" ie. Z:\ftwntfilp012.
Is there a way to use this code without having to change the lettering each time someone with a different letter before their drive uses this macro?
I hope I'm clear
Thanks for any assistance
atljpe

Expand|Select|Wrap|Line Numbers
  1. ..Sub NEWSEND()
  2. ActiveWorkbook.Save
  3. 'sub openup()
  4.     Workbooks.Open ("N:\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls")
  5.     Sheets("Archive").Select
  6.     ActiveSheet.Unprotect
  7.     Range("B3").Select
  8.     Selection.AutoFilter..
Hello, atljpe!

Nice of you to add a portion of your code...

You can use it as the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Workbooks.Open (App.Path &  "\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 
  3.  
  4.  
Have fun with the project!

Dököll
May 1 '07 #3

atljpe
P: 11
Thanks Dököll for responding............
Appears we're on the right track. I copied the suggested variation into my code but I received a run time 424 error. I tried to follow the help instructions but I was not able to figure out the fix. I also tried minor variations of what you gave me but I was not successful. Was I supposed to add something else to your suggested variation? Do you need more information from me or my code?
Hope you can help....thnx & brgds atljpe
May 2 '07 #4

Denburt
Expert 100+
P: 1,356
Try adding a breakpoint or even a message to verify that you are arriving at the correct path. If I am not mistaken app.path will lead you to your office program location. Since that is the application that your workbook is open in.

MsgBox app.Path

The following should get you there. This is the Visual Basic forum and is similar in nature however Excell and all office apps use VBA which is Visual basic for Applications. Not to take away from any of the guys in here though just an FYI for future references, the guys here are really talented especially if they are answering questions for both types of code but a VBA forum is probably more suited, such as MS Access. I don't usually move these threads since these guys are so good they more than hold their own.

MsgBox ThisWorkbook.Path

MsgBox activeworkbook.Path
May 2 '07 #5

atljpe
P: 11
Thanks for the education...I didn't know :)
-please show me within the code brackets, exactly how to arrange the MsgBox within the code
-my attempts have been usless
I greatly appreciate your patience

Also, if this string needs to be moved, please do, I'm sure you all have enough to deal with within your own categories than to have outside categories reside within your workloads.
May 2 '07 #6

Denburt
Expert 100+
P: 1,356
Old line of code:
Expand|Select|Wrap|Line Numbers
  1. Workbooks.Open (App.Path &  "\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 
New Code:
Expand|Select|Wrap|Line Numbers
  1. 'Check to verify the path is correct.
  2. MsgBox activeworkbook.Path
  3. 'Open the workbook, you may need to make an adjustment to the following statement to correct the path/file
  4. Workbooks.Open (activeworkbook.path &  "\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 
An alternative is to hard code the servername on down, this is not quite as fast as the previous method but might yield the results you need. A mapped drive is always quicker than using the full servername since a mapped path keeps the connection to that server open. The trick is to get the server name itself then you can easily use the following and it will work for everyone. In most cases you can move the mouse over the mapped drive and after a second or so the tool tip should pop up with the info you need.

Expand|Select|Wrap|Line Numbers
  1. Workbooks.Open ("\\MYSTUPIDSERVERNAME\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 
May 2 '07 #7

Dököll
Expert 100+
P: 2,364
Thanks Dököll for responding............
Appears we're on the right track. I copied the suggested variation into my code but I received a run time 424 error. I tried to follow the help instructions but I was not able to figure out the fix. I also tried minor variations of what you gave me but I was not successful. Was I supposed to add something else to your suggested variation? Do you need more information from me or my code?
Hope you can help....thnx & brgds atljpe
Quite welcome, atljpe! Looks like Denburt's idea should work, give that a whil. Please stay tuned if more is needed...
May 3 '07 #8

atljpe
P: 11
Denburt & Dököll,
Thank you very much for your logic power...
The second code worked like a charm, so problem solved...
I will work with the 1st code until I get it right, so I can educate myself more...
Thank You
atljpe
May 3 '07 #9

Denburt
Expert 100+
P: 1,356
Glad we could help if you have any questions at all please feel free to ask.

BTW

You will find that "Debug.print" is much better than msgbox but I thought it would be best to get your issue fixed first, and i was in a bit of a rush. If you use the following you can see the results in the "immediate" window. You may need to go to the menus click - View - Immediate Window (Ctrl G).


Expand|Select|Wrap|Line Numbers
  1. 'Check to verify the path is correct.
  2. Debug.print activeworkbook.Path
  3.  
  4. Workbooks.Open ("\\MYSTUPIDSERVERNAME\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 
  5.  
Also (fyi) "Instr" is a very convienient function for parsing or breaking up the paths/folders.
May 3 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.