saving to a unc path 1

[colinmitton]

I'm trying to save a document to same folder everytime but prompt the user for a file name. The folder is located on a server and has a UNC path. its part of a bigger macro but the relevent code is below :

Dim filename As String
filename = InputBox("Name for new file?")
If filename <> "" Then
ChDir "\\server\reports\"
With ActiveWorkbook
.SaveAs filename
End With
End If

The code works if I use "z:\server\reports" but I dont want to give out a drive letter as thats going to get too complicated with the different setup I have inherited here!

I'm not sure how to work with this one as my VBA is very limited.

Thanks in Advance.

 

[Andrzejek]

Works for me:

Dim filename As String

filename = InputBox("Name for new file?")

If filename <> "" Then
   ActiveWorkbook.SaveAs Filename:="\\server\reports\Junk.xls", _
   FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
   ReadOnlyRecommended:=False, CreateBackup:=False
End If

No need for: ChDir "\\server\reports\"

Make sure you validate for any *?/\ etc in your file name and make sure your UNC path is correct - mine starts with \\ntsvr

Have fun.

---- Andy

 

[colinmitton]

Thanks, I've adjusted my macro to:

Dim filename As String
Dim PathName As String
PathName = "\\bh_server\reporting"
filename = InputBox("Name for new file?")
If filename <> "" Then
ActiveWorkbook.SaveAs (PathName & "\" & filename)
End If

Which now works fine, I was getting too bogged down with the chdir!