Page Break in Excel from VB 2005

  • Thread starter Thread starter Stephen Plotnick
  • Start date Start date
S

Stephen Plotnick

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'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.
Click to expand...

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
 
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
 
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.
Click to expand...

Post your code.

Thanks,

Seth Rowe
 
'osheet.HPageBreaks.Add.Range("A72")
.
Click to expand...

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.Office.Interop.Excel

Module Module1

Sub Main()
Dim excel As New Application()
Dim workbook As Workbook = excel.Workbooks.Add()
workbook.Worksheets(1).HPageBreaks.Add(excel.Range("A5"))
excel.Visible = True
End Sub

End Module

Hope That Helps!

Thanks,

Seth Rowe
 
obook.osheet.HPageBreaks.Add(Excel.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.
 
obook.osheet.HPageBreaks.Add(Excel.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.













- Show quoted text -
Click to expand...

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

Thanks,

Seth Rowe
 
I can't believe this simple problem...

I'm very new to VB...

My code is very similar but my initial DIM is here.

Public oexcel As ApplicationClass = Nothing

oexcel = New ApplicationClass()

Here is my line in the program.

obook.osheet.HPageBreaks.Add(oexcel.Range("A72"))

Here is the exception.

System.MissingMemberException was unhandled
Message="Public member 'osheet' on type 'WorkbookClass' not found."
Source="Microsoft.VisualBasic"
StackTrace:
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object
o, Type objType, String name, Object[] args, String[] paramnames, Boolean[]
CopyBack)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at MeasureMonitorSpreadsheet.Module1.Main() in
C:\Users\SP\Documents\Visual Studio
2005\Projects\MeasureMonitorSpreadsheet\Module1.vb:line 565
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
 
I can't believe this simple problem...

I'm very new to VB...

My code is very similar but my initial DIM is here.

Public oexcel As ApplicationClass = Nothing

oexcel = New ApplicationClass()

Here is my line in the program.

obook.osheet.HPageBreaks.Add(oexcel.Range("A72"))

Here is the exception.

System.MissingMemberException was unhandled
Message="Public member 'osheet' on type 'WorkbookClass' not found."
Source="Microsoft.VisualBasic"
StackTrace:
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object
o, Type objType, String name, Object[] args, String[] paramnames, Boolean[]
CopyBack)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at MeasureMonitorSpreadsheet.Module1.Main() in
C:\Users\SP\Documents\Visual Studio
2005\Projects\MeasureMonitorSpreadsheet\Module1.vb:line 565
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()


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

Seth Rowe
Click to expand...
Click to expand...

I don't see where you dimension obook or osheet. Could you please post
the entire code you use?

Thanks,

Seth Rowe
 
Here is the code

Module Module1
Public conn As OLEDBConnection()
Public Filename As String
Public PDFFilename As String
Public ImageFileName As String
Public chkexcel As Boolean
Public oexcel As ApplicationClass = Nothing
Public obook As Workbook = Nothing
Public osheet As Worksheet = Nothing
Public LineNumber As Integer
Public cfColorScale As ColorScale = Nothing
Public cfIconSet As IconSetCondition = Nothing
Public R As Integer
Public InputNumber As Integer
Public MultiLetter As String
Public line As String
Public LastColumn As Integer
Public WorkCost As Long
Public WorkRetail As Long
Public WorkArea1 As Long
Public WorkArea2 As Long
Public WorkArea3 As Long
Public YTDNAME As String
Public CURMONTH As String
Public TWELVEMONTHNAME As String
Public YTDNAMELY As String
Public CURMONTHLY As String
Public TWELVEMONTHNAMELY As String

Sub Main()
Filename = AppDomain.CurrentDomain.BaseDirectory & "Steve.xlsx"
ImageFileName = AppDomain.CurrentDomain.BaseDirectory &
"GroupCBFLogoBetterWay.JPG"
PDFFilename = AppDomain.CurrentDomain.BaseDirectory & "Steve.pdf"
'check if file already exists then delete it to create a new file
If File.Exists(Filename) Then
File.Delete(Filename)
End If
If Not File.Exists(Filename) Then
chkexcel = False
'create new excel application
oexcel = CreateObject("Excel.Application")
'oexcel = New ApplicationClass()
'add a new workbook
obook = oexcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
'set the application alerts not to be displayed for confirmation
oexcel.Application.DisplayAlerts = True
'check total sheets in workbook
Dim S As Integer = oexcel.Application.Sheets.Count()
'leaving first sheet delete all the remaining sheets
If S > 1 Then
oexcel.Application.DisplayAlerts = False
Dim J As Integer = S
Do While J > 1
oexcel.Application.Sheets(J).delete()
J = oexcel.Application.Sheets.Count()
Loop
End If
'to check the session of excel application
chkexcel = True
Console.WriteLine("Generating Steve Report")
osheet = obook.Worksheets(1)
osheet.Name = "Measure and Monitoring"
oexcel.Visible = False
'Try
'Dbopen()
Try
' Create an instance of StreamReader to read from a file.
Using sr As StreamReader = New StreamReader("workdept.txt")
LineNumber = 1

