save as and add a timestamp

[dalex]

Hello,

I have a file called Main.xls. I would like to learn of some code that, when opened, will save a file called "Days.csv" to "Days (month, day, year).xls".

In simpler words, my objective is to obtain a code that will take Days.csv and copy it as a new file with the same name but add a timestamp to the end of it.

Note that the code needs to recognize that the original file is .csv (not sue if that is an i portant factor).

I've spent a couple of hours going through past reponses but am having a challenging time to develop code of my own.

Thank you very much,

 

[Loomah]

Hi dalex
You say you want to learn? You could start by recording yourself opening your .csv file in Excel (TOOLS>MACRO>RECORD NEW MACRO)
You can then edit this code (ALT+F8, Edit)

Once this .csv file has been opened you can then save it. To get the date part of your file name you will need something like this added to the bottom of your recorded code

ActiveWorkbook.SaveAs Filename:="Days" & Format(Now, "mmddyy") & ".xls"

This part of the code - Filename:="Days" will save your file to the default file location. If you want it somewhere else then you will have to specify the full path that you want to use.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[dalex]

Thanks...I learned the way you suggested and it was easy...it worked. Now I have a copy of the file...excellant thank you!

To complete this, could you help me with code that will execute the macro when the file is opened?

Dalex

 

[TimRHSD]

Dalex,

In the vba window double click on ThisWorkbook to open that window. In the General tab choose 'Workbook' and it should default to Sub Workbook_Open(). Add the following to the code "Application.Run "Your Macro" or put the code you created here.

HTH,

Tim

Tim Rutherford

http://www.Manna-Consulting.com

 

[dalex]

Right now the code sits in 'This Workbook' with the 'General' option selected.

Should I copy this into a module, and then put the Application.Run command within the 'General' page?

Maybe I should read up on the difference between Workbook and General...

 

[Loomah]

dalex
You need to follow Tim's instructions a little closer! You need to select Workbook not General from the lefthand dropdown.

This will give you access to various "Events" that are available to the "Workbook Object". One of thes is the "Open" event. This means that the code will run when the workbook is opened.

There is another dropdown on the right which gives you the list of available events. To put a skeleton procedure into your project you can simply click/choose one of these events. With larger more complex projects it is also a good way of moving to a specific routine.

As for the difference between General & Workbook, it's one of those things I can't explain properly! In essence "Workbook" refers to the Workbook Object. It is a module attached to the Workbook specifically. It is possible to have code in there that doesn't refer to the workbook but I wouldn't recommend it. General sort of acts like a normal module, kind of (see what I mean about not being able to explain properly?). I've never really used it!

As for where to put your code. I wouldn't bother with the Application.Run idea and just copy the code you have recorded/edited into the Workbook_Open event.

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[dalex]

Thank you for sharing your knowledge!

dalex