Save active sheet as "filename" on a remote server with username and password

  • Thread starter Thread starter Teddy
  • Start date Start date
T

Teddy

Hi gurus

(I know many of you are :-)

How do I sav a file on a network server I am not mapped up to?
I must provide a username and password.
How do I set in username and password for the server into the code???

v_FULLPATH_FILE_NAME =\\servername\driveletter$\folder\subfolder
\filename"

Application.ScreenUpdating = False
Inst.Copy ' name of the sheet
With ActiveSheet.Parent
Application.DisplayAlerts = False
.SaveAs Filename:=v_FULLPATH_FILE_NAME, FileFormat:=xlText
.Close SaveChanges:=False
Application.DisplayAlerts = True
MsgBox "Data saved"
End With
Application.ScreenUpdating = True
 
Teddy,

I think you will have to map to the network location. Here's an example
that will map the network location to drive Z and then unMap it at the end...

Sub trythis()

Dim strNetworkPath As String
Dim objNet As Object


strNetworkPath = "\\servername\driveletter$\folder\subfolder"

Set objNet = CreateObject("Wscript.Network")
objNet.MapNetworkDrive "Z:", strNetworkPath, False, "domain\username",
"password"

Application.ScreenUpdating = False
Inst.Copy ' name of the sheet
With ActiveSheet.Parent
Application.DisplayAlerts = False
.SaveAs Filename:=strNetworkPath & "\sample.xls", FileFormat:=xlText
.Close SaveChanges:=False
Application.DisplayAlerts = True
MsgBox "Data saved"
End With
Application.ScreenUpdating = True

objNet.RemoveNetworkdrive "Z:"
Set objNet = Nothing

End Sub
 
Teddy said:
Hi gurus

(I know many of you are :-)

How do I sav a file on a network server I am not mapped up to?
I must provide a username and password.
How do I set in username and password for the server into the code???

v_FULLPATH_FILE_NAME =\\servername\driveletter$\folder\subfolder
\filename"

Application.ScreenUpdating = False
Inst.Copy ' name of the sheet
With ActiveSheet.Parent
Application.DisplayAlerts = False
.SaveAs Filename:=v_FULLPATH_FILE_NAME, FileFormat:=xlText
.Close SaveChanges:=False
Application.DisplayAlerts = True
MsgBox "Data saved"
End With
Application.ScreenUpdating = True
Click to expand...

Hi Teddy,

It is not a good idea to store your passwords
in a text file.

I think the usual way to do what you want is
to share the folder where your files will be
saved, create a group, grant that group read/write
permissions on that folder, and add all the
users that will be saving Excel files to the group.
The details will depend on your network settings
(Workgroup or AD etc.)

You should probably contact your admin for that.

Hope this helps some.
 
Back
Top