7

I need to access via VBA a folder hosted on a Network File Server. The folder is accessible in writing only via a Service Account (different from normal user accounts) for which I do have username and password.

Via the UI I can see that folder and map it as a local drive but in order to access it in writing I need to log off from Windows and logon via the Service Account.

Is there any way to access the network folder during a normal user session but hardcoding username and pwd in the VBA code?

I did try mapping the folder as local drive with:

Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.MapNetworkDrive "S:", "\\corp-server\HostingFolder", False, Username, pwd

but did not work ("S" drive was not mapped). If instead I do the same but without providing Username and password:

Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.MapNetworkDrive "S:", "\\corp-server\HostingFolder"

it works perfectly.

Wondering now if what I am trying to do is actually possible? If not, is there any alternative?

Thanks

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
cfugge
  • 83
  • 1
  • 1
  • 6

1 Answers1

8

You might find this answer of value in your testing.

Essentially, I would check a couple things...

  1. Make sure you are not already connected to this resource using the current logged in user. If you are, you might get an error message like the following: enter image description here

  2. Make sure you are using the domain\username syntax in your username.

Otherwise I think you are the right track. I put together some sample code based on the link above, and was able to successfully connect to a network share under a different user name and iterate through a list of files.

(Note the tip that you don't actually have to map a drive to establish a connection.)

The following code is a really quick (working) VBA implementation of the sample listed at Access network share from within VBScript eg FileSystemObject 

Public Sub TestNetShareName()

    Dim NetworkObject As Object
    Dim FSO As Object
    Dim Directory As Object
    Dim Filename As Object
    Dim ServerShare As String
    Dim UserName As String
    Dim Password As String

    ServerShare = "\\corp-server\HostingFolder"
    UserName = "mydomain\myuser"
    Password = "freddie123"

    Set NetworkObject = CreateObject("WScript.Network")
    Set FSO = CreateObject("Scripting.FileSystemObject")

    NetworkObject.MapNetworkDrive "", ServerShare, False, UserName, Password

    Set Directory = FSO.GetFolder(ServerShare)
    For Each Filename In Directory.Files
        Debug.Print Filename.Name
    Next

    Set Filename = Nothing
    Set Directory = Nothing
    Set FSO = Nothing

    NetworkObject.RemoveNetworkDrive ServerShare, True, False

    Set NetworkObject = Nothing

End Sub
Community
  • 1
  • 1
AdamsTips
  • 1,648
  • 17
  • 22
  • Great stuff, thanks! As I am currently working from home I could only try your code via VPN and did not work (returned a "wrong username or password" error).Tried with and without domain in the username. I was also able to reproduce your error above (multiple connections) by opening the target folder in via Explorer. I need now to test when back in office but this sounds overall good to me and "must" work. If you believe there is something else I can try just let me know otherwise let's talk on Monday! – cfugge Dec 17 '14 at 17:30
  • This worked perfectly for me. Many thanks for a so clean piece of coding. – cfugge Dec 22 '14 at 13:37
  • Hey this code is awesome. Worked first time with little to no massaging. Cheers! – Wookies-Will-Code Jan 10 '18 at 22:45
  • 1
    What about if user is already connected? How can disconnect session by `VBA`? Or use existing connection. – Harun24hr Aug 18 '20 at 07:25