Filename +datestamp.xls

[manyo23]

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

 

[Von Pookie]

Try:

ActiveWorkbook.SaveAs "C:\Full\Path\To\Destination\Folder\workbook" & Format(Date, "ddmmyy") & ".xls"

 

[manyo23]

Hey,

worked perfectly - thanks!

 

[manyo23]

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]

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]

Try:

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:

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]

DKcrm

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

 

[DKcrm]

Yes, I have. I'm sorry it didn't look like it in the post.

In the code I have written the full path.

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? [/code]