Reference Worksheet Header in VBA Code 1

[Terpsfan]

I am using a function to export data from an Access query in the form of an ADO recordset to an Excel spreadsheet. That is working fine, but I can't see anything in the Excel object model where I can reference the worksheet header in vba code. After the export is completed I'm having to go into the spreadsheet and modify the header text. I want to automate the process where I don't have to do this - it's rather clunky for the user. Thanks

 

[combo]

For the 1st sheet:
With Sheets(1).Pagesetup
.LeftHeader = "left"
.CenterHeader = "center"
.RightHeader = "right"
End With

You can also use codes for date, time, sheet name etc.

combo

 

[dougcranston]

Omega36,

This is do'able. Snippet follows that I have in one of my VBScripts that reads data from Access and then creates a series of worksheets and then emails it to my users.

oXLWs.PageSetup.PrintArea = ""
With oXLWs.PageSetup
.LeftHeader = ""
.CenterHeader = "Reprint Requests: "
.RightHeader = ""
.LeftFooter = "&F"
.CenterFooter = "Page - &P"
.RightFooter = "&D &T"
.LeftMargin = oXLApp.InchesToPoints(0.5)
.RightMargin = oXLApp.InchesToPoints(0.5)
.TopMargin = oXLApp.InchesToPoints(0.8)
.BottomMargin = oXLApp.InchesToPoints(0.8)
.HeaderMargin = oXLApp.InchesToPoints(0.5)
.FooterMargin = oXLApp.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = -4142
.Orientation = 2 ' landscape
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Draft = False
.PaperSize = 1 ' xlPaperLetter
.FirstPageNumber = 1 ' xlAutomatic
.Order = 1 ' xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With

Hope this helps.
DougCranston

 

[gilbil]

hi all,
I'm trying to do the same thing however i was wondering if you guys can help me with howto move to the next line in a RightHeader section for example - i need to print:
"xxxxx
yyyyyy"
sometimes if the header is too high it's overwrites the first row of the worksheet - do i have to set the margines or what?
and another thing DougCranston mentioned that he emailed the sheet from Access - can I have the code line for it ?
thanks
Gil

 

[combo]

You can create multi-line string:
[tt]sHeader = "Line 1" & vbLf & "Line 2"
Worksheets(1).PageSetup.RightHeader = sHeader[/tt]

combo

 

[gilbil]

Great combo, thanks,
do you happend to know about my second question - the header overwrites the first row
thanks
gil

 

[combo]

To set the top of page:
[tt]With Worksheets(1).PageSetup
.HeaderMargin = Application.InchesToPoints(1)
.TopMargin = Application.InchesToPoints(1.7)
End With[/tt]
where HeaderMargin sets distance between top of page and header, TopMargin is the above plus header's height (i.e. up to printed area). So, the size of header is TopMargin-HeaderMargin and is to be adjusted depending on the font size and number of lines.

combo

 

[dougcranston]

Providing two versions since you did not indicate what version of Win you have, NT vs Win2k. I use CDONTS on my NT box and CDOSYS on my 2k systems.

HTH
DougCranston

'Sending a text email with an attached file
' If used on WIN2 Server/WS remove the double ''
' and then comment out the CDODNTS
''Set objMessage = CreateObject("CDO.Message&quot

'Sending a text email using a remote server

''Set objMessage = CreateObject("CDO.Message&quot
''objMessage.Subject = "Callout.xls Update"
''objMessage.Sender = "me@myemail.com"
''objMessage.To = "enduser@mymail.xxxx.com"
''objMessage.Cc = "me@myemail.com"
''objMessage.AddAttachment "c:\cmdfiles\tempfile\callout.xls"
''objMessage.TextBody = "The attached file is the latest listing."

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.

''objMessage.Configuration.Fields.Item _
''("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2

'Name or IP of Remote SMTP Server
''objMessage.Configuration.Fields.Item _
''("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= "smtp.tel.xxxx.com"

'Server port (typically 25)
''objMessage.Configuration.Fields.Item _
''("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25

''objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

''objMessage.Send


' CDONTS email for NT
Dim MyBody
Dim MyCDONTSMail

set MyCDONTSMail = CreateObject("CDONTS.NewMail&quot

MyCDONTSMail.Subject = "Callout.xls Update"
MyCDONTSMail.From = "me@myemail.com"
MyCDONTSMail.To = "enduser@myemail.com"
MyCDONTSMail.Cc = "me@myemail.com"
MyCDONTSMail.AttachFile("c:\cmdfiles\tempfile\callout.xls&quot
MyCDONTSMail.Body = "The attached file is the latest listing." & VbCrLf
MyCDONTSMail.Send
set MyCDONTSMail=nothing

 

[gilbil]

thanks combo for your reply,
and you DougCranston for your detailed answer however it didn't do nothing here, let me tell you how it goes:
I have win2k and outlook on the computers that suppose to send the email.
do i have to set the smtp configuration or is there a way to JUST send it like in the DoCmd.SendObject ?
here is the code that did nothing

Dim objMessage As Object
Set objMessage = CreateObject("CDO.Message&quot
objMessage.Subject = "Callout.xls Update"
objMessage.Sender = "me@myemail.com"
objMessage.To = "enduser@mymail.xxxx.com"
objMessage.AddAttachment "c:\aml\MmTest.xls"
objMessage.TextBody = "The attached file is the latest listing."
objMessage.Send

thanks
gil

 

[gilbil]

is there a difference between 'ms outlook' and
'outlook express' ?

 

[dougcranston]

gilbil,

If your running Win2k, you have to have an SMTP gateway to hit to use CDONTS (NT) or CDOSYS (Win2k or >).

For Win2k and using CDOSYS your script would be:

'Sending a text email with an attached file
' If used on WIN2 Server/WS remove the double ''
' and then comment out the CDODNTS
Set objMessage = CreateObject("CDO.Message&quot

'Sending a text email using a remote server

Set objMessage = CreateObject("CDO.Message&quot
objMessage.Subject = "Callout.xls Update"
objMessage.Sender = "me@myemail.com"
objMessage.To = "enduser@mymail.com"
objMessage.Cc = "me@myemail.com"
objMessage.AddAttachment "c:\cmdfiles\tempfile\callout.xls"
objMessage.TextBody = "The attached file is the latest listing."

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.

objMessage.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/sendusing";)

= 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserver";)

= "smtp.YOURSMTP.MAIL.GATEWAY.ADDRESS.GOES.HERE"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserverport";)

= 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send


Your sample snippet was incomplete.. Your dropped a number of required fields.

As for using DOCMD.Sendmail never used it and can't help you on that.

DougCranston