VBA code not working when using UNC path for saving file

stildawn · Oct 12th 2016

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

**norie** · Oct 12th 2016

Re: SaveAs Failed - I think because of network UNC

Can you post the line of code rather than a picture?:)

PS One thing I notice in the picture is that you don't specify the FileFormat argument when saving.

**S O** · Oct 12th 2016

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.

stildawn · Oct 12th 2016

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

Code

Private Sub MasMPOBut_Click()


'##### Save as master file and close template file #########################
ThisWorkbook.Sheets("Settings").Range("A2").Value = 1
InstallPath = ThisWorkbook.Sheets("Settings").Range("A1").Value & "\"
MPONo = ThisWorkbook.Sheets("Master").Range("D6").Value
Vendor = ThisWorkbook.Sheets("Master").Range("D10").Value
CurDate = Format(Date, "yyyy.mm.dd")




On Error Resume Next
MkDir InstallPath & "MPO Masters"
On Error GoTo 0




MasterSavePath = InstallPath & "MPO Masters\" & CurDate & " " & MPONo & " " & Vendor




On Error GoTo DupHandler
MkDir MasterSavePath
On Error GoTo 0




ThisWorkbook.SaveAs Filename:=MasterSavePath & "\" & CurDate & " " & MPONo & " " & Vendor & " Master File" & ".xlsm"




MsgBox "New Master File has been saved at the following location:" & vbNewLine & vbNewLine & MasterSavePath & "\" & CurDate & " " & MPONo & " " & Vendor & " Master File" & ".xlsm" & vbNewLine & vbNewLine & "Please open new Master File to continue.", vbInformation, "New Master File"




ThisWorkbook.FollowHyperlink MasterSavePath




If Workbooks.Count > 1 Then
        Workbooks(1).Activate
        ThisWorkbook.Close SaveChanges:=False
    Else
        ThisWorkbook.Saved = True
        Application.Quit
End If
'#############################################################################




Exit Sub




DupHandler:
MsgBox "Master file already generated for this MPO & date, please check folders and work from already created master file.", vbExclamation, "Master File Already Created"
Exit Sub




End Sub

Display More

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.

**S O** · Oct 13th 2016

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:

Code

#If Win64 Then
    Declare PtrSafe Function WNetCancelConnection2 Lib "mpr.dll" Alias "WNetCancelConnection2A" (ByVal lpName As String, ByVal dwFlags As Long, ByVal fForce As Long) As Long
    Declare PtrSafe Function WNetAddConnection2 Lib "mpr.dll" Alias "WNetAddConnection2A" (lpNetResource As NETCONNECT, ByVal lpPassword As String, ByVal lpUserName As String, ByVal dwFlags As Long) As Long
#Else
    Declare Function WNetCancelConnection2 Lib "mpr.dll" Alias "WNetCancelConnection2A" (ByVal lpName As String, ByVal dwFlags As Long, ByVal fForce As Long) As Long
    Declare Function WNetAddConnection2 Lib "mpr.dll" Alias "WNetAddConnection2A" (lpNetResource As NETCONNECT, ByVal lpPassword As String, ByVal lpUserName As String, ByVal dwFlags As Long) As Long
#End If




Const CONNECT_UPDATE_PROFILE    As Long = &H1
Const RESOURCE_CONNECTED        As Long = &H1
Const RESOURCE_GLOBALNET        As Long = &H2
Const RESOURCETYPE_DISK         As Long = &H1
Const RESOURCEDISPLAYTYPE_SHARE As Long = &H3
Const RESOURCEUSAGE_CONNECTABLE As Long = &H1




Type NETCONNECT
    dwScope         As Long
    dwType          As Long
    dwDisplayType   As Long
    dwUsage         As Long
    lpLocalName     As String
    lpRemoteName    As String
    lpComment       As String
    lpProvider      As String
End Type




Function MapNetworkDrive(ByVal driveLetter As String, ByVal UNC As String) As Boolean
    Dim dl As String * 1
    Dim nc As NETCONNECT
    
    dl = UCase$(driveLetter)
    
    nc.dwScope = RESOURCE_GLOBALNET
    nc.dwType = RESOURCETYPE_DISK
    nc.dwDisplayType = RESOURCEDISPLAYTYPE_SHARE
    nc.dwUsage = RESOURCEUSAGE_CONNECTABLE
    nc.lpLocalName = driveLetter & ":"
    nc.lpRemoteName = UNC
    
    MapNetworkDrive = (WNetAddConnection2(nc, vbNullString, vbNullString, CONNECT_UPDATE_PROFILE))
    
End Function




Function DisconnectNetworkDrive(driveLetter As String) As Boolean
    Dim dl As String * 1
    Dim nc As NETCONNECT
    
    nc.dwScope = RESOURCE_GLOBALNET
    nc.dwType = RESOURCETYPE_DISK
    nc.dwDisplayType = RESOURCEDISPLAYTYPE_SHARE
    nc.dwUsage = RESOURCEUSAGE_CONNECTABLE
    nc.lpLocalName = driveLetter & ":"
    nc.lpRemoteName = vbNullString
    
    DisconnectNetworkDrive = Not (WNetCancelConnection2(dl, CONNECT_UPDATE_PROFILE, False))
End Function

Display More

Used like so:

Code

'// To map a drive
connected = MapNetworkDrive("Z", "\\server\path\fileshare")


'// To disconnect a drive
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

stildawn · Oct 13th 2016

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?

**S O** · Oct 13th 2016

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.

stildawn · Oct 13th 2016

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

**S O** · Oct 17th 2016

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

Sorry I completely forgot about this. I've not got a lot of time in the next couple of days but I'll try and post something for you to look at.

stildawn · Oct 17th 2016

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

Thanks S O

**S O** · Oct 18th 2016

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:

Code

Function DriveExists(driveLetter As String) As Boolean


    Dim dl As String * 1
    Dim strPattern As String
    
    dl = UCase$(driveLetter)
    
    For Each d In CreateObject("Scripting.FileSystemObject").Drives
        strPattern = strPattern & d.driveLetter
    Next
    
    DriveExists = (dl Like "[" & strPattern & "]")
    
End Function

Display More

**S O** · Oct 18th 2016

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

Sub MapDrive(driveLetter As String, fileShare As String)
    driveLetter = UCase$(Left$(driveLetter, 1)) & ":"
    CreateObject("WScript.Shell").Run "CMD /C NET USE " & driveLetter & " """ & fileShare & """ /PERSISTENT:NO", 0, 1
End Sub


Sub RemoveDrive(driveLetter As String)
    driveLetter = UCase$(Left$(driveLetter, 1)) & ":"
    CreateObject("WScript.Shell").Run "CMD /C NET USE " & driveLetter & " /DELETE", 0, 1
End Sub

Participate now!

Share