Save Excel File to Fileserver via Macro

[tclord]

I have a macro that automatically performs a "save as", but I need to save it to a fileserver, not a local machine. The problem is that the 'save as' macro happens on an app server in one domain, and the destination location is in another domain. If I log on remotely to the app server, manually connect to the fileserver and then run the macro, it works. However, that defeats the purpose of automating this process. I need to insert a connection to the file server command into the VB macro - does anyone know how to do this? I've looked around other boards, and haven't found anything. Thanks for your help!

[mvidas]

tclord,

If you are looking to add a connection to the fileserver via code, try using the NET USE command. Helped me a lot in a previous project of mine here. Here is a modified version of what I had to do, with comments. If you don't need to use username/pw, take a look at the NET USE command on google (powerful utility) but I think you only neet to use 'net use \\server\share'


[vba]Function SetConnectionToServer() As Boolean
Dim FSO As Object, ServAddr As String, vFolder As Object
Dim vPW As String, vUser As String

'set variables
ServAddr = "\\server.domain.com\folder1\folder2\folder3\"
vUser = "tclord"
vPW = "password"

'create filesystemobject object
Set FSO = CreateObject("Scripting.FileSystemObject")

'see if connection already exists..
On Error Resume Next
Set vFolder = FSO.GetFolder(ServAddr)
On Error GoTo 0

'if no connection exists
If vFolder Is Nothing Then
'create shell object
Dim WShell As Object
Set WShell = CreateObject("wscript.shell")

'remove trailing \ from server addr (if applicable)
If Right(ServAddr, 1) = "\" Then ServAddr = Left(ServAddr, Len(ServAddr) - 1)

'delete any connections (could be bad connection)
WShell.Run "net use " & ServAddr & " /delete", WaitOnReturn:=True

'create new connection
WShell.Run "net use " & ServAddr & " " & vPW & " /user:" & vUser, WaitOnReturn:=True

Set vFolder = FSO.GetFolder(ServAddr)
Set WShell = Nothing
End If
Set FSO = Nothing
End Function[/vba]Matt