I am trying to create a report, which is very similar to a ?cross tab 

report.?  The first column of the report is a series of days of the month 

(1 day = one row). The additional columns represent parameters, which can 

vary in number from eight to over a hundred.  This creates a report that 

is a horizontal nightmare.  I can fit comfortably the first column of Days 

plus eleven parameters on one page.  What I would like to do is divide the 

report into pages based upon having the first column of Days plus eleven 

parameters.  In other words, page one would have Days, plus parameters 1 

to 11.  Page two would have Days, plus parameters 12 to 22 etc...  The 

central tenet is that the first column (Days) would always appear on each 

page (the last page would have parameters varying from 1 column to 11 

columns).  When I try to create this report, the subsequent page simply 

overwrites the previous page. I get one page instead of the desired many 

pages.   I am unable to force the report to print to a new page when the 

program loops through the report. 



 I created this report in the manner that Getz et al wrote in their book, 

A2K Developer?s Handbook, vol. 1, pages 727-733.  

Wow! I think you're using the wrong tool to produce the report ... This 

sounds like a job for Excel. If you can send the data to Excel you can 

format that resulting spreadsheet to repeat the first column on each page. 

This can all be done through VBA from Access.

... AL







> I am trying to create a report, which is very similar to a ?cross tab 

> report.?  The first column of the report is a series of days of the 

month 

> (1 day = one row). The additional columns represent parameters, which 

can 

> vary in number from eight to over a hundred.  This creates a report that 

> is a horizontal nightmare.  I can fit comfortably the first column of 

Days 

> plus eleven parameters on one page.  What I would like to do is divide 

the 

> report into pages based upon having the first column of Days plus eleven 

> parameters.  In other words, page one would have Days, plus parameters 1 

> to 11.  Page two would have Days, plus parameters 12 to 22 etc...  The 

> central tenet is that the first column (Days) would always appear on 

each 

> page (the last page would have parameters varying from 1 column to 11 

> columns).  When I try to create this report, the subsequent page simply 

> overwrites the previous page. I get one page instead of the desired many 

> pages.   I am unable to force the report to print to a new page when the 

> program loops through the report. 

> 

>  I created this report in the manner that Getz et al wrote in their 

book, 

> A2K Developer?s Handbook, vol. 1, pages 727-733.  

> Wow! I think you're using the wrong tool to produce the report ... This 

> sounds like a job for Excel. If you can send the data to Excel you can 

> format that resulting spreadsheet to repeat the first column on each 

page. 

> This can all be done through VBA from Access.

> ... AL

> 

> 

> 

> > I am trying to create a report, which is very similar to a ?cross tab 

> > report.?  The first column of the report is a series of days of the 

> month 

> > (1 day = one row). The additional columns represent parameters, which 

> can 

> > vary in number from eight to over a hundred.  This creates a report 

that 

> > is a horizontal nightmare.  I can fit comfortably the first column of 

> Days 

> > plus eleven parameters on one page.  What I would like to do is divide 

> the 

> > report into pages based upon having the first column of Days plus 

eleven 

> > parameters.  In other words, page one would have Days, plus parameters 

1 

> > to 11.  Page two would have Days, plus parameters 12 to 22 etc...  The 

> > central tenet is that the first column (Days) would always appear on 

> each 

> > page (the last page would have parameters varying from 1 column to 11 

> > columns).  When I try to create this report, the subsequent page 

simply 

> > overwrites the previous page. I get one page instead of the desired 

many 

> > pages.   I am unable to force the report to print to a new page when 

the 

> > program loops through the report. 

> > 

> >  I created this report in the manner that Getz et al wrote in their 

> book, 

> > A2K Developer?s Handbook, vol. 1, pages 727-733.  

Al, 

It looks like I now learn automation up close and personal.  I think I 

know how to populate the spreedsheet, just not sure how to format Excel to 

print first column on each page from Access.

Thanks,

Terry-- 



Wow! I think you're using the wrong tool to produce the report ... This 

> sounds like a job for Excel. If you can send the data to Excel you can 

> format that resulting spreadsheet to repeat the first column on each 

page. 

> This can all be done through VBA from Access.

> ... AL

> 

> 

> 

> > I am trying to create a report, which is very similar to a ?cross tab 

> > report.?  The first column of the report is a series of days of the 

> month 

> > (1 day = one row). The additional columns represent parameters, which 

> can 

> > vary in number from eight to over a hundred.  This creates a report 

that 

> > is a horizontal nightmare.  I can fit comfortably the first column of 

> Days 

> > plus eleven parameters on one page.  What I would like to do is divide 

> the 

> > report into pages based upon having the first column of Days plus 

eleven 

> > parameters.  In other words, page one would have Days, plus parameters 

1 

> > to 11.  Page two would have Days, plus parameters 12 to 22 etc...  The 

> > central tenet is that the first column (Days) would always appear on 

> each 

> > page (the last page would have parameters varying from 1 column to 11 

> > columns).  When I try to create this report, the subsequent page 

simply 

> > overwrites the previous page. I get one page instead of the desired 

many 

> > pages.   I am unable to force the report to print to a new page when 

the 

> > program loops through the report. 

> > 

> >  I created this report in the manner that Getz et al wrote in their 

> book, 

> > A2K Developer?s Handbook, vol. 1, pages 727-733.  

> Al, 

> It looks like I now learn automation up close and personal.  I think I 

> know how to populate the spreedsheet, just not sure how to format Excel 

to 

> print first column on each page from Access.

> Thanks,

> Terry-- 

> 

Here's a routine I was using recently to format a spreadsheet I created 

that was being e-mailed via Lotus Notes (also in VBA). The parameters are 

the filename of the workbook, and, optionally, the sheet name, otherwise 

it defaults to the first sheet.

====================================



Option Compare Database

Option Explicit



Function SheetFormat(sFileName As String, Optional vSheet As Variant = 1) 

As Boolean

    Dim objXL As Object

    Dim objWbk As Object

    Dim boolXL As Boolean

    Const xlLandscape As Integer = 2

    

    If fIsAppRunning("Excel") Then

        Set objXL = GetObject(, "Excel.Application")

        boolXL = False

    Else

        Set objXL = CreateObject("Excel.Application")

        boolXL = True

    End If

    

    objXL.Application.Visible = True    ''''    for debugging only

    

    Set objWbk = objXL.Workbooks.Open(sFileName)

    With objWbk

        With .Worksheets(vSheet)

            .Range("A1:H1").Font.Bold = True

            .Range("A1:H1").Interior.ColorIndex = 15

            .Columns("A:H").AutoFit

            With .PageSetup

                .PrintTitleRows = "$1:$1"

                .PrintTitleColumns = "$A:$B"

                .LeftMargin = objXL.Application.InchesToPoints(0.25)

                .RightMargin = objXL.Application.InchesToPoints(0.25)

                .CenterHorizontally = True

                .Orientation = xlLandscape

                .FitToPagesWide = 1

                .FitToPagesTall = 5

                .Zoom = False           ''' to select FitTo rather than 

Adjust (.Zoom=100)

            End With

            .Cells(1, 1).Select

        End With

        .Save

        .Close

    End With

    

    If boolXL Then objXL.Application.Quit

    

    Set objWbk = Nothing

    Set objXL = Nothing

    

    SheetFormat = True



End Function



========================================================

Sorry, Terry, I forgot to mention a few things:

1. the routine fIsAppRunning is available at  

http://www.mvps.org/access/api/api0007.htm

2. You need to set a Reference in MS-Access to the Excel object library by 

opening any Module in design view (even a "new" module) and pick <Tools> 

<References> off the menu. This will present you with a list 

of "reference" files available ... check the one that's called "Microsoft 

Excel 8.0 Object Library".  This is the same game you do with other 

applications that have object libraries you can use.



In case you haven't noticed, I'm a novice at playing with this stuff too, 

but browsing the various web sites has given me a whole whack of tools to 

use. (I used to have a GREAT mdb file I called MySamples.mdb that I 

collected all sorts of useful routines into, and used as templates for 

future projects. When I left my last job, I saved a bunch of mdb files 

onto ZIP disks, and took them home, only to find that my ZIP drive refused 

to read them. [ZIP drives must be made by MURPHY!) I'm now trying to 

rebuild such a Samples file.



... AL

Hey Al, you are a good man.  Thanks for the help.  This gives me a good 

starting point.  I had previously considered Excel as the solution, only 

to talk myself out of it because of the extral layer it adds to the 

complexity.  However you are right, it is the logical choice.

Thanks again,

Terry Franklin



> Sorry, Terry, I forgot to mention a few things:

> 1. the routine fIsAppRunning is available at  

> http://www.mvps.org/access/api/api0007.htm

> 2. You need to set a Reference in MS-Access to the Excel object library 

by 

> opening any Module in design view (even a "new" module) and pick <Tools> 

> <References> off the menu. This will present you with a list 

> of "reference" files available ... check the one that's 

called "Microsoft 

> Excel 8.0 Object Library".  This is the same game you do with other 

> applications that have object libraries you can use.

> 

> In case you haven't noticed, I'm a novice at playing with this stuff 

too, 

> but browsing the various web sites has given me a whole whack of tools 

to 

> use. (I used to have a GREAT mdb file I called MySamples.mdb that I 

> collected all sorts of useful routines into, and used as templates for 

> future projects. When I left my last job, I saved a bunch of mdb files 

> onto ZIP disks, and took them home, only to find that my ZIP drive 

refused 

> to read them. [ZIP drives must be made by MURPHY!) I'm now trying to 

> rebuild such a Samples file.

> 

> ... AL