asked on 7/13/2011

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 Excel Visual Basic Classic

Last Comment

brl8

7/19/2011

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

7/13/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

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

brl8

7/14/2011

ASKER

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.

Thanks!!!

brl8

brl8

7/19/2011

ASKER

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:\"
Else
    MsgBox outputFile & " file not found."
End If

oWshNetwork.RemoveNetworkDrive "M:"

Open in new window