Automatically Saving A Workbook

[Papa Waigo]

I would like to be able to save a workbook using a macro, that will save
the file in a particular loaction e.g. C:\My Documents\Spreadsheet
Files\Example Spreadsheet.xls and have the directory created for those
users for whom it does not exist.

I would also like the file to be named according to the contents of
cell c2 on a sheet named "list"

Any help would be much appreciatted.

 

[markwalling]

answers? no. guidance to where you could find help on using the file
system? yes:

the visual basic help file is pretty useless to beginers, but if you
are pointed in the right direction it can be very valuable.

here is a technical article from msdn about the FileSystemObject. that
should be a good starting place. Most of the methods are linked in the
msdn, otherwise type it into visual basic and hit F1.

any more questions feel free to post them.

MW

"if you give a man a fish, you feed him for a day. if you teach a man
to fish, you feed him for a lifetime."

 

[Guest]

Sub SaveFile()
Dim s as String, sName as String
s = "C:\My Documents\Spreadsheet Files\"
On Error Resume Next
Mkdir "C:\My Documents"
MkDir "C:\My Documents\Spreadsheet Files"
On Error goto 0
sName = ActiveWorkbook _
.Worksheets("List").Range("c2").Text & ".xls"
if dir(s & sName) <> "" then
kill s & sName
End if
ActiveWorkbook.SaveAs s & sName
end sub

 

[Papa Waigo]

Thank you for your help here

This code gives me a "subscript out of range" error

any ideas?

 

[Guest]

That would be true if the activeworkbook doesn't have a worksheet with a name
of LIST.

 

[Papa Waigo]

Many thanks again, I can see what I've done wrong, this works
perfectly.

How would i change this so that if the filename already exists a msgbox
appears stating that is the case, and the macro would stop running?