Avatar of brl8
brl8Flag for United States of America

asked on 

VBA excel - save to a UNC path that requires a username and password

Hi All,

I have a macro that saves two spreadsheets to a UNC path (the path is to a sharepoint list) which, in order to open in windows explorer, you need to provide credentials. If I have the directory open in windows explorer (already having provided my credentials) then the following line of code works. If I don't already have the directory open, the code fails with the error message" "Run-time error '1004': Incorrect function."

wb.SaveAs Filename:=outputfile, FileFormat:=51

Open in new window

My question is: how do I adjust my saveAs code to pass credentials so that I can save to this UNC path without user intervention?

I've also tried the following lines of code. This one works and pulls up the Windows explorer window:
ShellExecute 0, "open", "C:\Documents and Settings", 0, 0, 1

Open in new window

This one does not work - there is no error but nothing happens:

ShellExecute 0, "open", "\\MyServer\MyLibrary\Staff Directory", 0, 0, 1

Open in new window

So, I'm pretty sure this is due to the fact that a username and password are required. Does anyone know how to either prompt for a username and password or somehow pass them in using ShellExecute?

Any suggestions will be appreciated!
Microsoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of brl8
Flag of United States of America image


Thanks for the reply.  Yes, it has been a few months!  I am about to go on maternity leave and need to wrap up a few loose ends.  I'm going to look into this and I'll let you know what I find out.


Avatar of brl8
Flag of United States of America image


Thanks!  That thread pointed me in the right direction.  Here is what I ended up doing:

Set oWshNetwork = CreateObject("WScript.Network")
Set fso = CreateObject("Scripting.FileSystemObject")
'map a drive to the sharepoint server
On Error Resume Next
    oWshNetwork.MapNetworkDrive "M:", strPath, "False", strUser, strPwd
If Err.Number <> 0 Then
    MsgBox "Error accessing \\mySharePointSite\myLibrary\Staff Directory.  Please verify that you are on the network."
    SaveNewDir = 1
    Exit Function
End If

'copy file to the newly mapped M:\ drive.
If fso.FileExists(outputFile) Then
    fso.CopyFile outputFile, "M:\"
    MsgBox outputFile & " file not found."
End If

oWshNetwork.RemoveNetworkDrive "M:"

Open in new window

Visual Basic Classic
Visual Basic Classic

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo