Add variable to excel header

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
damoncf1234Asked:
Who is Participating?
 
David LeeConnect With a Mentor Commented:
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
 
David LeeCommented:
Hello again, damoncf1234.

Yes, this is simple to do.  Does the script need to prompt the user for the date?
0
 
damoncf1234Author Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
damoncf1234Author Commented:
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
 
damoncf1234Author Commented:
Very quick response (as usual).  Great person to have on experts-exchange.  Thanks again.  
0
 
David LeeCommented:
Thanks and you're welcome!
0
 
damoncf1234Author Commented:
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
 
David LeeCommented:
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
 
damoncf1234Author Commented:
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
 
David LeeCommented:
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
 
damoncf1234Author Commented:
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
 
damoncf1234Author Commented:
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
 
David LeeCommented:
I don't know.  I'll have to play around with it and see what I can do.
0
 
David LeeCommented:
Ok, change this line of code from

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

to

    excSht.PageSetup.LeftHeader = FormatDateHeader(datDate)
0
 
damoncf1234Author Commented:
Great.  Thanks.
0
 
David LeeCommented:
You're welcome.  Sorry for the confusion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.