Solved

Formatting ExcelWorksheet using Visual Basic 6

Posted on 2006-07-13
5
540 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
5 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
Comment Utility
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
Comment Utility
0
 
LVL 8

Expert Comment

by:kaliyugkaarjun
Comment Utility
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now