' Read and display the lines from the file until the end
' of the file is reached.
Do
line = sr.ReadLine()
Generate_Sheet()
LineNumber = LineNumber + 1
Loop Until line Is Nothing
sr.Close()
End Using
Catch E As Exception
' Let the user know what went wrong.
Console.WriteLine("The file could not be read:")
Console.WriteLine(E.Message)
End Try

osheet.Range("A2:AZ400").Font.Size = 8.5
osheet.Range("1:1").Font.Size = 8
osheet.Range("A1:AZ400").Font.Bold = True
osheet.Range("A1:AZ400").Font.Name = "Segoe(UI)"
osheet.Range("A1").Value = "Report for Steve"
osheet.Range("A1").EntireColumn.AutoFit()
With osheet.Range("A1").Borders(XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With osheet.Range("A1").Borders(XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With osheet.Range("A1").Borders(XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With
With osheet.Range("A1").Borders(XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
End With

'format headings
osheet.Rows("1").RowHeight = 40
osheet.Columns("A").ColumnWidth = 35
osheet.Range("B:ALN").ColumnWidth = 14
osheet.Range("A1:AZ1").WrapText = True
osheet.Rows("4").Hidden = True
osheet.Rows("9").Hidden = True
osheet.Rows("12").Hidden = True
osheet.Rows("15").Hidden = True
osheet.Rows("20").Hidden = True
osheet.Rows("23").Hidden = True
osheet.Rows("26").Hidden = True
osheet.Rows("31").Hidden = True
osheet.Rows("34").Hidden = True
osheet.Rows("56").Hidden = True
osheet.Rows("59").Hidden = True
osheet.Rows("71").Hidden = True
osheet.Rows("77").Hidden = True
osheet.Rows("82").Hidden = True
osheet.Rows("85").Hidden = True
osheet.PageSetup.CenterHeader = "&C&""Segoe(UI),Bold""&16 Steve"
osheet.PageSetup.CenterFooter = "&C&""Segoe(UI),Bold""&12 Steve
Reporting© for Month of June 2007"
osheet.PageSetup.LeftMargin = 40
osheet.PageSetup.RightMargin = 0
osheet.PageSetup.PrintGridlines = True
With osheet.PageSetup.LeftHeaderPicture
.Filename = ImageFileName
.Height = 25.25
.Width = 100.5
End With
osheet.PageSetup.LeftHeader = "&G"
osheet.PageSetup.PrintTitleRows = ("$A$1")
osheet.PageSetup.PrintTitleColumns = ("$A:$A")
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape
'obook.osheet.HPageBreaks.Add("A72")
'osheet.HPageBreaks.Add(Before:=ActiveCell)

'Dim hpagebreaks As HPageBreaks = osheet.HPageBreaks
'hpagebreaks.Add("A72")

Thanks for all you help,
Steve



rowe_newsgroups said:
I can't believe this simple problem...

I'm very new to VB...

My code is very similar but my initial DIM is here.

Public oexcel As ApplicationClass = Nothing

oexcel = New ApplicationClass()

Here is my line in the program.

obook.osheet.HPageBreaks.Add(oexcel.Range("A72"))

Here is the exception.

System.MissingMemberException was unhandled
Message="Public member 'osheet' on type 'WorkbookClass' not found."
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[]
CopyBack)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at MeasureMonitorSpreadsheet.Module1.Main() in
C:\Users\SP\Documents\Visual Studio
2005\Projects\MeasureMonitorSpreadsheet\Module1.vb:line 565
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()


obook.osheet.HPageBreaks.Add(Excel.Range
("A72"))
Click to expand...
I get the blue line under Excel.Range
Click to expand...
Error 1 'Range' is a type in 'Excel' and cannot be used as an
expression.
Click to expand...
'osheet.HPageBreaks.Add.Range("A72")
.
Click to expand...
On Jul 9, 12:43 pm, "Stephen Plotnick" <[email protected]>
wrote:
Seth,
Click to expand...
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.
Click to expand...
On Jul 9, 12:24 pm, "Stephen Plotnick" <[email protected]>
wrote:
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.
Click to expand...
Thanks in advance.
Click to expand...
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.

Seth Rowe
Click to expand...
Post your code.

Seth Rowe
Click to expand...
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:
Click to expand...
Option Strict Off
Click to expand...
'// Of course you'll need to reference
'// Microsoft Excel Object Library 12.0 for this to work
Imports Microsoft.Office.Interop.Excel
Click to expand...
Module Module1
Click to expand...
Sub Main()
Dim excel As New Application()
Dim workbook As Workbook = excel.Workbooks.Add()
workbook.Worksheets(1).HPageBreaks.Add(excel.Range("A5"))
excel.Visible = True
End Sub
Click to expand...
End Module
Click to expand...
Hope That Helps!

Seth Rowe- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
You looked at my code wrong - I used the instance "excel" not the type
"Excel." You need to use the instance of Excel.Application that you
use to create the excel workbooks.

Seth Rowe
Click to expand...
Click to expand...

I don't see where you dimension obook or osheet. Could you please post
the entire code you use?

Thanks,

Seth Rowe
Click to expand...
 
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_Report".

Thanks,

Seth Rowe
 
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_Report".

Thanks,

Seth Rowe
Click to expand...

Okay, I got impatient so I'm going to post the conversion of the code
you posted earlier. I did more than fix your excel problems - I
removed the massive list of Public variables you declared - as they
break the rules of encapsulation and I did some general cleanup. The
reason I wanted the rest of your code is that I'm guessing you are
accessing some of the public variables I removed in the
Generate_Report method. If so, just pass the values as parameters to
that function.

If you need any more help please let me know.

/////////////

Option Strict Off

Imports Microsoft.Office.Interop.Excel
Imports System.IO

Module Module1

Sub Main()
Dim fileName As String = String.Format("{0}Steve.xlsx",
AppDomain.CurrentDomain.BaseDirectory)
Dim imageFileName As String =
String.Format("{0}GroupCBFLogoBetterWay.JPG",
AppDomain.CurrentDomain.BaseDirectory)
Dim pdfFileName As String = String.Format("{0}Steve.PDF",
AppDomain.CurrentDomain.BaseDirectory)

If File.Exists(fileName) Then File.Delete(fileName)

Dim excel As New Application()

Try
excel.DisplayAlerts = False
Dim workbook As Workbook =
excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)

While excel.Application.Sheets.Count > 1

excel.Application.Sheets(excel.Application.Sheets.Count).Delete()
End While

Console.WriteLine("Generating Steve Report")

Dim sheet As Worksheet = DirectCast(workbook.Sheets(1),
Worksheet)

sheet.Name = "Measure and Monitoring"

Try
Using reader As New StreamReader("workdept.txt")
Do
Dim line As String = reader.ReadLine()
Generate_Report() '// You'll want to pass any
required parameters here
If line Is Nothing Then Exit Do
Loop
End Using
Catch ex As Exception
Console.WriteLine("The file could not be read:")
Console.WriteLine(ex.Message)
End Try

sheet.Range("A2:AZ400").Font.Size = 8.5
sheet.Range("1:1").Font.Size = 8
sheet.Range("A1:AZ400").Font.Bold = True
sheet.Range("A1:AZ400").Font.Name = "Segoe(UI)"
sheet.Range("A1").Value = "Report for Steve"
sheet.Range("A1").EntireColumn.AutoFit()


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous

sheet.Range("A1").Borders(XlBordersIndex.xlEdgeBottom).Weight =
XlBorderWeight.xlThin


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous
sheet.Range("A1").Borders(XlBordersIndex.xlEdgeTop).Weight
= XlBorderWeight.xlThin


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous

sheet.Range("A1").Borders(XlBordersIndex.xlEdgeRight).Weight =
XlBorderWeight.xlThin


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous

sheet.Range("A1").Borders(XlBordersIndex.xlEdgeLeft).Weight =
XlBorderWeight.xlThin

sheet.Rows("1").RowHeight = 40
sheet.Columns("A").ColumnWidth = 35
sheet.Range("B:ALN").ColumnWidth = 14
sheet.Range("A1:AZ1").WrapText = True
sheet.Rows("4").Hidden = True
sheet.Rows("9").Hidden = True
sheet.Rows("12").Hidden = True
sheet.Rows("15").Hidden = True
sheet.Rows("20").Hidden = True
sheet.Rows("23").Hidden = True
sheet.Rows("26").Hidden = True
sheet.Rows("31").Hidden = True
sheet.Rows("34").Hidden = True
sheet.Rows("56").Hidden = True
sheet.Rows("59").Hidden = True
sheet.Rows("71").Hidden = True
sheet.Rows("77").Hidden = True
sheet.Rows("82").Hidden = True
sheet.Rows("85").Hidden = True
sheet.PageSetup.CenterHeader = "&C&""Segoe(UI),Bold""&16
Steve"
sheet.PageSetup.CenterFooter = "&C&""Segoe(UI),Bold""&12
Steve Reporting© for Month of June 2007"
sheet.PageSetup.LeftMargin = 40
sheet.PageSetup.RightMargin = 0
sheet.PageSetup.PrintGridlines = True
sheet.PageSetup.LeftHeaderPicture.Filename = imageFileName
sheet.PageSetup.LeftHeaderPicture.Height = 25.25
sheet.PageSetup.LeftHeaderPicture.Width = 100.5
sheet.PageSetup.LeftHeader = "&G"
sheet.PageSetup.PrintTitleRows = ("$A$1")
sheet.PageSetup.PrintTitleColumns = ("$A:$A")
sheet.PageSetup.Orientation =
XlPageOrientation.xlLandscape
sheet.HPageBreaks.Add(excel.Range("A72"))
Finally
'// Putting this here prevents the user from seeing Excel
flash on the screen
'// which also gives the process a significant performance
boost (no painting)
'// More importantly, this guarantees that Excel always is
shown - thus
'// preventing any memory leaks caused by hidden excel
sheets being created.
excel.Visible = True
End Try

End Sub

End Module

//////////////

Thanks,

Seth Rowe
 
Just got back from a meeting; thanks I'm going to try this.

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.

THanks again,
Steve
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_Report".

Thanks,

Seth Rowe
Click to expand...

Okay, I got impatient so I'm going to post the conversion of the code
you posted earlier. I did more than fix your excel problems - I
removed the massive list of Public variables you declared - as they
break the rules of encapsulation and I did some general cleanup. The
reason I wanted the rest of your code is that I'm guessing you are
accessing some of the public variables I removed in the
Generate_Report method. If so, just pass the values as parameters to
that function.

If you need any more help please let me know.

/////////////

Option Strict Off

Imports Microsoft.Office.Interop.Excel
Imports System.IO

Module Module1

Sub Main()
Dim fileName As String = String.Format("{0}Steve.xlsx",
AppDomain.CurrentDomain.BaseDirectory)
Dim imageFileName As String =
String.Format("{0}GroupCBFLogoBetterWay.JPG",
AppDomain.CurrentDomain.BaseDirectory)
Dim pdfFileName As String = String.Format("{0}Steve.PDF",
AppDomain.CurrentDomain.BaseDirectory)

If File.Exists(fileName) Then File.Delete(fileName)

Dim excel As New Application()

Try
excel.DisplayAlerts = False
Dim workbook As Workbook =
excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)

While excel.Application.Sheets.Count > 1

excel.Application.Sheets(excel.Application.Sheets.Count).Delete()
End While

Console.WriteLine("Generating Steve Report")

Dim sheet As Worksheet = DirectCast(workbook.Sheets(1),
Worksheet)

sheet.Name = "Measure and Monitoring"

Try
Using reader As New StreamReader("workdept.txt")
Do
Dim line As String = reader.ReadLine()
Generate_Report() '// You'll want to pass any
required parameters here
If line Is Nothing Then Exit Do
Loop
End Using
Catch ex As Exception
Console.WriteLine("The file could not be read:")
Console.WriteLine(ex.Message)
End Try

sheet.Range("A2:AZ400").Font.Size = 8.5
sheet.Range("1:1").Font.Size = 8
sheet.Range("A1:AZ400").Font.Bold = True
sheet.Range("A1:AZ400").Font.Name = "Segoe(UI)"
sheet.Range("A1").Value = "Report for Steve"
sheet.Range("A1").EntireColumn.AutoFit()


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeBottom).LineStyle =
XlLineStyle.xlContinuous

