I'm working on a template that will house several types of data --
some inputted directly into the workbook, other data being linked to
multiple external workbooks. When I say 'multiple', I'm thinking one
for every day of the year!

In order to make the links to these external workbooks, I'd like to
first create the exact file structure -- users can simply then 'save-
as' over top of the existing workbook. Is there a way to create this
file structure using VBA? That is, can I have Excel repeatedly 'save-
as' the current workbook, using the date as the file name?

The desired path would look like this:
\\server\folder1\folder2\StoreName\January1.xls
\\server\folder1\folder2\StoreName\January2.xls
\\server\folder1\folder2\StoreName\January3.xls
\\server\folder1\folder2\StoreName\January4.xls
etc....

Ideas?

TIA, Ray

spath = "\\server\folder1\folder2\StoreName\"
Thisworkbook.SaveAs sPath & format(date,"mmmmd") & .xls

It might be better to use


Thisworkbook.SaveAs sPath & format(date,"mmmmdd") & .xls
--
Regards,
Tom Ogilvy



"Ray" wrote:

> I'm working on a template that will house several types of data --
> some inputted directly into the workbook, other data being linked to
> multiple external workbooks. When I say 'multiple', I'm thinking one
> for every day of the year!
>
> In order to make the links to these external workbooks, I'd like to
> first create the exact file structure -- users can simply then 'save-
> as' over top of the existing workbook. Is there a way to create this
> file structure using VBA? That is, can I have Excel repeatedly 'save-
> as' the current workbook, using the date as the file name?
>
> The desired path would look like this:
> \\server\folder1\folder2\StoreName\January1.xls
> \\server\folder1\folder2\StoreName\January2.xls
> \\server\folder1\folder2\StoreName\January3.xls
> \\server\folder1\folder2\StoreName\January4.xls
> etc....
>
> Ideas?
>
> TIA, Ray
>
>

On Feb 21, 10:26 am, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> spath = "\\server\folder1\folder2\StoreName\"
> Thisworkbook.SaveAs sPath & format(date,"mmmmd") & .xls
>
> It might be better to use
>
> Thisworkbook.SaveAs sPath & format(date,"mmmmdd") & .xls
> --
> Regards,
> Tom Ogilvy
>
> "Ray" wrote:
> > I'm working on a template that will house several types of data --
> > some inputted directly into the workbook, other data being linked to
> > multiple external workbooks. When I say 'multiple', I'm thinking one
> > for every day of the year!
>
> > In order to make the links to these external workbooks, I'd like to
> > first create the exact file structure -- users can simply then 'save-
> > as' over top of the existing workbook. Is there a way to create this
> > file structure using VBA? That is, can I have Excel repeatedly 'save-
> > as' the current workbook, using the date as the file name?
>
> > The desired path would look like this:
> > \\server\folder1\folder2\StoreName\January1.xls
> > \\server\folder1\folder2\StoreName\January2.xls
> > \\server\folder1\folder2\StoreName\January3.xls
> > \\server\folder1\folder2\StoreName\January4.xls
> > etc....
>
> > Ideas?
>
> > TIA, Ray

Thanks Tom ... but how do I get the macro to scroll through each date
to create the files? In other words, I want to create all 365 files
today!

I'm thinking that I could enter the dates in cells A1:A365, but I'm
not sure how to modify your code to make Excel progress down the
column, saving a new file for each date?

ray

Sub ABC()
Dim spath as String, i as Long, dt as Date
spath = "\\server\folder1\folder2\StoreName\"
for i = 1 to 365
dt = dateserial(2007,1,i)
Thisworkbook.SaveAs sPath & format(dt,"mmmmd") & .xls
Next
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

> spath = "\\server\folder1\folder2\StoreName\"
> Thisworkbook.SaveAs sPath & format(date,"mmmmd") & .xls
>
> It might be better to use
>
>
> Thisworkbook.SaveAs sPath & format(date,"mmmmdd") & .xls
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ray" wrote:
>
> > I'm working on a template that will house several types of data --
> > some inputted directly into the workbook, other data being linked to
> > multiple external workbooks. When I say 'multiple', I'm thinking one
> > for every day of the year!
> >
> > In order to make the links to these external workbooks, I'd like to
> > first create the exact file structure -- users can simply then 'save-
> > as' over top of the existing workbook. Is there a way to create this
> > file structure using VBA? That is, can I have Excel repeatedly 'save-
> > as' the current workbook, using the date as the file name?
> >
> > The desired path would look like this:
> > \\server\folder1\folder2\StoreName\January1.xls
> > \\server\folder1\folder2\StoreName\January2.xls
> > \\server\folder1\folder2\StoreName\January3.xls
> > \\server\folder1\folder2\StoreName\January4.xls
> > etc....
> >
> > Ideas?
> >
> > TIA, Ray
> >
> >

On Feb 21, 11:04 am, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> Sub ABC()
> Dim spath as String, i as Long, dt as Date
> spath = "\\server\folder1\folder2\StoreName\"
> for i = 1 to 365
> dt = dateserial(2007,1,i)
> Thisworkbook.SaveAs sPath & format(dt,"mmmmd") & .xls
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Tom Ogilvy" wrote:
> > spath = "\\server\folder1\folder2\StoreName\"
> > Thisworkbook.SaveAs sPath & format(date,"mmmmd") & .xls
>
> > It might be better to use
>
> > Thisworkbook.SaveAs sPath & format(date,"mmmmdd") & .xls
> > --
> > Regards,
> > Tom Ogilvy
>
> > "Ray" wrote:
>
> > > I'm working on a template that will house several types of data --
> > > some inputted directly into the workbook, other data being linked to
> > > multiple external workbooks. When I say 'multiple', I'm thinking one
> > > for every day of the year!
>
> > > In order to make the links to these external workbooks, I'd like to
> > > first create the exact file structure -- users can simply then 'save-
> > > as' over top of the existing workbook. Is there a way to create this
> > > file structure using VBA? That is, can I have Excel repeatedly 'save-
> > > as' the current workbook, using the date as the file name?
>
> > > The desired path would look like this:
> > > \\server\folder1\folder2\StoreName\January1.xls
> > > \\server\folder1\folder2\StoreName\January2.xls
> > > \\server\folder1\folder2\StoreName\January3.xls
> > > \\server\folder1\folder2\StoreName\January4.xls
> > > etc....
>
> > > Ideas?
>
> > > TIA, Ray

Works perfectly ... thanks Tom!