Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

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
0
damoncf1234
Asked:
damoncf1234
  • 8
  • 8
1 Solution
 
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
 
David LeeCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now