sheet.Range("A1").Borders(XlBordersIndex.xlEdgeBottom).Weight =
XlBorderWeight.xlThin


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeTop).LineStyle =
XlLineStyle.xlContinuous
sheet.Range("A1").Borders(XlBordersIndex.xlEdgeTop).Weight
= XlBorderWeight.xlThin


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeRight).LineStyle =
XlLineStyle.xlContinuous

sheet.Range("A1").Borders(XlBordersIndex.xlEdgeRight).Weight =
XlBorderWeight.xlThin


sheet.Range("A1").Borders(XlBordersIndex.xlEdgeLeft).LineStyle =
XlLineStyle.xlContinuous

sheet.Range("A1").Borders(XlBordersIndex.xlEdgeLeft).Weight =
XlBorderWeight.xlThin

sheet.Rows("1").RowHeight = 40
sheet.Columns("A").ColumnWidth = 35
sheet.Range("B:ALN").ColumnWidth = 14
sheet.Range("A1:AZ1").WrapText = True
sheet.Rows("4").Hidden = True
sheet.Rows("9").Hidden = True
sheet.Rows("12").Hidden = True
sheet.Rows("15").Hidden = True
sheet.Rows("20").Hidden = True
sheet.Rows("23").Hidden = True
sheet.Rows("26").Hidden = True
sheet.Rows("31").Hidden = True
sheet.Rows("34").Hidden = True
sheet.Rows("56").Hidden = True
sheet.Rows("59").Hidden = True
sheet.Rows("71").Hidden = True
sheet.Rows("77").Hidden = True
sheet.Rows("82").Hidden = True
sheet.Rows("85").Hidden = True
sheet.PageSetup.CenterHeader = "&C&""Segoe(UI),Bold""&16
Steve"
sheet.PageSetup.CenterFooter = "&C&""Segoe(UI),Bold""&12
Steve Reporting© for Month of June 2007"
sheet.PageSetup.LeftMargin = 40
sheet.PageSetup.RightMargin = 0
sheet.PageSetup.PrintGridlines = True
sheet.PageSetup.LeftHeaderPicture.Filename = imageFileName
sheet.PageSetup.LeftHeaderPicture.Height = 25.25
sheet.PageSetup.LeftHeaderPicture.Width = 100.5
sheet.PageSetup.LeftHeader = "&G"
sheet.PageSetup.PrintTitleRows = ("$A$1")
sheet.PageSetup.PrintTitleColumns = ("$A:$A")
sheet.PageSetup.Orientation =
XlPageOrientation.xlLandscape
sheet.HPageBreaks.Add(excel.Range("A72"))
Finally
'// Putting this here prevents the user from seeing Excel
flash on the screen
'// which also gives the process a significant performance
boost (no painting)
'// More importantly, this guarantees that Excel always is
shown - thus
'// preventing any memory leaks caused by hidden excel
sheets being created.
excel.Visible = True
End Try

