asked on

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

David Lee

Hello again, damoncf1234.

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

damoncf1234

ASKER

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

ASKER CERTIFIED SOLUTION

David Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

damoncf1234

ASKER

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.

damoncf1234

ASKER

Very quick response (as usual). Great person to have on experts-exchange. Thanks again.

David Lee

Thanks and you're welcome!

damoncf1234

ASKER

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.

David Lee

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.

damoncf1234

ASKER

Yes, I tried:

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

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

Thanks

David Lee

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).

damoncf1234

ASKER

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

damoncf1234

ASKER

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?

David Lee

I don't know. I'll have to play around with it and see what I can do.

David Lee

Ok, change this line of code from

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

to

excSht.PageSetup.LeftHeader = FormatDateHeader(datDate)

damoncf1234

ASKER

Great. Thanks.

David Lee

You're welcome. Sorry for the confusion.