
Inserting an Excel page break from VB6
I've been struggling with inserting a page break into an Excel spreadsheet generated by VB6 code.
This little test case works:
Here is a skeleton of the code in my main application:
The intended spreadsheet is generated ok, but the page breaks do not appear.
But a second spreadsheet is generated named "Book1"
I've stepped through to be sure that [condition] is in fact detected
What am I doing wrong?
Please help
This little test case works:
Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
With oXLApp
'
' some code that populates the spreadsheet
'
oXLSheet.HPagebreaks.Add Before:=oXLSheet.Range("A33")
End With
Here is a skeleton of the code in my main application:
Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
sWorkbookName = "Seeding.xls"
With oXLApp
.Visible = True
sCurrentName = .Workbooks.Add.Name
'
' Looping over rows
' populate the spreadsheet row by row.
'
If [condition] Is True Then
iRow = iRow + 1 ' inserts a blank row
sRow = Trim(CStr(iRow))
sRange = "A" & sRow
oXLSheet.HPagebreaks.Add Before:=oXLSheet.Range(sRange)
End If
'
' End the looping
'
.Application.Workbooks(sCurrentName).SaveAs (sSavePath & sWorkbookName)
End With
The intended spreadsheet is generated ok, but the page breaks do not appear.
But a second spreadsheet is generated named "Book1"
I've stepped through to be sure that [condition] is in fact detected
What am I doing wrong?
Please help
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Oh ya! I missed that!
Rory didn't post code so I will. But he caught it, not me.
Set oXLApp = CreateObject("Excel.Applic ation")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
sWorkbookName = "Seeding.xls"
With oXLApp
.Visible = True
'
' Looping over rows
' populate the spreadsheet row by row.
'
If [condition] Is True Then
iRow = iRow + 1 ' inserts a blank row
sRow = Trim(CStr(iRow))
sRange = "A" & sRow
oXLSheet.HPagebreaks.Add Before:=oXLSheet.Range(sRa nge)
End If
'
' End the looping
'
oXLBook.SaveAs (sSavePath & sWorkbookName)
End With
Kevin
Rory didn't post code so I will. But he caught it, not me.
Set oXLApp = CreateObject("Excel.Applic
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
sWorkbookName = "Seeding.xls"
With oXLApp
.Visible = True
'
' Looping over rows
' populate the spreadsheet row by row.
'
If [condition] Is True Then
iRow = iRow + 1 ' inserts a blank row
sRow = Trim(CStr(iRow))
sRange = "A" & sRow
oXLSheet.HPagebreaks.Add Before:=oXLSheet.Range(sRa
End If
'
' End the looping
'
oXLBook.SaveAs (sSavePath & sWorkbookName)
End With
Kevin

ASKER
OK, here is what happens:
(referring to the code given by zorvek)
An excel spreadsheet is generated with the name "Seeding.xls". It is blank, but seems to have the page breaks.
A second spreadsheet is generated with the name "Book2". It contains the computed data, but no page breaks.
(referring to the code given by zorvek)
An excel spreadsheet is generated with the name "Seeding.xls". It is blank, but seems to have the page breaks.
A second spreadsheet is generated with the name "Book2". It contains the computed data, but no page breaks.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER
Code fragment:
Your first question:
All data insertion is done between "With oXLApp" and "End With", as shown above.
Second question:
Line 7 ???
Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
sWorkbookName = "Seeding.xls"
With oXLApp
.Visible = True
sCurrentName = .Workbooks.Add.Name
iRow = 3
For iIndex = 1 To iNseeds
iRow = iRow + 1
sRow = Trim(CStr(iRow))
sCellNumber = "A" & sRow
.Range(sCellNumber).Value = strSeeding(iIndex, 1)
sCellNumber = "B" & sRow
.Range(sCellNumber).Value = strSeeding(iIndex, 2)
' and so on
Your first question:
All data insertion is done between "With oXLApp" and "End With", as shown above.
Second question:
Line 7 ???

ASKER
AHA!
Your second question nailed it!
Explanation: I had originally needed the "current name" because of the way I was auto-naming and auto-saving the spreadsheet
.Application.Workbooks(sCu rrentName) .SaveAs (sSavePath & sWorkbookName)
Using your oXLBook.SaveAs has no need to know the current name.
I commented line 7 out (code, above) and everything works!
Thank you!
And thank you to rorya too!
Your second question nailed it!
Explanation: I had originally needed the "current name" because of the way I was auto-naming and auto-saving the spreadsheet
.Application.Workbooks(sCu
Using your oXLBook.SaveAs has no need to know the current name.
I commented line 7 out (code, above) and everything works!
Thank you!
And thank you to rorya too!
Kevin