End Sub

End Module

//////////////

Thanks,

Seth Rowe
 
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.
Click to expand...

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
 
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
 
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,




You mention a possible memory leak. Is
that a normal concern here?
Click to expand...

Any COM interop project concerns me, as it isn't handled by the
framework's garbage collector. But here my main concern was just
having invisible copies of excel running if the project errored out.

Consider this code:

Sub Main()
Dim excel as new Excel.Application()
Throw New Exception()
End Sub

This would create an excel application and then error out and quit.
The problem is that is you look at the taskmanager you will have a
"excel.exe" running. In a live environment the user might think "maybe
I did something wrong" and run the app again - boom another copy
running. After 12 times the user calls IT complaining of massive
slowdowns, then the IT guy comes after you :-)
I'm going to start to move the public variables. Is there a reason not to
keep them there? It seems so much easier:)
Click to expand...

While there is nothing "illegal" about using public variables, it can
be dangerous. One of the foundations of OOP (object orientated
programming) is that of encapsulation. Encapsulation is basically the
idea that an object should be self sufficient and not require or
modify any outside objects - any thing the object needs should be
given it's own copy. This concept prevents problems (particularly in
multithreaded solutions) where variable are modified in unexpected
ways. So while it might not be necessary here, it can/will be a huge
time saver it other projects as these types of errors are some of the
most difficult to find and fix. Also, after you get used to
encapsulated programming, it will be much easier than using and
maintaining the public variables.

Thanks,

Seth Rowe
 
Once again thanks; not only for the help but the lesson.

Like I said I've been programming COBOL forever; actaully since 1981. I've
self taught myself VB over the last couple of months and feel I've gotten to
the level of 3 out of 10.

In my way of thinking, old school, I declare all variables and only need to
pass them when calling another program.. within the same program there is no
need to pass them. Time to change and the change is fun!

Thanks for all you help and lesson learned.

Steve
 
Back
Top