How to save excel file with current date using macro

[#1]

Hi all,

I have a sheet with 2 objects.

These objects are sent to an existing Excel document using macro. (Found on this community)

Then is saves the file (overwrite)

I need to save the file with a sort of timestamp in it's document name.

Who can help me?

I need to save the file like this:

   ' Save and quit  
   objExcelSheet.SaveAs "C:\documenten\test\test20140612.xlsx"
   objExcelApp.Application.Quit  
   SET objExcelSheet = NOTHING        
   SET objExcelApp = NOTHING  

But then with a variable date / date function.

Thanks,

Kevin

[#2 · Accepted solution]

1. 
   
2. 
   
3. ' Save and quit   
4.    vX=Year(now())&month(now())&day(now())
5.    objExcelSheet.SaveAs "C:\documenten\test\test"&vX&".xlsx" 
6.    objExcelApp.Application.Quit   
7.    SET objExcelSheet = NOTHING         
8.    SET objExcelApp = NOTHING
9. 
   
10. Hope this helps

[#3]

Thank you,

This was the right solution..

Now I have 1 more question:

I want Qlikview to open the saved file

This is an action on a button that calls the Macro and opens the file.

I need the same date in this screen at "bestandsnaam": (Sorry for the dutch system language)

Someone who knows how to solve this?

Thanks,

Kevin

[#4]

place this expression in place of filename/bestandsnaam.

='D:\Test\Test'& year(today())&num(Month(Today()),00)&day(today())&'.xlsx'

or

='D:\Test\Test'& year(today())&Repeat('0', 2 - Len(num(Month(Today()))) )& num(Month(Today()))&day(today())&'.xlsx'

[#5]

Thanks!

This works