Solved

Formatting ExcelWorksheet using Visual Basic 6

Posted on 2006-07-13
5
548 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 127
Advice in Xamarin 21 120
Formula problem with Excel attachment 6 44
can this macro be converted to sub procedure 4 33
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

734 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