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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The code looks OK. Are you sure the page breaks are NOT added? Did you check on "Page breaks" in the Options dialog?
Kevin
Kevin
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.
Oh ya! I missed that!
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
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
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.
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
Do you have any oXLApp.Workbooks.Add statements other than this line?
Set oXLBook = oXLApp.Workbooks.Add
Kevin
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 ???
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
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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial