VBA code not working when using UNC path for saving file

  • Hi All


    I have a tool I've been developing, and basically when you run it for the first time, it asks you to point to a save location (which it then uses in future when making different files and saving different things).


    My end user is getting an error on one of the save as commands. Here is the issue step by step:


    Full path saved for useis: \\nzsrv01\companynz\CompanyShared\CLIENT RATES & INFORMATION\I\ICEBREAKER\CT BOOKINGS\2_SPO\SPO Masters\11.10_GHF


    When the user runs the code this happens:
    [ATTACH=CONFIG]70407[/ATTACH]


    Debugging to this line of code:
    [ATTACH=CONFIG]70408[/ATTACH]


    You see its just a save as command. Now this works perfectly if the installpath (which is the full unc path listed above in this case) is a normal like "C:\Blah" path. But it seems to be throwing this error due to the unc network path, even though as far as I know Excel vba should accept these paths.


    Any ideas/tests I can do to figure this out.


    I know I could map the network drive, but eventually multiple users will be using this so would rather avoid having the users do any mapping etc.


    Thanks

  • Re: SaveAs Failed - I think because of network UNC


    I'm pretty sure the MkDir command doesn't support UNC paths - but you won't see that in your code because of "On Error Resume Next"


    If it can't create the folder, then your save path is invalid.


    One option could be to actually map the drive within the code, and then disconnect it when you've finished.

  • Re: SaveAs Failed - I think because of network UNC


    Here is the code instead of the picture (sorry that was the picture sent by the user):




    Quote from S O;779068

    I'm pretty sure the MkDir command doesn't support UNC paths - but you won't see that in your code because of "On Error Resume Next"


    If it can't create the folder, then your save path is invalid.


    One option could be to actually map the drive within the code, and then disconnect it when you've finished.


    Ahhhh that could make sense, so the real error could be that it can't make a directory on a UNC path.


    Is there another make directory command that would work with UNC?


    Or alternatively, can you advise how I would map the drive within the code, never done anything like that before. Would mapping/disconnecting cause any issues with their other windows experience? Like I assume they use the network drive for other work etc and I don't want code that would mess up their other experiences in using the network drive.

  • Re: VBA code not working when using UNC path for saving file


    Hi stildawn,


    This is something I've been using for setting up and removing network drives. You can just dump this in a module and call the functions in your project with the required arguments:



    Used like so:


    Code
    1. '// To map a drive
    2. connected = MapNetworkDrive("Z", "\\server\path\fileshare")
    3. '// To disconnect a drive
    4. disconnected = DisconnectNetworkDrive("Z")


    I've used "Z" as an example but you can use any letter as long as that drive isn't already mapped. I've also assumed you don't need a username/password to connect to the fileshare - if you do then the code will need to be modified slightly.


    Edit: I'm not sure why some of the code is being highlighted red in the above post - but it works fine in the VBE

  • Re: VBA code not working when using UNC path for saving file


    Thanks S O looks intense.


    Is there anyway to test this? I unmapped one of my network drives and then tried to remap and disconnect using the code while watching my computer haha, but nothing seemed to happen?


    Is there also a way to check for already mapped letters so I can build that in?

  • Re: VBA code not working when using UNC path for saving file


    Hi stildawn,


    You can add a breakpoint inside the function so that the code halts and then use F8 to step through each line and check it.


    There red is a way to list drives but I'm on my phone at the moment and about to go to sleep, I'll post a function up tomorrow for you if someone hasn't replied by then.

  • Re: VBA code not working when using UNC path for saving file


    Yeah I know how to step through code haha, I have done that a few times, but I haven't noticed (by watching my computer and refreshing it) the mapping working from the code?


    Anyway have a good sleep, no rush on this :)

  • Re: VBA code not working when using UNC path for saving file


    Here's a function that you can use to see if the drive letter has already been mapped:


  • Re: VBA code not working when using UNC path for saving file


    The other thing you can do to map/unmap is use a command prompt:


    Code
    1. Sub MapDrive(driveLetter As String, fileShare As String)
    2. driveLetter = UCase$(Left$(driveLetter, 1)) & ":"
    3. CreateObject("WScript.Shell").Run "CMD /C NET USE " & driveLetter & " """ & fileShare & """ /PERSISTENT:NO", 0, 1
    4. End Sub
    5. Sub RemoveDrive(driveLetter As String)
    6. driveLetter = UCase$(Left$(driveLetter, 1)) & ":"
    7. CreateObject("WScript.Shell").Run "CMD /C NET USE " & driveLetter & " /DELETE", 0, 1
    8. End Sub