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

[atljpe]

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

..Sub NEWSEND()
ActiveWorkbook.Save
'sub openup()
    Workbooks.Open ("N:\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls")
    Sheets("Archive").Select
    ActiveSheet.Unprotect
    Range("B3").Select
    Selection.AutoFilter..

[atljpe]

Please let me know if my question is not clear

[Dököll]

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

..Sub NEWSEND()
ActiveWorkbook.Save
'sub openup()
    Workbooks.Open ("N:\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls")
    Sheets("Archive").Select
    ActiveSheet.Unprotect
    Range("B3").Select
    Selection.AutoFilter..

Hello, atljpe!

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

You can use it as the following:

 
Workbooks.Open (App.Path &  "\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 
 
 

Have fun with the project!

Dököll

[atljpe]

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

[Denburt]

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

[atljpe]

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.

[Denburt]

Old line of code:

Workbooks.Open (App.Path &  "\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 

New Code:

'Check to verify the path is correct.
MsgBox activeworkbook.Path
'Open the workbook, you may need to make an adjustment to the following statement to correct the path/file
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.

Workbooks.Open ("\\MYSTUPIDSERVERNAME\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 

[Dököll]

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

[atljpe]

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

[Denburt]

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

'Check to verify the path is correct.
Debug.print activeworkbook.Path
 
Workbooks.Open ("\\MYSTUPIDSERVERNAME\Central Region Reports\Central Region DOL Reports\Archive Central Region DOL Log.xls") 
 

Also (fyi) "Instr" is a very convienient function for parsing or breaking up the paths/folders.