Page Break in Excel from VB 2005

**rowe_newsgroups** · Jul 9 '07, 04:45 PM

Re: Page Break in Excel from VB 2005

On Jul 9, 12:24 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

I'm using VB 2005 and Excel 2007 and cannot figure out how to insert a page
break before line 72. I've tried several ways but always get an exception
error.
>
Thanks in advance.

I would recommend you use the "Record Macro" functionality of Excel
and record a macro of you adding the pagebreak. Then you just have to
modify that classic vb code to Visual Basic .Net and use it in your
application.

Thanks,

Seth Rowe

**Stephen Plotnick** · Jul 9 '07, 04:45 PM

Re: Page Break in Excel from VB 2005

Seth,

THanks for the reply. That is what I did and I always get an exception at
run time. I googled and seemed to have tried everything and it never seems
to work.

Steve
"rowe_newsgroup s" <rowe_email@yah oo.comwrote in message
news:1183998991 .748123.138610@ 22g2000hsm.goog legroups.com...

On Jul 9, 12:24 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

>I'm using VB 2005 and Excel 2007 and cannot figure out how to insert a
>page
>break before line 72. I've tried several ways but always get an exception
>error.
>>
>Thanks in advance.

>
I would recommend you use the "Record Macro" functionality of Excel
and record a macro of you adding the pagebreak. Then you just have to
modify that classic vb code to Visual Basic .Net and use it in your
application.
>
Thanks,
>
Seth Rowe
>

**rowe_newsgroups** · Jul 9 '07, 04:55 PM

Re: Page Break in Excel from VB 2005

On Jul 9, 12:43 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

Seth,
>
THanks for the reply. That is what I did and I always get an exception at
run time. I googled and seemed to have tried everything and it never seems
to work.
>
Steve"rowe_news groups" <rowe_em...@yah oo.comwrote in message
>
news:1183998991 .748123.138610@ 22g2000hsm.goog legroups.com...
>

On Jul 9, 12:24 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

I'm using VB 2005 and Excel 2007 and cannot figure out how to insert a
page
break before line 72. I've tried several ways but always get an exception
error.

>

Thanks in advance.

>

I would recommend you use the "Record Macro" functionality of Excel
and record a macro of you adding the pagebreak. Then you just have to
modify that classic vb code to Visual Basic .Net and use it in your
application.

>

Thanks,

>

Seth Rowe

Post your code.

Thanks,

Seth Rowe

**Stephen Plotnick** · Jul 9 '07, 05:25 PM

Re: Page Break in Excel from VB 2005

'osheet.HPageBr eaks.Add.Range( "A72")
..

"rowe_newsgroup s" <rowe_email@yah oo.comwrote in message
news:1183999625 .014207.264600@ q75g2000hsh.goo glegroups.com.. .

On Jul 9, 12:43 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

>Seth,
>>
>THanks for the reply. That is what I did and I always get an exception at
>run time. I googled and seemed to have tried everything and it never
>seems
>to work.
>>
>Steve"rowe_new sgroups" <rowe_em...@yah oo.comwrote in message
>>
>news:118399899 1.748123.138610 @22g2000hsm.goo glegroups.com.. .
>>

On Jul 9, 12:24 pm, "Stephen Plotnick" <the...@attglob al.netwrote:
>I'm using VB 2005 and Excel 2007 and cannot figure out how to insert a
>page
>break before line 72. I've tried several ways but always get an
>exception
>error.

>>

>Thanks in advance.

>>

I would recommend you use the "Record Macro" functionality of Excel
and record a macro of you adding the pagebreak. Then you just have to
modify that classic vb code to Visual Basic .Net and use it in your
application.

>>

Thanks,

>>

Seth Rowe

>
Post your code.
>
Thanks,
>
Seth Rowe
>

**rowe_newsgroups** · Jul 9 '07, 06:25 PM

Re: Page Break in Excel from VB 2005

On Jul 9, 1:18 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

'osheet.HPageBr eaks.Add.Range( "A72")
.
>
"rowe_newsgroup s" <rowe_em...@yah oo.comwrote in message
>
news:1183999625 .014207.264600@ q75g2000hsh.goo glegroups.com.. .
>

On Jul 9, 12:43 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

Seth,

>

THanks for the reply. That is what I did and I always get an exception at
run time. I googled and seemed to have tried everything and it never
seems
to work.

>

Steve"rowe_news groups" <rowe_em...@yah oo.comwrote in message

>

>news:118399899 1.748123.138610 @22g2000hsm.goo glegroups.com.. .

>

On Jul 9, 12:24 pm, "Stephen Plotnick" <the...@attglob al.netwrote:
I'm using VB 2005 and Excel 2007 and cannot figure out how to insert a
page
break before line 72. I've tried several ways but always get an
exception
error.

>

Thanks in advance.

>

I would recommend you use the "Record Macro" functionality of Excel
and record a macro of you adding the pagebreak. Then you just have to
modify that classic vb code to Visual Basic .Net and use it in your
application.

>

Thanks,

>

Seth Rowe

>

Post your code.

>

Thanks,

>

Seth Rowe

You need to specify the range using the excel application object. The
following works fine (except for a late binding warning for using
HPageBreaks) for me:

Option Strict Off

'// Of course you'll need to reference
'// Microsoft Excel Object Library 12.0 for this to work
Imports Microsoft.Offic e.Interop.Excel

Module Module1

Sub Main()
Dim excel As New Application()
Dim workbook As Workbook = excel.Workbooks .Add()
workbook.Worksh eets(1).HPageBr eaks.Add(excel. Range("A5"))
excel.Visible = True
End Sub

End Module

Hope That Helps!

Thanks,

Seth Rowe

**Stephen Plotnick** · Jul 9 '07, 10:15 PM

Re: Page Break in Excel from VB 2005

obook.osheet.HP ageBreaks.Add(E xcel.Range
("A72"))

I get the blue line under Excel.Range

Error 1 'Range' is a type in 'Excel' and cannot be used as an expression.

"rowe_newsgroup s" <rowe_email@yah oo.comwrote in message
news:1184005431 .309811.162690@ g4g2000hsf.goog legroups.com...

On Jul 9, 1:18 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

> 'osheet.HPageBr eaks.Add.Range( "A72")
>.
>>
>"rowe_newsgrou ps" <rowe_em...@yah oo.comwrote in message
>>
>news:118399962 5.014207.264600 @q75g2000hsh.go oglegroups.com. ..
>>

On Jul 9, 12:43 pm, "Stephen Plotnick" <the...@attglob al.netwrote:
>Seth,

>>

>THanks for the reply. That is what I did and I always get an exception
>at
>run time. I googled and seemed to have tried everything and it never
>seems
>to work.

>>

>Steve"rowe_new sgroups" <rowe_em...@yah oo.comwrote in message

>>

>>news:11839989 91.748123.13861 0@22g2000hsm.go oglegroups.com. ..

>>

On Jul 9, 12:24 pm, "Stephen Plotnick" <the...@attglob al.netwrote:
>I'm using VB 2005 and Excel 2007 and cannot figure out how to
>insert a
>page
>break before line 72. I've tried several ways but always get an
>exception
>error.

>>

>Thanks in advance.

>>

I would recommend you use the "Record Macro" functionality of Excel
and record a macro of you adding the pagebreak. Then you just have
to
modify that classic vb code to Visual Basic .Net and use it in your
application.

>>

Thanks,

>>

Seth Rowe

>>

Post your code.

>>

Thanks,

>>

Seth Rowe

>
You need to specify the range using the excel application object. The
following works fine (except for a late binding warning for using
HPageBreaks) for me:
>
Option Strict Off
>
'// Of course you'll need to reference
'// Microsoft Excel Object Library 12.0 for this to work
Imports Microsoft.Offic e.Interop.Excel
>
Module Module1
>
Sub Main()
Dim excel As New Application()
Dim workbook As Workbook = excel.Workbooks .Add()
workbook.Worksh eets(1).HPageBr eaks.Add(excel. Range("A5"))
excel.Visible = True
End Sub
>
End Module
>
Hope That Helps!
>
Thanks,
>
Seth Rowe
>

**rowe_newsgroups** · Jul 10 '07, 12:25 AM

Re: Page Break in Excel from VB 2005

On Jul 9, 6:05 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

obook.osheet.HP ageBreaks.Add(E xcel.Range
("A72"))
>
I get the blue line under Excel.Range
>
Error 1 'Range' is a type in 'Excel' and cannot be used as an expression.
>
"rowe_newsgroup s" <rowe_em...@yah oo.comwrote in message
>
news:1184005431 .309811.162690@ g4g2000hsf.goog legroups.com...
>
>
>

On Jul 9, 1:18 pm, "Stephen Plotnick" <the...@attglob al.netwrote:

'osheet.HPageBr eaks.Add.Range( "A72")
.

