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

 

[Jonmo1]

also the message comes up with a box saying where it is to be stored

Is it correct?
please look VERY CLOSELY...

according to the code provided by macro recorder it should look like this
Z:\test\Dock Work Sheet blank.xls

 

[ginner159]

the message looked like this

in response to the merged cells Ive unmerged them and will try again when im back at work tommorrow.

thanks again for all your help guys

 

[ginner159]

its still not working without the merged cells. any other thoughts

 

[ginner159]

anyone really need to get this sorted

 

[ginner159]

well ive managed to do it found it on another website, but ive edited it a little to match my file directorys. its acived by changing the save as function directly there is just one little niggle im having now.

its saving the file and opening the save as box. what i want it do is open the folder i specified in the code but let me then selct the sub folder it needs to be saved in.

heres the code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Saves filename as value of Boat name plus customer and the date due in Wide 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("B6").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")

ActiveWorkbook.SaveAs Filename:="Z:\" & newFile
End Sub

the folder structure is as follows

Z:\
Z:\Dock Work\
Z:\Dock Work\Narrow Dock\
Z:\Dock Work\Wide Dock\

to clear up i want to be able to save as to either the wide or narrow after the save as opens

 

[ginner159]

bumb plz really nead help

 

[ginner159]

sorted it! please close this thread and thanks for your help

 

[_John_]

Just wanted to say thanks to all involved in this thread. It helped me bunches with what I was trying to do here.

How did you resolve your final problem though ginner159?

Regards
John

 

[ginner159]

i just re recorded a normal save macro just saving a file to the networked drive then added the extra bits to make it achieve the file name desired