Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pagebreaks problems - Urgent
Hi,
I have an Excel application that creates a visual design using images. As an overview the application paste images into the design sheet moving down any existings images. I find out where the next pagebreak will be to locate the image before or after a pagebreak to avoid splitting the image in the preview or when printing. When the "design" is finished it is exported to another workbook (Saved as). All the pageSetup settings are set on the new workbook's sheet that will hold the design on. In the exporting application everything works find, it finds the pagebreaks and locate the images just right. The preview or printing never splits any images. The problem: The exported sheet has different pagebreaks even they have same pageSetup properties. This means if a design is larger than one page, the images are splitted or wrongly located. You can find the code of the exportDesign sub below. I will appreciate any ideas. Carlos Public Sub ExportDesign() Application.ScreenUpdating = False Dim oExpSheet As Worksheet Dim oWorkBook As Workbook Dim oSheet As Worksheet Dim oObj As Object Set oSheet = Application.Sheets("Design") Application.ScreenUpdating = False CurWorkBook = ThisWorkbook.Name sFileFormat = ThisWorkbook.FileFormat 'Do sFileName = Application.GetSaveAsFilename(FileFilter:="Excel files (*.XLS),*.Xls") 'Loop Until sFileName < False If sFileName = sAppName Then MsgBox "You can not save the exporting file using current application's name, process canceled" Exit Sub End If If sFileName = False Then MsgBox "'Save as' process has been canceled" Exit Sub End If Set oWorkBook = Workbooks.Add If Dir(sFileName) < "" Then Set oFS = CreateObject("Scripting.FileSystemObject") oFS.DeleteFile sFileName, True End If oWorkBook.SaveAs Filename:=sFileName NewWorkbook = oWorkBook.Name Application.Workbooks(CurWorkBook).Activate oSheet.Select nPages = findPages() nheight = ActiveCell.Height Columns("A:V").Select Selection.Copy Application.Workbooks(NewWorkbook).Activate For Each oExpSheet In ActiveWorkbook.Sheets If oExpSheet.Name = "Sheet1" Then oExpSheet.Name = "Design Project" End If Next Set oExpSheet = ActiveWorkbook.Sheets("Design Project") oExpSheet.Select Columns("A:V").Select oExpSheet.Paste Range("A1", "A600").RowHeight = nheight Range("A7").RowHeight = 21 ' Saves last row nCol = Range("V1").Column oExpSheet.Cells(1, nCol).Value = nPageFormat oExpSheet.Cells(2, nCol).Value = nPages oExpSheet.Cells(1, 1).Select ActiveWindow.View = xlPageBreakPreview oExpSheet.DisplayPageBreaks = True With oExpSheet.PageSetup '.PrintArea = "$C$3:$T$" & Trim(Str(aPageRange(nPages - 1, 2) + 5)) .PrintArea = oSheet.PageSetup.PrintArea .Orientation = oSheet.PageSetup.Orientation .PaperSize = oSheet.PageSetup.PaperSize .FitToPagesWide = 1 .FitToPagesTall = nPages .LeftMargin = oSheet.PageSetup.LeftMargin .RightMargin = oSheet.PageSetup.RightMargin .TopMargin = oSheet.PageSetup.TopMargin .BottomMargin = oSheet.PageSetup.BottomMargin .Zoom = oSheet.PageSetup.Zoom .PrintGridlines = False End With oExpSheet.Cells(3, nCol).Value = aPageRange(nPages - 1, 2) + 5 lFreezePanes = ActiveWindow.FreezePanes ActiveWindow.FreezePanes = False With Application .CutCopyMode = False .Workbooks(NewWorkbook).Save .Workbooks(NewWorkbook).Close .Workbooks(CurWorkBook).Activate .ActiveSheet.Cells(1, 1).Select .ScreenUpdating = True End With MsgBox "The exported design file has been saved to '" & sFileName ActiveWindow.FreezePanes = lFreezePanes End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pagebreaks problems - Urgent
Carlos,
Instead of copying and pasting the range, why not just copy the entire sheet as is, and not worry about the formatting... oSheet.Copy Befo=Workbooks(NewWorkBook).Worksheets(1) Regards, Jim Cone San Francisco, USA "Carlos Lozano" wrote in message ... Hi, I have an Excel application that creates a visual design using images. As an overview the application paste images into the design sheet moving down any existings images. I find out where the next pagebreak will be to locate the image before or after a pagebreak to avoid splitting the image in the preview or when printing. When the "design" is finished it is exported to another workbook (Saved as). All the pageSetup settings are set on the new workbook's sheet that will hold the design on. In the exporting application everything works find, it finds the pagebreaks and locate the images just right. The preview or printing never splits any images. The problem: The exported sheet has different pagebreaks even they have same pageSetup properties. This means if a design is larger than one page, the images are splitted or wrongly located. You can find the code of the exportDesign sub below. I will appreciate any ideas. Carlos -snip- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pagebreaks problems - Urgent
Hi Jim,
Thank you for your feedback. It would have been neat if I could replace several lines of code with only one. Unfortunately oSheet.Copy copies the whole oSheet object including any code. The oSheet object has code that depends on a class defined in the current workbook (the application). That class won't be in the new workbook, so it fails on activation. I found the problem for the mismatch of the pagebreaks. The original sheet has a row size of 15.75, when assigned to the new exported sheet it takes only the integer part, changing the location of the pagebreaks. I was doing the following: nHeight = oSheet.Cells(nMinRow, 1).RowHeight .. .. Set oExpSheet = ActiveWorkbook.Sheets("Design Project") oExpSheet.Select Columns("A:V").Select oExpSheet.Paste oExpSheet.Range("A1", "A800").RowHeight = nHeight oExpSheet.Range("A7").RowHeight = 21 .. .. The value in nHeight is the row height = 15.75, but when assigned to the range on oExpSheet it truncates it to only 15. I also tried the below direct assignment. oExpSheet.Range("A1", "A800").RowHeight = oSheet.Cells(nMinRow, 1).RowHeight The only way it works is by hard-coding the row height directly as: oExpSheet.Range("A1", "A800").RowHeight = 15.75 This will give problems if the user changes the row size. Thank you, Carlos Lozano "Jim Cone" wrote: Carlos, Instead of copying and pasting the range, why not just copy the entire sheet as is, and not worry about the formatting... oSheet.Copy Befo=Workbooks(NewWorkBook).Worksheets(1) Regards, Jim Cone San Francisco, USA "Carlos Lozano" wrote in message ... Hi, I have an Excel application that creates a visual design using images. As an overview the application paste images into the design sheet moving down any existings images. I find out where the next pagebreak will be to locate the image before or after a pagebreak to avoid splitting the image in the preview or when printing. When the "design" is finished it is exported to another workbook (Saved as). All the pageSetup settings are set on the new workbook's sheet that will hold the design on. In the exporting application everything works find, it finds the pagebreaks and locate the images just right. The preview or printing never splits any images. The problem: The exported sheet has different pagebreaks even they have same pageSetup properties. This means if a design is larger than one page, the images are splitted or wrongly located. You can find the code of the exportDesign sub below. I will appreciate any ideas. Carlos -snip- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pagebreaks problems - Urgent
Carlos,
How do you have nHeight declared? Have you tried it with it declared as a Single or Double? Regards, Jim Cone "Carlos Lozano" wrote in message ... Hi Jim, Thank you for your feedback. It would have been neat if I could replace several lines of code with only one. Unfortunately oSheet.Copy copies the whole oSheet object including any code. The oSheet object has code that depends on a class defined in the current workbook (the application). That class won't be in the new workbook, so it fails on activation. I found the problem for the mismatch of the pagebreaks. The original sheet has a row size of 15.75, when assigned to the new exported sheet it takes only the integer part, changing the location of the pagebreaks. I was doing the following: nHeight = oSheet.Cells(nMinRow, 1).RowHeight . . Set oExpSheet = ActiveWorkbook.Sheets("Design Project") oExpSheet.Select Columns("A:V").Select oExpSheet.Paste oExpSheet.Range("A1", "A800").RowHeight = nHeight oExpSheet.Range("A7").RowHeight = 21 . . The value in nHeight is the row height = 15.75, but when assigned to the range on oExpSheet it truncates it to only 15. I also tried the below direct assignment. oExpSheet.Range("A1", "A800").RowHeight = oSheet.Cells(nMinRow, 1).RowHeight The only way it works is by hard-coding the row height directly as: oExpSheet.Range("A1", "A800").RowHeight = 15.75 This will give problems if the user changes the row size. Thank you, Carlos Lozano "Jim Cone" wrote: Carlos, Instead of copying and pasting the range, why not just copy the entire sheet as is, and not worry about the formatting... oSheet.Copy Befo=Workbooks(NewWorkBook).Worksheets(1) Regards, Jim Cone San Francisco, USA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pagebreaks problems - Urgent
Jim,
The original code had no declaration for nHeight. I thought about it and declared it as double, but nothing changed. I also tried using a direct assignment between both sheets to avoid using variables with same results. Thanks, Carlos Lozano "Jim Cone" wrote: Carlos, How do you have nHeight declared? Have you tried it with it declared as a Single or Double? Regards, Jim Cone "Carlos Lozano" wrote in message ... Hi Jim, Thank you for your feedback. It would have been neat if I could replace several lines of code with only one. Unfortunately oSheet.Copy copies the whole oSheet object including any code. The oSheet object has code that depends on a class defined in the current workbook (the application). That class won't be in the new workbook, so it fails on activation. I found the problem for the mismatch of the pagebreaks. The original sheet has a row size of 15.75, when assigned to the new exported sheet it takes only the integer part, changing the location of the pagebreaks. I was doing the following: nHeight = oSheet.Cells(nMinRow, 1).RowHeight . . Set oExpSheet = ActiveWorkbook.Sheets("Design Project") oExpSheet.Select Columns("A:V").Select oExpSheet.Paste oExpSheet.Range("A1", "A800").RowHeight = nHeight oExpSheet.Range("A7").RowHeight = 21 . . The value in nHeight is the row height = 15.75, but when assigned to the range on oExpSheet it truncates it to only 15. I also tried the below direct assignment. oExpSheet.Range("A1", "A800").RowHeight = oSheet.Cells(nMinRow, 1).RowHeight The only way it works is by hard-coding the row height directly as: oExpSheet.Range("A1", "A800").RowHeight = 15.75 This will give problems if the user changes the row size. Thank you, Carlos Lozano "Jim Cone" wrote: Carlos, Instead of copying and pasting the range, why not just copy the entire sheet as is, and not worry about the formatting... oSheet.Copy Befo=Workbooks(NewWorkBook).Worksheets(1) Regards, Jim Cone San Francisco, USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
URGENT- Form design and field tab-order problems | Excel Discussion (Misc queries) | |||
urgent ......problems on group | Excel Worksheet Functions | |||
List Box Problems - Urgent!! | Excel Programming | |||
pagebreaks | Excel Programming | |||
Excel pagebreaks | Excel Programming |