Inserting Page break after field value changes in excel?? 2

[neemi]

Can anyone help me!!?

I have a table which is exported into excel from Microsoft Access 2000.

The table includes student details with the first colum being the Centre they are studying at followed by the rest of their details in the preceding columns.

What I am trying to acheive is a procedure which I can run each time I export this Spreadsheet to Excel that will automatically insert a page break and a new row when the Centre Name in the first column changes. so that the student from the centres can be grouped together.

I had a look at the help files and I know to enter a page break you would use the foolwing code:-

worksheets ("Sheet1&quot.Row(25).pagebreak = xlPagebreakManual

However this seems to me that you will need to know the row number that a page break is needed. Whereas I need to enter it whenever the Centre name changes. So I need some sort of comparison of the rows in colum 1, 1 by 1 and when they are different insert a page break.

I need help in coding this.

Solving this would be of so much help and will be greatly appreciated.

I have not really used vba in excel, only access. I know they are virtually the same but please help.

Thanks in advance.

Neemi

 

[RobBroekhuis]

Assuming there are no empty rows in your table (so that an empty cell signifies the end of the data), you can use:

iRow=2
Centre=cells(1,1)
do while cells(iRow,1)<>&quot;&quot;
if cells(iRow,1)<>centre then
Centre=cells(iRow,1)
activesheet.cells(iRow,1).entirerow.insert
activesheet.cells(iRow,1).pagebreak = xlPagebreakManual
iRow=iRow+1
end if
iRow=iRow+1
loop

Rob

 

[neemi]

Hi Rob...

Thanks for your response!

It works great.. However.....

The first row in my spreadsheet contains some column headings and it is inserting a newline and page break after the volumn headings aswell.

Also it seems to be inserting a page break and then the new row so that the first row in each section is empty whereas I want the last row of each section to be empty so that I can enter some values in there...

Could you please also tell me what the code is doing exactly so I can understand it more and try to develop it further in the future.

Cheers in advance

Neemi

 

[RobBroekhuis]

It's just a basic loop with iRow as a counter variable. To ignore the first row, just start at row 2 instead of 3, and change the order of the row skip and pagebreak insert lines to get the blank row at the end.

iRow=3
Centre=cells(iRow-1,1) 'variable to hold the previous row's Centre value
do while cells(iRow,1)<>&quot;&quot; 'keep going until the end
if cells(iRow,1)<>centre then 'if row# iRow has a new Centre value
Centre=cells(iRow,1)
activesheet.cells(iRow,1).entirerow.insert 'insert a row
iRow=iRow+1 'skip over the empty row
activesheet.cells(iRow,1).pagebreak = xlPagebreakManual 'insert pagebreak
end if
iRow=iRow+1 'go to next row
loop 'loop back until done


Rob

 

[neemi]

Thanks rob..

I have anoyher problem with the code now.. When I go to run it i am getting a message saying that the Macros in the project are disabled.

I don't know wat is going on.

;(

 

[RobBroekhuis]

Go to Tools-Macro-Security and check your macro security setting. Mine is always set to Low, so I'm not very familiar with how to deal with more stringent settings. Setting it to &quot;medium&quot; should still allow you to run macros (you may have to exit and re-enter Excel to have it take effect on your current workbook).
Others may have more pertinent help for this particular question...
Rob

 

[neemi]

Hi rob..

One last question if possible....

Along with my student details I have some cloumns with some numbers in them.

the whole point of me entering an extra row after each Centre is so that I can sum the columns with numbers in to give me totals for each centre.

Could you help me with this.

The columns I want to sum for each centre are columns 9 to 14.

I know I need to store the cell value for the first and last of each centre....

But I'm stuck with the coding.

your help is appreciated.

Neemi

 

[Loomah]

Hi neemi
Just a thought
Have you tried using subtotals?

Data>Subtotals

You can add as many totals as you like, choose when to insert them (make sure you sort the list beforehand) in your case everytime the Centre Name changes and add page breaks automatically.

No need to code anything but if you're going to do it a lot, just record yourself doing it once.
;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[RobBroekhuis]

iRow=3
iRow0=2
Centre=cells(iRow-1,1)
do while cells(iRow,1)<>&quot;&quot;
if cells(iRow,1)<>centre then
Centre=cells(iRow,1)
cells(iRow,1).entirerow.insert
range(cells(iRow,9),cells(iRow,14)).formulaR1C1= _
&quot;=sum(R&quot; & iRow0 & &quot;C:R&quot; & iRow-1 & &quot;C)&quot;
iRow=iRow+1
iRow0=iRow
cells(iRow,1).pagebreak = xlPagebreakManual
end if
iRow=iRow+1 'go to next row
loop 'loop back until done


Rob

 

[neemi]

Thanks for all your help rob!

It really helped me speed up a process

Cheers.

Gave you a star for your help

Neemi

 

[neemi]

Hi again rob...

I have got the page breaks and sub totals working fine know in excel.

I have another q.

I export the database from access using the following ado code---

Public Function CreateExcel()

Dim rs As New ADODB.Recordset
Dim con As Object

Set con = Application.CurrentProject.Connection

' Open Recordset
rs.Open &quot;tblTesting&quot;, con, adOpenKeyset, adLockOptimistic

Dim oExcel As Object
Dim obook As Object
Dim osheet As Object

Set oExcel = CreateObject(&quot;Excel.Application&quot
Set obook = oExcel.workbooks.Add
Set osheet = obook.worksheets(1)

' Set column headings

osheet.range(&quot;A1&quot = &quot;Level&quot;
osheet.range(&quot;B1&quot = &quot;Centre&quot;
osheet.range(&quot;C1&quot = &quot;Student Code&quot;
osheet.range(&quot;D1&quot = &quot;Surname&quot;
osheet.range(&quot;E1&quot = &quot;Forename&quot;
osheet.range(&quot;F1&quot = &quot;Age&quot;
osheet.range(&quot;G1&quot = &quot;Int Qual Code&quot;
osheet.range(&quot;H1&quot = &quot;Nat Qual Code&quot;
osheet.range(&quot;I1&quot = &quot;Int Qual Name&quot;
osheet.range(&quot;J1&quot = &quot;GLH&quot;
osheet.range(&quot;K1&quot = &quot;Entry&quot;
osheet.range(&quot;L1&quot = &quot;On Prog&quot;
osheet.range(&quot;M1&quot = &quot;Achieve&quot;
osheet.range(&quot;N1&quot = &quot;Fee Rem&quot;
osheet.range(&quot;O1&quot = &quot;WP Unit&quot;
osheet.range(&quot;P1&quot = &quot;Add Supp&quot;
osheet.range(&quot;Q1&quot = &quot;Total Actual Units&quot;

osheet.range(&quot;A:Q&quot.Font.Size = &quot;8.5&quot;

' Format the headings
With osheet.range(&quot;A1:Q1&quot
.RowHeight = 35
.Interior.ColorIndex = 37
.Interior.Pattern = 1
.borders(8).LineStyle = 1
.borders(8).Weight = 2
.borders(8).ColorIndex = -4105
.borders(9).LineStyle = 1
.borders(9).Weight = -4138
.borders(9).ColorIndex = -4105
End With

osheet.range(&quot;A1&quot.ColumnWidth = 1
osheet.range(&quot;B1&quot.ColumnWidth = 10.86
osheet.range(&quot;C1:E1&quot.ColumnWidth = 8.43
osheet.range(&quot;F1&quot.ColumnWidth = 4.43
osheet.range(&quot;G1&quot.ColumnWidth = 10.57
osheet.range(&quot;H1&quot.ColumnWidth = 8.29
osheet.range(&quot;I1&quot.ColumnWidth = 16.44
osheet.range(&quot;J1&quot.ColumnWidth = 4.47

' Format the funding cells

With osheet.range(&quot;K:Q&quot
.numberformat = &quot;0.00&quot;
.ColumnWidth = 6.29
End With

' osheet.range(&quot;A2&quot.FreezePanes = True

With osheet.PageSetup
.PrintTitleRows = &quot;$1:$1&quot;
.Orientation = 2
.PrintGridlines = True
.CenterFooter = &quot;Page &P&quot;
End With

' transfer data to excel

osheet.range(&quot;A2&quot.copyfromrecordset rs

osheet.range(&quot;A:A&quot.Delete shift:=-4159

' Save and quit Excel

obook.saveas &quot;E:\Natdat\Testing\TestExport.xls&quot;
oExcel.Quit

rs.Close

End Function

What I would like to do is perform the calculations of the subtotals and insert the page breaks through access as one full process?

Can you give any advise?

Is there a way I can export the data from access and then format my spreadsheet?

I need to combine the code I have included and the code you helped me with.

Cheers,

Neemi

 

[RobBroekhuis]

I've not used Access, but I see no reason why you couldn't use the code we worked out before verbatim in your Access VBA routine, with the exception that you'll have to enclose the whole thing inside a

with osheet
...
end with

structure, preceding each sheet property with a period. Something like

iRow=3
iRow0=2
with osheet

Centre=.cells(iRow-1,1)
do while .cells(iRow,1)<>&quot;&quot;
if .cells(iRow,1)<>centre then
Centre=.cells(iRow,1)
.cells(iRow,1).entirerow.insert
.range(.cells(iRow,9),.cells(iRow,14)).formulaR1C1= _
&quot;=sum(R&quot; & iRow0 & &quot;C:R&quot; & iRow-1 & &quot;C)&quot;
iRow=iRow+1
iRow0=iRow
.cells(iRow,1).pagebreak = xlPagebreakManual
end if
iRow=iRow+1
loop
end with

Hope that works...
Rob

 

[neemi]

I tryed wat you suggested....

But it dont work..

There is a compile error- the sub or function &quot;cells&quot; is not defined.

I don't know wat to do.

trying to suz it but any help appreciated.

cheers

neemi

 

[RobBroekhuis]

If it's a compile error (as opposed to a runtime error), create a reference to the Excel object model from your Access VBE (use Tools-References and check the appropriate checkbox).
Rob

 

[neemi]

I've sussed the cell thing now.

I had to include osheet before each cell... e.g. osheet.cell etc.

But,

the code you helped me with is great... however one small thing that i have been trying to sort out but can't.

The Totals work great but the last group of Centres is not having the Totals calculated as the loop is is working unitill there is a &quot;&quot; value.

I tryed changing the do while...loop to a do...loop untill. but still don't calculate the last set of totals.

thanks in advance again.

Neemi

 

[RobBroekhuis]

Just add an additional statement after the end of the do..loop:

range(cells(iRow,9),cells(iRow,14)).formulaR1C1= _
&quot;=sum(R&quot; & iRow0 & &quot;C:R&quot; & iRow-1 & &quot;C)&quot;

Rob