Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there any way to create a folder structure for tabs in a workbo | Excel Discussion (Misc queries) | |||
how can I create a dummy variable in Excel? | Excel Discussion (Misc queries) | |||
How do I create a Work Breakdown Structure (WBS)? | Excel Discussion (Misc queries) | |||
Create Directory Structure from Cell Value. | Excel Discussion (Misc queries) | |||
closing dummy file | Excel Programming |