Print Excel Worksheets in VB6

Hello everybody.
I need to be able to print an excel worksheet in my application written using VB6.
In a thread of this forum i found a chunk o code that lets me do that, which is:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Const SW_SHOWNORMAL = 1


Private Sub Command1_Click()
ShellExecute Me.hwnd, "Print", "c:/test.xls", vbNullString, "C:\", SW_SHOWNORMAL
End Sub



But:
- I need to put an header and footer on the page before and after the excel worksheet
- I need to be able to let the user choose which worksheet he wants to print (choosing from several available in the excel file)

Can anybody help me? :rolleyes:

Thanks in advance :D :D

NeT

Try opening excel using the Excel Object Library.
Then you could use a sub like this to choose which sheet to print and to create headers and footers

Sub PrintSheet(sh As Worksheet, strFooter As String, strHeader As String)
sh.PageSetup.CenterFooter = strFooter
sh.PageSetup.CenterHeader = strHeader
sh.PrintOut
End Sub

Thanks Wamphyri for your help, but, since I have never used the Excel Object Library before I would like some tips on how to proceed.

1st I have to add referece in my project for the Excel object library(nothing difficult about this one :) )

2nd how do I specify the file where the program is supposed to go and get the worksheet I need to print?

3rd How do I specify the worksheet I want to be printed? In the sub you've written in your post there is a parameter named "sh As Worksheet" to be passed to the sub. But how do I point to it? With the worksheet's name? or the ordinal number?

Thanks again :rolleyes:

NeT :D

Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSH As Excel.Worksheet

'open excel application
Set xlApp = New Excel.Application
'Open excel workbook
Set xlWB = xlApp.Workbooks.Open(FileName:="C:\YourFile.xls")
'There are two ways to access specific worksheets
'By index number (the first worksheet in this case)
Set xlSH = xlWB.Worksheets(1)
'or by the Sheet's Name
Set xlSH = xlWB.Worksheets("TestSheet")

PrintSheet xlSH, "MyFoot", "MyHead"

'Close workbook (optional)
xlWB.Close
'Quit excel (automatically closes all workbooks)
xlApp.Quit
'Clean up memory (you must do this)
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

Sub PrintSheet(sh As Worksheet, strFooter As String, strHeader As String)
sh.PageSetup.CenterFooter = strFooter
sh.PageSetup.CenterHeader = strHeader
sh.PrintOut
End Sub