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'


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 FunctionMatt