Solved: Excel 2003/07 - How do I assign a UNC path to a USB Drive

[frank_m]

How can I assign a UNC path (ie: \\MySharedDrive ) to an external USB Drive(Currently Drive Letter G)

I would like to do it with vba code in Excel, but if that is complicated, manually will be ok.

I do not currently have a network set up between the two computers I have at home, but I am willing to set one up if it makes things any easier.

I need this so that I can test various workbooks from home that will be used at the office on a shared network drive. At the office they do not have a server, so I am almost sure that they are also using a usb drive configured with a unc path on the network. I could ask our IT guy how he did it, but he often takes days or weeks to answer my requests.

Thanks

[joms]

hi frank_m, can try this:


Dim wb As Workbook
Dim sFileName As String

'sFileName = "\\MySharedDrive\MyExcelfile.xls"
sFileName = "G:\Mytemplate.xls"

Workbooks.Open (sFileName)

[frank_m]

Hi joms,

Thanks for your suggestion. I probably did not explain my need very well. My situation is that I want to be able to test code that will be used at the office on a shared network drive, but because I work from home I have no way to test code that uses a network path. - After a lot of google searching it seems there might not be any way to change a local usb drive path, to have a UNC path. - I do have a second computer but I have encountered some difficultly getting vista to recognize the xp pc.

My conclusion at this point is that my best bet is probably going to be to persue getting my computers to recognize each other on a network.

Thanks

[Kenneth Hobs]

Assign a virtual drive as needed. You can do this in Explorer or by a BAT file using the DOS command Subst or a Shell in DOS to do that same. You could query drives to find UNC names if needed.

If you are working from two different drives or paths, use DIR() to determine if the file exists or not. It it exists, then you can use that drive/path.

[frank_m]

Hi Kenneth -- I appreciate your time and offer of idea's

I was able to create a virtual drive using the Subst command, but I have not been able to figure out how to give it a UNC path

- Through trial and error I was able to figure out how to accomplish what I wanted with a different method:

I Right Clicked My Local Drive F: which is a partition of my C Drive
Then I Clicked Share from the dropdown menu
that took me to the Sharing Tab in properties
Clicked the Advanced Sharing button (note that I am not actualy networked to any other computer)
Clicked the Share this folder Check Box
Clicked the Add Button which displayed a dialog for naming
In the Share Name Textbox I typed "MyShare" without the quotes
Clicked ok
Clicked the permissions button
marked all the Full control to everyone checkbox's
Clicked Apply
Clicked ok
Clicked the Security Tab
Clicked the Edit button
marked all of the Allow Full Control for everyone checkbox's
Clicked Apply
Clicked ok
Clicked Close

After I closed that, a popup appeared for a minute or two, as the drive and the files and subfolder's were processed.

Then I right clicked my F drive again, clicked Share from the dropdown
and i can see the Drive letter F, and underneath it says "Shared" and below that shows the network path

Now I can use either F: or the UNC path "\\OWNER-PC\MyShare" as my save to path for a workbook

OWNER is my user name, I am not sure why -PC was added automatically, but as I described above, its easy to discover the UNC path by right clicking properties > then the Share Tab

(As mentioned in my first post, the reason I needed this is so I can work on projects at home and be certain various save-as procedures will function when used with the shared network drive at the office)

[frank_m]

:rotlaugh:
Please pardon me... I had a couple steps described wrong in my previous post and my allotted time for editing has expired.

The corrected steps are:

I Right Clicked My Local Drive F: which is a partition of my C Drive
Then I Clicked Share from the dropdown menu
that took me to the Sharing Tab in properties
Clicked the Advanced Sharing button (note that I am not actually networked to any other computer)
Clicked the Share this folder Check Box
In the Share Name Textbox I typed "MyShare" without the quotes
Clicked the permissions button
marked all the Full control to everyone checkbox's
Clicked Apply
Clicked ok
Clicked Apply
Clicked ok
Clicked the Security Tab
Clicked the Edit button
I made sure that all of the Allow Full Control for everyone checkbox's were checked
Clicked Apply
Clicked ok
Clicked Close

After I closed that, a popup appeared for a short time, as the drive and the files and subfolder's were processed.

Then I right clicked my F drive again, clicked Share from the dropdown
and i can see the Drive letter F, and underneath it says "Shared" and below that shows the network path

Now I can use either F: or the UNC path "\\OWNER-PC\MyShare" as my save to path for a workbook

OWNER-PC is my Computer name.

- Its easy to discover the UNC path by right clicking properties > then the Share Tab

Then to make sure how to undo it all
I Right Clicked Drive F again
Clicked Share from the dropdown menu
From the Sharing Tab I Clicked the Advanced Sharing button
and un-checked the Share this folder Check Box
then Apply and Ok

(As mentioned in my first post, the reason I needed this is so I can work on projects at home and be certain various save-as procedures will function when used with the shared network drive at the office)

[frank_m]

With further testing I discovered that the same can be accomplished with a usb drive. Even a thumb drive. There is no security tab with drives that are not formatted NTSF which means it's fewer steps to configure it. - Very easy actually.