Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Formatting ExcelWorksheet using Visual Basic 6

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

885 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