| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble saving a worsheet to a file using macros.
I have set up a macro using the following code to save my worksheet as a
seperate file using the days date as the filename eg 21012005.xls Sheets("*****").Copy strAppend = Format(Date, "DDMMYYYY") strPath = "*****" fSaveName = strPath & strAppend & ".xls" ActiveWorkbook.SaveAs fSaveName ActiveWorkbook.Close I would like to put in a code that says if that file (21012005.xls) all ready exists, add an "a" after the date,eg "21012005a.xls". Can this be done? I'm only new to using macros and any help would be greatly appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble saving a worsheet to a file using macros.
Sheets("*****").Copy
strAppend = Format(Date, "DDMMYYYY") strPath = "*****" fSaveName = strPath & strAppend & ".xls" if dir(fSaveName) < "" then fSaveName = strPath & strAppend & "a.xls" End If ActiveWorkbook.SaveAs fSaveName ActiveWorkbook.Close -- Regards, Tom Ogilvy "phoenixx153" wrote in message ... I have set up a macro using the following code to save my worksheet as a seperate file using the days date as the filename eg 21012005.xls Sheets("*****").Copy strAppend = Format(Date, "DDMMYYYY") strPath = "*****" fSaveName = strPath & strAppend & ".xls" ActiveWorkbook.SaveAs fSaveName ActiveWorkbook.Close I would like to put in a code that says if that file (21012005.xls) all ready exists, add an "a" after the date,eg "21012005a.xls". Can this be done? I'm only new to using macros and any help would be greatly appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble saving a worsheet to a file using macros.
Thank you very much Tom.
I appreciate it immensly "Tom Ogilvy" wrote: Sheets("*****").Copy strAppend = Format(Date, "DDMMYYYY") strPath = "*****" fSaveName = strPath & strAppend & ".xls" if dir(fSaveName) < "" then fSaveName = strPath & strAppend & "a.xls" End If ActiveWorkbook.SaveAs fSaveName ActiveWorkbook.Close -- Regards, Tom Ogilvy "phoenixx153" wrote in message ... I have set up a macro using the following code to save my worksheet as a seperate file using the days date as the filename eg 21012005.xls Sheets("*****").Copy strAppend = Format(Date, "DDMMYYYY") strPath = "*****" fSaveName = strPath & strAppend & ".xls" ActiveWorkbook.SaveAs fSaveName ActiveWorkbook.Close I would like to put in a code that says if that file (21012005.xls) all ready exists, add an "a" after the date,eg "21012005a.xls". Can this be done? I'm only new to using macros and any help would be greatly appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble saving a worsheet to a file using macros.
Tom, can I add an additional Q. here?
Can your modify your code so that the new created "DDMMYYYY.xls" formatted file will not contain the original file macro codes and possible control button figures on the copied sheet? Regards J_J "Tom Ogilvy" wrote in message ... Sheets("*****").Copy strAppend = Format(Date, "DDMMYYYY") strPath = "*****" fSaveName = strPath & strAppend & ".xls" if dir(fSaveName) < "" then fSaveName = strPath & strAppend & "a.xls" End If ActiveWorkbook.SaveAs fSaveName ActiveWorkbook.Close -- Regards, Tom Ogilvy "phoenixx153" wrote in message ... I have set up a macro using the following code to save my worksheet as a seperate file using the days date as the filename eg 21012005.xls Sheets("*****").Copy strAppend = Format(Date, "DDMMYYYY") strPath = "*****" fSaveName = strPath & strAppend & ".xls" ActiveWorkbook.SaveAs fSaveName ActiveWorkbook.Close I would like to put in a code that says if that file (21012005.xls) all ready exists, add an "a" after the date,eg "21012005a.xls". Can this be done? I'm only new to using macros and any help would be greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trouble opening an Excel file that contains macros | Excel Worksheet Functions | |||
| Macros making file saving extremely slow. | Excel Discussion (Misc queries) | |||
| Macros make file saving extremely slow. | Excel Programming | |||
| control box, macro security and trouble saving a file | Excel Programming | |||
| Saving file without macros | Excel Programming | |||
Linear Mode
