Automatically Saving A Workbook

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.

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."


Papa Waigo wrote:
> 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.
>
>
> --
> Papa Waigo
> ------------------------------------------------------------------------
> Papa Waigo's Profile: http://www.excelforum.com/member.php...o&userid=36074
> View this thread: http://www.excelforum.com/showthread...hreadid=558861

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

--
Regards,
Tom Ogilvy



"Papa Waigo" wrote:

>
> 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.
>
>
> --
> Papa Waigo
> ------------------------------------------------------------------------
> Papa Waigo's Profile: http://www.excelforum.com/member.php...o&userid=36074
> View this thread: http://www.excelforum.com/showthread...hreadid=558861
>
>

Thank you for your help here

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

any ideas?

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

--
Regards,
Tom Ogilvy


"Papa Waigo" wrote:

>
> Thank you for your help here
>
> This code gives me a "subscript out of range" error
>
> any ideas?
>
>
> --
> Papa Waigo
> ------------------------------------------------------------------------
> Papa Waigo's Profile: http://www.excelforum.com/member.php...o&userid=36074
> View this thread: http://www.excelforum.com/showthread...hreadid=558861
>
>