>

"rowe_newsgroup s" <rowe_em...@yah oo.comwrote in message

>

>news:118399962 5.014207.264600 @q75g2000hsh.go oglegroups.com. ..

>

On Jul 9, 12:43 pm, "Stephen Plotnick" <the...@attglob al.netwrote:
Seth,

>

THanks for the reply. That is what I did and I always get an exception
at
run time. I googled and seemed to have tried everything and it never
seems
to work.

>

Steve"rowe_news groups" <rowe_em...@yah oo.comwrote in message

>

>news:118399899 1.748123.138610 @22g2000hsm.goo glegroups.com.. .

>

On Jul 9, 12:24 pm, "Stephen Plotnick" <the...@attglob al.netwrote:
I'm using VB 2005 and Excel 2007 and cannot figure out how to
insert a
page
break before line 72. I've tried several ways but always get an
exception
error.

>

Thanks in advance.

>

I would recommend you use the "Record Macro" functionality of Excel
and record a macro of you adding the pagebreak. Then you just have
to
modify that classic vb code to Visual Basic .Net and use it in your
application.

>

Thanks,

>

Seth Rowe

>

Post your code.

>

Thanks,

>

Seth Rowe

>

You need to specify the range using the excel application object. The
following works fine (except for a late binding warning for using
HPageBreaks) for me:

>

Option Strict Off

>

'// Of course you'll need to reference
'// Microsoft Excel Object Library 12.0 for this to work
Imports Microsoft.Offic e.Interop.Excel

>

Module Module1

>

Sub Main()
Dim excel As New Application()
Dim workbook As Workbook = excel.Workbooks .Add()
workbook.Worksh eets(1).HPageBr eaks.Add(excel. Range("A5"))
excel.Visible = True
End Sub

>

End Module

>

Hope That Helps!

>

Thanks,

>

Seth Rowe- Hide quoted text -

>
- Show quoted text -

You looked at my code wrong - I used the instance "excel" not the type
"Excel." You need to use the instance of Excel.Applicati on that you
use to create the excel workbooks.

Thanks,

Seth Rowe

**rowe_newsgroups** · Jul 10 '07, 02:45 PM

Re: Page Break in Excel from VB 2005

Okay, I'm working on your code, but you didn't post all of it. I need
to see the rest of Sub Main as well as the function "Generate_Repor t".

Thanks,

Seth Rowe

**rowe_newsgroups** · Jul 10 '07, 09:05 PM

Re: Page Break in Excel from VB 2005

The generate_rtn is a routie that checks a line counter and based on the

value calls another routine. I have around 40 lines in a text file that is
being created from another program and I build rows in the spreadsheet based
on the lines in the text file.

You'll need to add the line counter back then and pass it as a
parameter to the Generate_Report method. I removed it as I didn't see
the point to it at the time (though I guessed it had something to do
with the Generate_Report method)

Thanks,

Seth Rowe

**Stephen Plotnick** · Jul 11 '07, 09:46 AM

Re: Page Break in Excel from VB 2005

Seth,

The page break is now working, thanks.

I'm going to start to move the public variables. Is there a reason not to
keep them there? It seems so much easier:)

The reason I did not have the spreadsheet visible is because this program is
going to run a couple hundred times in a day and the time to open and close
excel itself would be very time consuming. After each run an E-mail is
created with the XLSX and the PDF. You mention a possible memory leak. Is
that a normal concern here?

I did not post the entire program because it is almost 2000 lines long. The
generate lines rountine has many sub routines.

Once again thanks for you efforts. I just can't believe I struggled so long
with what should be something simple like a page break. I'm doing all kinds
of conditional fomratting, etc. without an issues. I guess my first VB
program is more of a learning experience. I've been writing COBOL programs
forever:)

Thanks,
Steve
"rowe_newsgroup s" <rowe_email@yah oo.comwrote in message
news:1184100961 .973490.262380@ w3g2000hsg.goog legroups.com...

>The generate_rtn is a routie that checks a line counter and based on the
>value calls another routine. I have around 40 lines in a text file that
>is
>being created from another program and I build rows in the spreadsheet
>based
>on the lines in the text file.

>
You'll need to add the line counter back then and pass it as a
parameter to the Generate_Report method. I removed it as I didn't see
the point to it at the time (though I guessed it had something to do
with the Generate_Report method)
>
Thanks,
>
Seth Rowe
>
>

Page Break in Excel from VB 2005

Page Break in Excel from VB 2005

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment