Solved

Add variable to excel header

Posted on 2010-09-07
16
1,027 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:damoncf1234
Comment Utility
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
Comment Utility
Very quick response (as usual).  Great person to have on experts-exchange.  Thanks again.  
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Thanks and you're welcome!
0
 

Author Comment

by:damoncf1234
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:damoncf1234
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, change this line of code from

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

to

    excSht.PageSetup.LeftHeader = FormatDateHeader(datDate)
0
 

Author Comment

by:damoncf1234
Comment Utility
Great.  Thanks.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.  Sorry for the confusion.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Find out how to use dynamic social media in email signatures with this top 10 DOs & DON’Ts.
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now