Create dummy file structure?
 

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

Create dummy file structure?
 

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

Create dummy file structure?
 

On Feb 21, 10:26 am, 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

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

Create dummy file structure?
 

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

Create dummy file structure?
 

On Feb 21, 11:04 am, Tom Ogilvy
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!