Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 12th, 2005, 03:08 AM
Registered User
Join Date: Feb 2005
Location: , , .
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

Reply With Quote
  #2 (permalink)  
Old February 12th, 2005, 07:09 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , Denmark.
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
Reply With Quote
  #3 (permalink)  
Old February 12th, 2005, 11:38 AM
Friend of Wrox
Join Date: Oct 2003
Location: , , Finland.
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)


Reply With Quote
  #4 (permalink)  
Old February 15th, 2005, 12:49 AM
Registered User
Join Date: Feb 2005
Location: , , .
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

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 07:13 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.