troubleshooting Question

Inserting an Excel page break from VB6

Avatar of Norma Posy
Norma PosyFlag for United States of America asked on
Visual Basic ClassicMicrosoft Excel
7 Comments2 Solutions1208 ViewsLast Modified:
I've been struggling with inserting a page break into an Excel spreadsheet generated by VB6 code.
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

Open in new window


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

Open in new window


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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros