+ Reply to Thread
Results 1 to 8 of 8

Need to bypass the drive letter (i.e C Drive) and use the drive name

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    49

    Need to bypass the drive letter (i.e C Drive) and use the drive name

    Good afternoon.

    I am having real difficulty in changing my Macro to bypass the drive letter and use the drive name.

    Basically, their are 20 people in my team which will require access to the file I have built. The macro in the file currently uses Z:\ as this is what my PC is set to. Others on the same drive (letter) the file works fine, however other are set to X:\ or Z:\ and the macro errors.

    I have found the server name which is \\gbrcrotpc0c1002\Life_Ent by going into Programs/Accessories/Comand Prompt

    I have pasted two examples below as I am by far experienced in using Macros (my first attempt to be honest).

    The first one works with others on the Y:\ as mentioned above, the second is me trying to bypass the letter so regardless of which letter they have used, it can still access the file.

    Please tell me there is a way of doing this, as I wont be able to get everyone to change their "letter".

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by alansidman; 10-11-2013 at 10:29 AM. Reason: added code tags

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,678

    Re: Need to bypass the drive letter (i.e C Drive) and use the drive name

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (Because this is your first posting, I have added the code tags. Please read our rules and adhere to them in the future.)
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Need to bypass the drive letter (i.e C Drive) and use the drive name

    If using the UNC path, as you are in the 2nd example, works then use it.

    PS Can you add code tags?
    If posting code please use code tags, see here.

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,678

    Re: Need to bypass the drive letter (i.e C Drive) and use the drive name

    It looks like the second one should work. Have you tested on machines that have different Drive letters?

  5. #5
    Forum Contributor
    Join Date
    04-24-2007
    Location
    NYC
    MS-Off Ver
    2k3/2k7/2010
    Posts
    270

    Re: Need to bypass the drive letter (i.e C Drive) and use the drive name

    You have the code right there.

    Z:\Pricing Tool\NEW PRICING TOOL (July onwards only)\
    vs
    \\gbrcrotpc0c1002\Life_Ent\Pricing Tool\NEW PRICING TOOL (July onwards only)\

    "z:\" is the same equivelant of "\\gbrcrotpc0c1002\Life_Ent\"

    You need to find out each pathname of the letter drive you are using and replace it.

  6. #6
    Registered User
    Join Date
    10-10-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    49

    Re: Need to bypass the drive letter (i.e C Drive) and use the drive name

    I did load of Google research to obtain the UNC and how to enter it into the current Macro to replace the "letter".

    The strange thing is, when I leave it as using the "letter", it works fine on my machine, however when entering the UNC which I obtained from my PC, it says it cannot find the file??

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Need to bypass the drive letter (i.e C Drive) and use the drive name

    Where is the workbook containing the code located in relation to the files you are trying to reference in the code?

  8. #8
    Forum Contributor
    Join Date
    04-24-2007
    Location
    NYC
    MS-Off Ver
    2k3/2k7/2010
    Posts
    270

    Re: Need to bypass the drive letter (i.e C Drive) and use the drive name

    Do not dynamically search the UNC from a different workstation. If you have a Z: drive that is a network share and your coworker has a z: drive as a network share, it does not mean they both goto the same place. Your Z: and His/Her Z: could goto different places.

    Get the path name from your workstation and hard code it into the macro.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Drive Letter Property not returning mapped drive letter
    By Mike_bee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2013, 02:45 PM
  2. Replies: 0
    Last Post: 07-12-2011, 11:30 AM
  3. Replies: 3
    Last Post: 07-24-2006, 03:30 PM
  4. Save to hard drive and backup to thumb drive.
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-27-2006, 09:17 AM
  5. linking one worksheet on one drive to another sheet on another drive
    By phlashh in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-09-2005, 02:59 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1