Save Macro w Suggested File Name to Network Drive

  • Thread starter Thread starter John Michaels
  • Start date Start date
J

John Michaels

I'm attempting the following macro which saves the file to a network
drive (with no errors)


ChDir "J:\"

ActiveWorkbook.SaveAs Filename:="J:\User.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ThisWorkbook.Close savechanges:=True

End Sub


so that the file will refer to a cell within the worksheet for the name
of the file to be saved to the same network drive


Range("B30").Select

ChDir "J:\"

ActiveWorkbook.SaveAs Filename:=ActiveCell.Value,
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False

ThisWorkbook.Close savechanges:=True

but as written, it saves to 'My Documents' vs the network drive. I'm
assuming that the "network drive letter" needs to be included with
[Filename:=ActiveCell.Value] but I've not been able to figure out how
to do it. Any help would be greatly appreciated!
 
Hello John,

Try setting the drive first then the directory.

ChDrive "J"
ChDir("J:\")

That should solve the problem.

Sincerely,
Leith Ross
 
Back
Top