Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Formatting ExcelWorksheet using Visual Basic 6

Posted on 2006-07-13
5
Medium Priority
?
552 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 500 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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

718 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