Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6
Print Excel Worksheets in VB6 Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Go Back  Xtreme Visual Basic Talk > > > > Print Excel Worksheets in VB6


Reply
 
Thread Tools Display Modes
  #1  
Old 03-27-2003, 11:15 AM
Netrunner Netrunner is offline
Freshman
 
Join Date: Nov 2002
Posts: 32
Default 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?

Thanks in advance

NeT
Reply With Quote
  #2  
Old 03-27-2003, 01:27 PM
Wamphyri's Avatar
WamphyriPrint Excel Worksheets in VB6 Wamphyri is offline
Variable not defined

Retired Moderator
* Guru *
 
Join Date: Apr 2002
Location: Ottawa, Ontario
Posts: 4,793
Default

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
Code:
Sub PrintSheet(sh As Worksheet, strFooter As String, strHeader As String) sh.PageSetup.CenterFooter = strFooter sh.PageSetup.CenterHeader = strHeader sh.PrintOut End Sub
__________________
-Carl
Reply With Quote
  #3  
Old 03-28-2003, 07:44 AM
Netrunner Netrunner is offline
Freshman
 
Join Date: Nov 2002
Posts: 32
Question

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

NeT
Reply With Quote
  #4  
Old 03-28-2003, 09:10 AM
Wamphyri's Avatar
WamphyriPrint Excel Worksheets in VB6 Wamphyri is offline
Variable not defined

Retired Moderator
* Guru *
 
Join Date: Apr 2002
Location: Ottawa, Ontario
Posts: 4,793
Default

Code:
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
__________________
-Carl
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Listviews to excel worksheets Tolstoy Word, PowerPoint, Outlook, and Other Office Products 0 02-28-2003 03:01 PM
why does my code no longer work dapul General 4 11-07-2002 07:22 PM
Excel Worksheets UncleSam Word, PowerPoint, Outlook, and Other Office Products 2 08-09-2002 02:53 PM
How to set Excel Print Preview Margins MarkNewVBGuy Word, PowerPoint, Outlook, and Other Office Products 1 12-14-2001 01:57 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6 Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6 Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
 
Print Excel Worksheets in VB6
Print Excel Worksheets in VB6
 
-->