?
Solved

Add variable to excel header

Posted on 2010-09-07
16
Medium Priority
?
1,058 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
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!

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

650 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