Help saving file to network location VBA

[ginner159]

sorry if posted in wrong place ive serched around and tryied various options but to no avail.

i need to save a file accrossthe local network. i have set it up as a macro and works fine if saving on the same computer as the file was set up. i then copyied and changed the code to try make it save to a shared folder on another pc... failed... mapped the drive and tried it that way ... failed.

its my first time really using VB/VBA so please be kind

Sub Saveoffice()
     'Saves filename as Boat name plus customer and the date due in Dock
 
    Dim newFile As String, fName As String, fName1 As String, fName2 As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("B3").Value
    fName1 = Range("B4").Value
    fName2 = Range("H9").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & fName1 & " " & Format$(fName2, "dd-mm-yyyy")
     ' Change directory to suit your PC, including USER NAME
    ChDrive "Z:\"
    ChDir "Z:\test"
    ActiveWorkbook.SaveAs Filename:=newFile
 
End Sub

 

[VoG]

Try

Sub Saveoffice()
     'Saves filename as Boat name plus customer and the date due in Dock
 
    Dim newFile As String, fName As String, fName1 As String, fName2 As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("B3").Value
    fName1 = Range("B4").Value
    fName2 = Range("H9").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & fName1 & " " & Format$(fName2, "dd-mm-yyyy")
     ' Change directory to suit your PC, including USER NAME
    ActiveWorkbook.SaveAs Filename:="Z:\test\" & newFile
 
End Sub

 

[ginner159]

just tried it and got,

run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed

the z: drive is mapped to the shareddocs on Office2 if that helps at all

 

[VoG]

Maybe

Sub Saveoffice()
     'Saves filename as Boat name plus customer and the date due in Dock
 
    Dim newFile As String, fName As String, fName1 As String, fName2 As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("B3").Value
    fName1 = Range("B4").Value
    fName2 = Range("H9").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & fName1 & " " & Format$(fName2, "dd-mm-yyyy") & ".xls"
     ' Change directory to suit your PC, including USER NAME
    ActiveWorkbook.SaveAs Filename:="Z:\test\" & newFile, FileFormat:=56
 
End Sub

 

[ginner159]

nope same error code thanks for all your help btw its racking my brain

 

[VoG]

What code does the macro recorder give you if you record a macro whilst manually saving to that location?

 

[ginner159]

What code does the macro recorder give you if you record a macro whilst manually saving to that location?

If i record one the VBA read out is this;

Sub savetodrive()
'
' savetodrive Macro
' Macro recorded 16/06/2011 by Default_User
'
'
ActiveWorkbook.SaveAs Filename:="Z:\test\Dock Work Sheet blank.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

obviously im guessing if i wanted the file to be saved with the required name i would have to add the lines that to this so that it would.

If it helps i could send you the blank template file?

 

[VoG]

What is in B4 and B5?

What does the message box give you

Sub Saveoffice()
     'Saves filename as Boat name plus customer and the date due in Dock
 
    Dim newFile As String, fName As String, fName1 As String, fName2 As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("B3").Value
    fName1 = Range("B4").Value
    fName2 = Range("H9").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & fName1 & " " & Format$(fName2, "dd-mm-yyyy") & ".xls"
     ' Change directory to suit your PC, including USER NAME
     MsgBox "Trying to save as" & vbNewLine & "Z:\test\" & newFile
    ActiveWorkbook.SaveAs Filename:="Z:\test\" & newFile, FileFormat:=56
 
End Sub

 

[ginner159]

ive done a screen print for you of the top of the form which the macro is referancing

sorry about size dont know how to reduce smaller.

also the message comes up with a box saying where it is to be stored, click ok and the error 1004 again


Edit:
Heres the link to the actual file
http://dl.dropbox.com/u/22829037/Dock%20Work%20Sheet%20blank.xls

 

[VoG]

That looks like you have merged cells. Merged cells and macros generally do not mix well. Try this to change them to centred across http://www.mrexcel.com/forum/showthread.php?t=335123