Filename +datestamp.xls

manyo23

New Member
Joined
Sep 13, 2006
Messages
11
Hi Guys,

I want to save a file called workbook.xls, but instead of just saving it I want to archive it as well with a datestamp at the end of it. So today I want to save it as workbook130906.xls, tommorow workbook140906.xls and so on. How can this be done in VBA?

Manish
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try:
Code:
ActiveWorkbook.SaveAs "C:\Full\Path\To\Destination\Folder\workbook" & Format(Date, "ddmmyy") & ".xls"
 
Upvote 0
New topic

Lets say I had a file workbook.xls. Within this workbook I am constantly archiving, so the last sheet is 130906 one before 120906 etc...now lets say i want to make a comparision of one day to the next. so i want to compare todays figure in 130906 of 5 to 120906 of 4. For this I need to go into sheet labelled 120906 and pluck out this information. However I need to check the previous days (this means 3 days over the weekend) and I need to be able to get teh data dynamically. how would I go about doing this?

manish
 
Upvote 0
To get data from sheets dynamically use the INDIRECT function. And to calculate the previous working day before a date look at the WORKDAY function ( need to instal the Analysis Toolpak add-in for that ).
 
Upvote 0
Try:
Code:
ActiveWorkbook.SaveAs "C:\Full\Path\To\Destination\Folder\workbook" & Format(Date, "ddmmyy") & ".xls"


Hi.. Looking over shoulders here..

I'm trying to do this, but where should I put it?

I tried this:

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    Sheets("Medlemsliste").Range("c1") = " " & Date & " klokken " & Time
    ActiveWorkbook.SaveAs "C:\Full\Path\To\Destination\Folder\workbook" & Format(Date, "ddmmyy") & ".xls"
End Sub

.... but crash on save..

Would anyone help me?
 
Upvote 0
DKcrm

Have you tried changing the path to the folder you want to save to?
 
Upvote 0
Yes, I have. I'm sorry it didn't look like it in the post. :oops:

In the code I have written the full path.

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    Sheets("Medlemsliste").Range("c1") = " " & Date & " klokken " & Time
    ActiveWorkbook.SaveAs "C:\Documents and Settings\ejer\skrivebord\ICD\Medlemsliste" & Format(Date, "ddmmyy") & ".xls"
End Sub


The code works (I can see I have a file called 'Medlemsliste180906.xls'), but it causes a crash on save.

What's wrong? :rolleyes: [/code]
 
Upvote 0

Forum statistics

Threads
1,218,625
Messages
6,143,576
Members
450,493
Latest member
Woejeber

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top