Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 12th, 2005, 03:08 AM
Registered User
Join Date: Feb 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Printing excel sheet

Hi all,
I have a Visual Basic application to print an excel sheet programmatically.
This is the code I have used.

    Dim x1 As New Excel.Application
    x1.Workbooks.Open (fileToPrint)
    x1.Workbooks(fileToPrint).Close (False)

But I want to add print page set up before printing.I have to add a row at the top of every page which is being printed. And page set up like orientation, number of copies are to be added as well.
Anybody can give me some sample code to achieve this.
Any help or guidance will be appreciated.
Thank you

Old February 12th, 2005, 07:09 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts

Try some of this code - got most of it from the net so it's not tested:


Public xlApp As Object
Public xlWbk As Object

Private Sub Command1_Click()

    On Error Resume Next
    Set xlApp = CreateObject("Excel.Application")

    If xlApp Is Nothing Then
        MsgBox "Unable to start Excel."
        Exit Sub
    End If
    On Error GoTo 0

    Set xlWbk = xlApp.Workbooks.Open("F:\book.xls")

    With xlWbk.Sheets(1)
        .Range("A1") = "This is an opened instance of Book.xls."
    End With

' Run macro placed in Excel
    xlApp.Run "myMacro"

' xlApp.Visible = True 'If you do not want it to show then remove this line
    xlApp.Quit 'will close the newly created XL instance

    Set xlWbk = Nothing
    Set xlApp = Nothing

End Sub

To test it try first saving a macro named myMacro in the workbook, e.g.:

MsgBox "Working"

With regards to printdesign I'd recommend you use the macroeditor to get the necessary code. When satisfied place the code in the macro you call from VB6.

Ref.: http://www.mrexcel.com/archive2/60500/70091.htm
Old February 12th, 2005, 11:38 AM
Friend of Wrox
Join Date: Oct 2003
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts

Hi Siddiq

You can try this one too

Dim x1 As New Excel.Application
    x1.Workbooks.Open (FileToPrint)
    With x1.ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .Orientation = xlLandscape
    End With
    x1.Workbooks.Open(FileToPrint).PrintOut Copies:=2
    x1.Workbooks(FileToPrint).Close (False)


Old February 15th, 2005, 12:49 AM
Registered User
Join Date: Feb 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Thank you vemaju and birger.
My problem is solved. I used your code vamaju.
Thanks a lot

Similar Threads
Thread Thread Starter Forum Replies Last Post
EXCEL SHEET SECURITY kotaiah Excel VBA 1 February 21st, 2013 08:45 AM
ListBox in Excel Sheet anup.bihani Excel VBA 1 November 16th, 2006 10:29 AM
Non Editable Excel Sheet kotaiah Excel VBA 0 September 13th, 2006 03:46 AM
write multi-sheet Excel w/o Excel.Application manmoth Classic ASP Components 2 November 22nd, 2005 10:56 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.