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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Try:
Code:
ActiveWorkbook.SaveAs "C:\Full\Path\To\Destination\Folder\workbook" & Format(Date, "ddmmyy") & ".xls"
 

manyo23

New Member
Joined
Sep 13, 2006
Messages
11
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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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 ).
 

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
DKcrm

Have you tried changing the path to the folder you want to save to?
 

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
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]
 

Forum statistics

Threads
1,136,261
Messages
5,674,702
Members
419,520
Latest member
Jennifer4Dillon

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
Top