Link to home
Avatar of Norma Posy
Norma PosyFlag for United States of America

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
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
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
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
Avatar of 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
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.
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 ???
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!