Setting Print options from a VB6 program
 

I am not sure if this is the correct place to post this or not but here goes.

I have been doing some programming to take information from an Access
database, massaging it and putting it in an Excel spreadsheet. The
programming overwrites the file each time.

I have done some formatting eg. Bolding, Autofit, horizontal alignment, and
in some projects I have put in some Sum formulas.

My client is now asking for specific printing formatting options that are
normally setup after the information gets to the spreadsheet. The list is
below but I have no idea where to even begin looking for information on
these. Basically once the spreadsheet is setup and opened, they want the
settings below predone. Since I am overwriting the file each time these
formatting options are also overwritten.

I have found a couple of references to some printing options but they don't
all seem to work as expected. For example, I am trying to set the gridlines
to show with

ExcelSheet.PageSetup.PrintGridlines = True

This seems to work ok but when I try to set the pages to 1 page wide by
whatever tall I don't get what I want. It continues to expand past the page
but does not show the remaining columns that do not show on the first page
and I get many copyies of the same pages.

ExcelSheet.PageSetup.FitToPagesWide = 1

I am trying to set the first row to print on every page but it does not seem
to be working either. I would prefer to just say Row A and all of them show:

ExcelSheet.PageSetup.PrintTitleColumns = "$A:$CZ"

1. Landscape orientation
2. Legal size paper
3. 0.2" margins, except bottom edge @ 0.75"
4. Footer: Left - Date & time printed; Centre - Name of Show; Right -Page#
of #pages
5. Fit to print 1 page wide by however many pages long
6. Gridlines
7. Repeat header row on every page

One to add is how to set the Freeze Panes? I usually can tell in the code
what the cell would be if I were to do it manually but am not sure about what
function to use.

TIA rasinc

Setting Print options from a VB6 program
 

Ras,

I was searching for similar but thru VBA ...
Following example was in the Help...DON'T IGNORE ".zoom = False" :
Hope it helps ...

This example causes Microsoft Excel to print Sheet1 exactly one page
wide and tall.

With Worksheets("Sheet1").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With

JimP

rasinc wrote:
I am not sure if this is the correct place to post this or not but here goes.

I have been doing some programming to take information from an Access
database, massaging it and putting it in an Excel spreadsheet. The
programming overwrites the file each time.

I have done some formatting eg. Bolding, Autofit, horizontal alignment, and
in some projects I have put in some Sum formulas.

My client is now asking for specific printing formatting options that are
normally setup after the information gets to the spreadsheet. The list is
below but I have no idea where to even begin looking for information on
these. Basically once the spreadsheet is setup and opened, they want the
settings below predone. Since I am overwriting the file each time these
formatting options are also overwritten.

I have found a couple of references to some printing options but they don't
all seem to work as expected. For example, I am trying to set the gridlines
to show with

ExcelSheet.PageSetup.PrintGridlines = True

This seems to work ok but when I try to set the pages to 1 page wide by
whatever tall I don't get what I want. It continues to expand past the page
but does not show the remaining columns that do not show on the first page
and I get many copyies of the same pages.

ExcelSheet.PageSetup.FitToPagesWide = 1

I am trying to set the first row to print on every page but it does not seem
to be working either. I would prefer to just say Row A and all of them show:

ExcelSheet.PageSetup.PrintTitleColumns = "$A:$CZ"

1. Landscape orientation
2. Legal size paper
3. 0.2" margins, except bottom edge @ 0.75"
4. Footer: Left - Date & time printed; Centre - Name of Show; Right -Page#
of #pages
5. Fit to print 1 page wide by however many pages long
6. Gridlines
7. Repeat header row on every page

One to add is how to set the Freeze Panes? I usually can tell in the code
what the cell would be if I were to do it manually but am not sure about what
function to use.

TIA rasinc

Setting Print options from a VB6 program
 

Thanks Jim,

I found that was the problem last week. I had done everything exceopt the
..zoom statement. Appreciate the response.

"JimP" wrote:

Ras,

I was searching for similar but thru VBA ...
Following example was in the Help...DON'T IGNORE ".zoom = False" :
Hope it helps ...

This example causes Microsoft Excel to print Sheet1 exactly one page
wide and tall.

With Worksheets("Sheet1").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With

JimP

rasinc wrote:
I am not sure if this is the correct place to post this or not but here goes.

I have been doing some programming to take information from an Access
database, massaging it and putting it in an Excel spreadsheet. The
programming overwrites the file each time.

I have done some formatting eg. Bolding, Autofit, horizontal alignment, and
in some projects I have put in some Sum formulas.

My client is now asking for specific printing formatting options that are
normally setup after the information gets to the spreadsheet. The list is
below but I have no idea where to even begin looking for information on
these. Basically once the spreadsheet is setup and opened, they want the
settings below predone. Since I am overwriting the file each time these
formatting options are also overwritten.

I have found a couple of references to some printing options but they don't
all seem to work as expected. For example, I am trying to set the gridlines
to show with

ExcelSheet.PageSetup.PrintGridlines = True

This seems to work ok but when I try to set the pages to 1 page wide by
whatever tall I don't get what I want. It continues to expand past the page
but does not show the remaining columns that do not show on the first page
and I get many copyies of the same pages.

ExcelSheet.PageSetup.FitToPagesWide = 1

I am trying to set the first row to print on every page but it does not seem
to be working either. I would prefer to just say Row A and all of them show:

ExcelSheet.PageSetup.PrintTitleColumns = "$A:$CZ"

1. Landscape orientation
2. Legal size paper
3. 0.2" margins, except bottom edge @ 0.75"
4. Footer: Left - Date & time printed; Centre - Name of Show; Right -Page#
of #pages
5. Fit to print 1 page wide by however many pages long
6. Gridlines
7. Repeat header row on every page

One to add is how to set the Freeze Panes? I usually can tell in the code
what the cell would be if I were to do it manually but am not sure about what
function to use.

TIA rasinc