asked on

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:

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

zorvek (Kevin Jones)

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

Kevin

SOLUTION

Rory Archibald

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.

See Pricing Options

Start Free Trial

zorvek (Kevin Jones)

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

Norma Posy

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.

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

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.

See Pricing Options

Start Free Trial

Norma Posy

ASKER

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 Posy

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(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!