Macro to save to LAN using cell reference for file name.

Posted by Gary Hewitt-Long on November 14, 2001 11:01 AM

Hi,

anyone know how to change this macro? I am trying to save a file to another computer on a network naming it with the contents of sheet NAME!A1 which happens to be the date (month and year).
When I run the macro it will only save it in the same directory as the original file.

Sub archive()
'
' archive Macro
' Macro recorded 14/11/01 by Gary Hewitt-Long
'

'
Windows("SECONDS.xls").Activate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save
ChDir "\\Lisa\c\Seconds_archive"
ActiveWorkbook.SaveAs FileName:="\\Lisa\c\Seconds_archive\SECONDS.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ThisFile = Range("NAME!A1").Value
ActiveWorkbook.SaveAs FileName:=ThisFile, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close
ChDir "C:\Steve\Steve Share"
Workbooks.Open FileName:="C:\Steve\Steve Share\SECONDS.xls"
End Sub

By the way I tried in the above macro to save the file first, then save under the original name on the LAN computer, close the file, then open the file and then save, but it still saves in the original directory of the original file, that the macro was run in to begin with, AAAARRRRGGGGHHH.

Hope someone can help.

Regards,

Gary Hewitt-Long

Posted by Dan on November 14, 2001 11:23 AM

Non-VBA expert here giving advice!

Try changing:
ThisFile = "\\Lisa\c\Seconds_archive" & Range("NAME!A1").Value

*OR* change the second SaveAs line to read:

ActiveWorkbook.SaveAs FileName:="\\Lisa\c\Seconds_archive" & ThisFile, (etc....)

Does that help? archive Macro Macro recorded 14/11/01 by Gary Hewitt-Long

Posted by Gary Hewitt-Long on November 14, 2001 12:12 PM

Don't bother, I have worked it out :o)

Concatenated the directory in the sheet that it gets the name from so Value becomes the new directory i.e.

=CONCATENATE ("\\Lisa\c|Seconds_archive\",B2,".xls")

giving new path name along with the name.

\\Lisa\c\Seconds_archive\November-2001.xls

Regards,

Gary Hewitt-Long

Posted by Gary Hewitt-Long on November 14, 2001 1:10 PM

Thanks, I managed it in the end, by going the long way round. I was thinking along the lines of renaming the file, which meant I had to concatenate the final directory along with the new file name. When I got that far I thought hang on, why don't I do that with the first part and refernce that - hey presto it worked. Thanks anyway.

Regards,

Gary Hewitt-Long
(another VBA non expert)