Solved

Add variable to excel header

Posted on 2010-09-07
16
1,038 Views
Last Modified: 2013-11-27
This question is related to the question that BlueDevilFan just answered yesterday (see "related solutions")...  Is there a way to add a variable to an excel spreadsheet header?  

For example, in the vbscript that bluedevilfan created yesterday, a spreadsheet is opened, and some information from outlook is populated in the spreadsheet (based on a date that the user provides).  Is there a way to display the day of the week, and date in the left side of the header?  Of course the day of the week/date would not always be "today's date" -- it would be the date that the user provides for the vbscript.  

For example, if the user provides 9/7/10 to the existing vbscript, is there a way to populate the excel header with:  

Tuesday
07SEP2010

-Thanks
0
Comment
Question by:damoncf1234
  • 8
  • 8
16 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 33619444
Hello again, damoncf1234.

Yes, this is simple to do.  Does the script need to prompt the user for the date?
0
 

Author Comment

by:damoncf1234
ID: 33619720
Hi BlueDevilFan,

I'm trying to incorporate this into the script you developed yesterday (so it can use the same date the user provides for the rest of the spreadsheet) -- I think it was "datDate"?...

Thanks
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 33623312
Add this function to the code from the other question, then insert this code at line 23

    excSht.Cells(RowNumber,ColNumber) = FormatDateHeader(datDate)

where RowNumbeand ColNumber are the numbers of the row and column where you want the header to appear.
Function FormatDateHeader(datValue)
    FormatDateHeader = WeekdayName(Weekday(datValue)) & vbLf & StrZero(Day(datValue), 2) & UCase(MonthName(Month(datValue), True)) & Year(datValue)
End Function

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:damoncf1234
ID: 33641942
Thanks.  I don't have access to the network that we're running that script on until tomorrow (Friday), but I'll award the points to you, and reply back tomorrow if there are any issues.  

Great job once again.  
0
 

Author Closing Comment

by:damoncf1234
ID: 33641949
Very quick response (as usual).  Great person to have on experts-exchange.  Thanks again.  
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33641966
Thanks and you're welcome!
0
 

Author Comment

by:damoncf1234
ID: 33645168
BlueDevilFan,

I copied the function and the line of code into the existing vbscript, and it doesn't seem to put anything into the actual header of the spreadsheet.  

Looking back at this line:
excSht.Cells(RowNumber,ColNumber) = FormatDateHeader(datDate)

would this populate spreadsheet header, or a cell in the spreadsheet?  

Thanks.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33645214
Did you change RowNumber and ColNumber to the actual numbers?  For example,

excSht.Cells(1,3) = FormatDateHeader(datDate)

would put the date in cell C1.
0
 

Author Comment

by:damoncf1234
ID: 33645312
Yes, I tried:

excSht.Cells(1,1) = FormatDateHeader(datDate)

I'm trying to populate the header (not the first row of the spreadsheet).  

Thanks
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33645328
I guess I don't know what you mean by header.  Keep in mind that I'm not an Excel guru.  I consider the "header" to be the first row (i.e. the column heads).  
0
 

Author Comment

by:damoncf1234
ID: 33645418
Just like MS Word, Excel has "headers and footers" -- and there are "pre-defined" variables that you can use (like the current date/time, page numbers, file name, etc...).  See the screen shot (attached) from Excel 2007.  

I did a search, and found this from another site -- this person was trying to do the same thing, except he was working with a macro:

http://www.ozgrid.com/forum/showthread.php?t=82534

Thanks again.  
Excel-header-footer-options.doc
0
 

Author Comment

by:damoncf1234
ID: 33648409
Do you know if there's a way to add a (non-standard) variable to the "page header" (like in the comment above) using the vbscript?  
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33648435
I don't know.  I'll have to play around with it and see what I can do.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33665057
Ok, change this line of code from

    excSht.Cells(RowNumber,ColNumber) = FormatDateHeader(datDate)

to

    excSht.PageSetup.LeftHeader = FormatDateHeader(datDate)
0
 

Author Comment

by:damoncf1234
ID: 33681295
Great.  Thanks.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33685735
You're welcome.  Sorry for the confusion.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question