Solved

Formatting ExcelWorksheet using Visual Basic 6

Posted on 2006-07-13
5
549 Views
Last Modified: 2013-11-25
Dear Expert,

I have developed a Visual Basic 6.0 Program to export the required data from an access database to MS Excel. I have many columns (Fields) which span on multiple pages in excel. Everything is working fine.

But i want to know the code for -
1. How to change the Pageorientation to landscape?
2. How to set the Printing Margin for the worksheet?
3. How to insert header or heading rows to the worksheet?

Please solve my problems

Thanks
Sher Singh Rawat

0
Comment
Question by:shersinghrawat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
ID: 17098669
So I take it you are using Excel automation and have already got code in place

this is what I suggest u do, go to Excel, then record a new macro, then do your steps (one at a time)
u should end up with 3 macros
Now apply this to your code

objWB may be a workbook

e.g.

    With objWB.ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    objWB.ActiveSheet.PageSetup.PrintArea = ""
    With objWB.ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With


if u dont have the reference, u need to constant values for things like  xlPrintErrorsDisplayed


0
 
LVL 8

Expert Comment

by:kaliyugkaarjun
ID: 17098757
0
 
LVL 8

Expert Comment

by:kaliyugkaarjun
ID: 17098825
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
ID: 17098856
Hi shersinghrawat,

rockiroads's code example answers the first two.

> 3. How to insert header or heading rows to the worksheet?

With objWB.ActiveSheet
    ' insert three rows at top
    .[1:3].Insert

    ' put headers in Row 1
    .[a1:d1].Value = Array("w", "x", "y", "z")
End With

Regards,

Patrick
0
 

Author Comment

by:shersinghrawat
ID: 17105792
Dear expert

I also have another query related to the same. I want subtotals on more than one column and the number of columns depend on the recordset from some table i.e.they are not fixed.

Consider the following :
ExlObj.ActiveCell.Worksheet.Cells(4, 1).Subtotal 4, xlSum, (6,7,8), 0, 0, xlSummaryBelow.

In this example i have taken three column numbers 6,7 & 8 (or F, G & H) for subtotal. But as earlier said, the number of columns are not fixed then how can i use some variable in place of  (6,7,8).  I created a string variable and assigned the required input to it but it does not work.

for example:

dim abc as string
'..
'remaining code goes here
'..
' and the variable abc is assigned the final string value

abc="6, 7, 8, 9, 10, 11, 12"
'---then i used it in subtotal function
ExlObj.ActiveCell.Worksheet.Cells(4, 1).Subtotal 4, xlSum, (abc), 0, 0, xlSummaryBelow.

----- But it does not work and gives error...

Please give me solution.

Thanks
Sher Singh Rawat
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses
Course of the Month11 days, 12 hours left to enroll

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question