using vba to copy file to a network share with username and password

I have an access project and I am using the file dialog to allow a user to select a file. I need to copy this file to a network share that is not mounted as a drive, since I do not want anyone access to the shared folder, only the application.

So I need to be able to pass the username and password in the copy command somehow. I tried copy (xcopy) and cannot seem to pass username, password information. I could perhaps use net use to mount it, copy the file, then unmount it. If that turns out is the only solution, how do I find the first available drive letter to use for mounting.

drakkarnoirAsked:
Who is Participating?
 
David LeeConnect With a Mentor Commented:
Well, I don't know if this is what you're looking for, but it gets the job done.  I don't know what you consider to be "very very slow" so this might be too slow to.  I tested it by copying a 15mb file over an 11mbps wireless network.  It took about 30 seconds in that setting.  Hope this helps.

set objFSO = CreateObject("Scripting.FileSystemObject")
set wshNet = CreateObject("WScript.Network")
strUsername = "Domain\Username"
strPassword = "Password"
strSource = "Path and file to copy"
strDestination = GetNextLetter("F")     '<-- You can change the letter.  It represents the starting letter to work from.
wshNet.MapNetworkDrive strDestination, "\\Server\Share", , strUsername, strPassword
objFSO.CopyFile strSource, strDestination & "\"
wshNet.RemoveNetworkDrive strDestination, True
Set objFSO = Nothing
Set wshNetwork = Nothing

'The following function is from: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q303209&ID=KB;EN-US;Q303209
Function GetNextLetter(DriveLetter)
   'Start x at the ascii value of the drive letter to start the search
   'unless something is passed in. This sample uses capital letters and
   'starts at F.
   If IsEmpty(DriveLetter) Then
    x = 70
   Else
    x = Asc(DriveLetter)
   End If
   
   Set oDrives = wshNet.EnumNetworkDrives

   'Step by two since the first item in the collection is the drive letter
   'and the second item is the network mapping
   For i = 0 To oDrives.Count - 1 Step 2
    If Chr(x) & ":" = oDrives.Item(i) Then
        x = x + 1
    End If
   Next
   
   GetNextLetter = Chr(x) & ":"
End Function
0
 
drakkarnoirAuthor Commented:
I tried using Shell() to do a net use to mount the network share, then filecopy to copy it, then net use to delete the drive. it is very very slow that way, so I hope there may be a better alternative
0
 
drakkarnoirAuthor Commented:
that works good, my anti-virus popped up when doing Set oDrives = wshNet.EnumNetworkDrives but I can work around that, thanks.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
David LeeCommented:
You're welcome.  Glad I could help.
0
 
andybrookeCommented:
I tried this but get object required error 424. And debugs to Set oDrives = wshNet.EnumNetworkDrives
0
 
David LeeCommented:
Hi, andybrooke.

That would mean that the object wshNet wasn't created successfully on line #2 of the code or was destroyed prematurely.  Can you run the code from a debugger to see what's happening?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.