I am using a macro template that was created before I started my temp assignment at my current place of employment. The macro template (.mht) has a year hard coded in it. Is there a way to put some type of variable in it that will update the current year each new year?

In a FEX that does not use the macro template, I have the following coded so that the year is updated automatically each year. I need to do something similar in the macro template (.mht) file. Thanks.

 
-INCLUDE footing
-SET &CRYEAR=EDIT(&YYMD,'9999$$$$');
"@Copyright &CRYEAR"

 

This message has been edited. Last edited by: Michele Brooks,

Grab the current date (for year) in VBA and locate it where you need it?

currentYear = DatePart("yyyy", Now())

T

Note sure how I would fit the currentYear in. This is the code from the macro template

 

 {mso-footer-data:"&L&6&F&C&6Confidential\, \[Un\]published Property of ABC=
Company\, Inc\000ADo Not Duplicate or Distribute\.  Use and Distribut=
ion Limited Solely to Authorized Personnel\.\000A\@Copyright 2013 ABC=
Company\, Inc\&R&6Page &P of &N";
 

I changed the 2013 to 2014 in the .mht just to test the change. When I executed the FEX that uses this macro template, the change that I made did not show up. The copyright statement year still shows 2013.

quote:

I changed the 2013 to 2014 in the .mht just to test the change.

What is the version of MS Office you're using?
You still have XP machines to modify your mht files,right?

MS Office 2010

I have two computers. One I use at the office and one I use at home. Today, I am using the computer at home and the Windows Operating System is Windows 7 Enterprise.

Sorry, I have been unable to reply.

Anyway, the code that you show indicates to me that this is part of the "PageSetup" within Excel so I would suggest that you need to write VBA to insert the text that you require into "ActiveSheet.PageSetup".

Search MS Excel forums for examples.

Incidentally, the code that you show is not macro (or VBA) code, you will locate it by going into Page Setup in MS Excel and looking at the Footer.

T

quote:

ActiveSheet.PageSetup

I am not adding new text code to the .mht file, but I am modifying existing text.

I also realize that the copyright statement appears in the footer of page setup. That's where I looked for the changes that I made, but the changes were not reflected in the footer. Thanks for your feedback.

quote:

I changed the 2013 to 2014 in the .mht just to test the change. When I executed the FEX that uses this macro template, the change that I made did not show up. The copyright statement year still shows 2013.

Seems like still your server has old mht file.

If you don't mind, please tell me how did you upload your .mht after your macro changes, back to the server without having XP machines with 2003 MS Office?

As you're in 7.6.11 with Win 7(MS 2010),you can't upload mht files directly.

I uploaded the .mht file using Win 7 (MS 2010) which I had a feeling wouldn't work. Are these the correct procedures for changing an existing macro template that uses one sheet? Thanks.

1.Using a computer that has Windows XP, open up a New Excel Workbook in Excel 2003
2.Make sure that the Workbook has 2 worksheets and that each sheet contains at least a single space in at least one cell
3.Create a New Macro
4.Copy and Paste the revised contents of the .mht file that resides on the WF reporting server into the VBA Macro Editor in Excel
5.Close the Macro Editor and go back to the Workbook page
6.Save As a Single File Web Page (.mht)
7.Close the Excel file
8.Upload the .mht file to the WF Reporting Server (I usually just drag and drop the .mht file from my Desktop)

quote:

I uploaded the .mht file using Win 7 (MS 2010) which I had a feeling wouldn't work.

You can't upload the .mht files from Win 7 (MS 2010) as you're in 7.6.11

It is not a good idea to create new macros if chnages requires with existing reports.
-------------
Win XP requires only for moving your mht files from your desktop to server.

You can modify your macro codes in MS 2010 itself.

1.Run your existing report, save it in your desktop.
2.Open it, press Alt + F11, do macro changes. You can use F8 for debugging/ensure macro code is working fine.

On the other hand, in XP machine, always have report(without/removed stylings,datas from sheets) saved in .xls format. As you said,each sheet contains at least a single space/value in at least one cell(usually A1 cell).Copy your modified macro code from Win 7 and paste it here & save as Single File Web Page (.mht). Drag & drop .mht to the server.

Filename shouldn't be mixed case while saving .mht

This message has been edited. Last edited by: Rifaz,

Thank you for the resolution to this post. I thought that I needed to have Windows XP and Microsoft 2003, now I know that I only need Win XP and I can use the later version of Excel.

Kudos