Inserting an Excel page break from VB6

Norma Posy
Norma Posy used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
The code looks OK. Are you sure the page breaks are NOT added? Did you check on "Page breaks" in the Options dialog?

Kevin
Most Valuable Expert 2011
Top Expert 2011
Commented:
Your code specifically adds a new workbook and then assigns its first sheet to oXLSheet, then you create another workbook which you save, but you are adding pagebreaks to that first sheet and not to the second workbook.
Top Expert 2008

Commented:
Oh ya! I missed that!

Rory didn't post code so I will. But he caught it, not me.

Set oXLApp = CreateObject("Excel.Application")
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(sRange)
   End If
'
' End the looping
'
    oXLBook.SaveAs (sSavePath & sWorkbookName)
End With

Kevin
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Norma PosyPhysicist

Author

Commented:
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.

Top Expert 2008
Commented:
Are you using oXLSheet to access the worksheet when placing values in it?

Do you have any oXLApp.Workbooks.Add statements other than this line?

   Set oXLBook = oXLApp.Workbooks.Add

Kevin
Norma PosyPhysicist

Author

Commented:
Code fragment:

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

Open in new window


Your first question:
All data insertion is done between "With oXLApp" and "End With", as shown above.

Second question:
Line 7 ???
Norma PosyPhysicist

Author

Commented:
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(sCurrentName).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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial