Announcement

Collapse
No announcement yet.

Confused about PageSetup.Zoom

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Confused about PageSetup.Zoom

    I am having trouble setting the "Fit to" option in the Scaling section of Page Setup in Print Preview of Excel.

    My best guess is that I need to use PageSetup.Zoom. The following compiles but throws a object error when run.

    This is my first attempt working with Excel, so suggestions about anything might help me along.

    Frank
    Code:
    #Compiler PBWin
    #Compile Exe "ExcelZoom.Exe"
    #Dim All
    
    #Include "excel.Inc"
    
    Function PBMain
      Local oExcelApp       As Excel_Application
      Local oExcelWorkbook  As Excel_Workbook
      Local oExcelWorksheet As Excel_Worksheet
      Local vItem, vCol, vRow, vRange, vFormat, vTrue, vFalse, vFileFormat, vFileName As Variant
      Local looper As Long
    
      vTrue = 1
      vFalse = 0
      vFileFormat = %xlFileFormat.xlWorkbookNormal ' "Excel 97-2003 Workbook"
      vFileName = "ExcelZoom.XLS"
    
      oExcelApp = AnyCom $PROGID_Excel_Application
      If IsFalse IsObject(oExcelApp) Or Err Then
        ? "Excel could not be opened."
        Exit Function
      End If
    
      'Object Let oExcelApp.Visible = vTrue
      Object Let oExcelApp.DisplayAlerts = vTrue
    
      Object Call oExcelApp.Workbooks.Add To oExcelWorkbook
      If IsFalse IsObject(oExcelWorkbook) Or Err Then
        ? "Excel could not open workbook."
        GoTo Terminate
      End If
    
    ' Select the first sheet
      vItem = 1
      Object Call oExcelWorkBook.WorkSheets(vItem).SELECT
      Object Get oExcelWorkbook.Activesheet To vItem
      oExcelWorkSheet = vItem
      If IsFalse IsObject(oExcelWorksheet) Or Err Then
        ? "Excel could not open worksheet."
        GoTo Terminate
      End If
    
      vRow = 1
      vItem = "Column 1"
      vCol = 1
      Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
      vItem = "Column 2"
      vCol = 2
      Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
      vItem = "Column 3"
      vCol = 3
      Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
      vItem = "Column 4"
      vCol = 4
      Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
      vItem = "Column 5"
      vCol = 5
      Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
      vItem = "Column 6"
      vCol = 6
      Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
    
      vRange = "A:F"
      vFormat = "@"
      Object Let oExcelWorkSheet.Range(vRange).NumberFormat = vFormat
    
      For Looper = 1 To 300
        vItem = "Something to Say"
        vRow = Looper + 1
        vCol = 1
        Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
        vItem = "A space filler"
        vCol = 2
        Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
        vItem = "Take up more room"
        vCol = 3
        Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
        vItem = "This is maybe enough"
        vCol = 4
        Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
        vItem = "Just a bit more"
        vCol = 5
        Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
        vItem = "Now Im done"
        vCol = 6
        Object Let oExcelWorksheet.Cells.Item(vRow, vCol) = vItem
      Next Looper
    
      ' Set the font and size
      Let vRange = "A:F"
      Let vItem = "Consolas"
      Object Let oExcelWorksheet.Range(vRange).Font.Name = vItem
      Let vItem = 9
      Object Let oExcelWorksheet.Range(vRange).Font.Size = vItem
    
      ' Center the Titles
      Let vRange = "1:1"
      Let vItem = %xlHAlign.xlHAlignCenter
      Object Let oExcelWorksheet.Range(vRange).HorizontalAlignment = vItem
    
      ' Center the "Column 3" and "Column 4" columns
      Let vRange = "C:D"
      Let vItem = %xlHAlign.xlHAlignCenter
      Object Let oExcelWorksheet.Range(vRange).HorizontalAlignment = vItem
    
      ' Autofit the data
      Let vRange = "A:F"
      Object Call oExcelWorksheet.Columns(vRange).AutoFit
    
      ' Freeze the header row
      vRange = "A2:A2"
      Object Call oExcelWorkSheet.Range(vRange).Select
      Object Let oExcelApp.ActiveWindow.FreezePanes = vTrue
    
      ' color the titles
      vRange = "A1:F1"
      vItem = %Constants.xlSolid
      Object Let oExcelWorkSheet.Range(vRange).Interior.Pattern = vItem
      vItem = %Constants.xlAutomatic
      Object Let oExcelWorkSheet.Range(vRange).Interior.PatternColorIndex = vItem
      vItem = 65535
      Object Let oExcelWorkSheet.Range(vRange).Interior.Color = vItem
    
      ' run a border around
      vRange = "A1:F" + Dec$(Looper)
      vItem = %xlLineStyle.xlContinuous
      vFormat = %xlBordersIndex.xlEdgeLeft
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).LineStyle = vItem
      vItem = %Constants.xlAutomatic
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).ColorIndex = vItem
      vItem = %xlBorderWeight.xlThin
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).Weight = vItem
    
      vItem = %xlLineStyle.xlContinuous
      vFormat = %xlBordersIndex.xlEdgeTop
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).LineStyle = vItem
      vItem = %Constants.xlAutomatic
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).ColorIndex = vItem
      vItem = %xlBorderWeight.xlThin
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).Weight = vItem
    
      vItem = %xlLineStyle.xlContinuous
      vFormat = %xlBordersIndex.xlEdgeBottom
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).LineStyle = vItem
      vItem = %Constants.xlAutomatic
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).ColorIndex = vItem
      vItem = %xlBorderWeight.xlThin
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).Weight = vItem
    
      vItem = %xlLineStyle.xlContinuous
      vFormat = %xlBordersIndex.xlEdgeRight
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).LineStyle = vItem
      vItem = %Constants.xlAutomatic
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).ColorIndex = vItem
      vItem = %xlBorderWeight.xlThin
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).Weight = vItem
    
      vItem = %xlLineStyle.xlContinuous
      vFormat = %xlBordersIndex.xlInsideVertical
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).LineStyle = vItem
      vItem = %Constants.xlAutomatic
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).ColorIndex = vItem
      vItem = %xlBorderWeight.xlThin
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).Weight = vItem
    
      vItem = %xlLineStyle.xlContinuous
      vFormat = %xlBordersIndex.xlInsideHorizontal
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).LineStyle = vItem
      vItem = %Constants.xlAutomatic
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).ColorIndex = vItem
      vItem = %xlBorderWeight.xlThin
      Object Let oExcelWorksheet.Range(vRange).Borders(vFormat).Weight = vItem
    
      ' set up the printed preview
      Object Let oExcelWorksheet.PageSetup.CenterHorizontally = vTrue
      Object Let oExcelWorksheet.PageSetup.CenterVertically = vTrue
    
      vItem = 1
      Object Let oExcelWorksheet.PageSetup.FitToPagesWide = vItem
      vItem = (300/52) + 1
      Object Let oExcelWorksheet.PageSetup.FitToPagesTall = vItem
    
    
    
      Object Let oExcelWorksheet.PageSetup.Zoom = vFalse
    
    ? Hex$(ObjResult)
    ?    "Code:"+Hex$(IDispInfo.Code)+   $CrLf+_
      "Context:"+Hex$(IDispInfo.Context)+$CrLf+_
         "Desc:"+     IDispInfo.Desc$+   $CrLf+_
        "Help$:"+     IDispInfo.Help$+   $CrLf+_
        "Param:"+Hex$(IDispInfo.Param)+  $CrLf+_
      "Source$:"+     IDispInfo.Source$
    
    
      ' save the document
      Object Call oExcelWorkbook.SaveAs(vFileName, vFileFormat)
    
    Terminate:
      Object Call oExcelApp.ActiveWindow.Close
      Object Call oExcelApp.Quit
    
      Set oExcelApp =       Nothing
      Set oExcelWorkbook =  Nothing
      Set oExcelWorksheet = Nothing
    End Function

  • #2
    Frank, this is how I create the "false" variable Excel expects. Try to replace

    Code:
    vFalse = 0
    with
    Code:
    LET vFalse=0 AS LONG
    POKE$ VARPTR(vFalse),MKWRD$(%VT_BOOL)
    Note that I used the excel.inc file provided with the samples of PbWin 9 to make your code work. I am using 10.04 and the include still works unchanged.
    Julien Tosoni - Goodyear Dunlop Tires France

    Comment


    • #3
      Note that I used the excel.inc file provided with the samples of PbWin 9 to make your code work. I am using 10.04 and the include still works unchanged.
      I believe you should also consider the Excel version. Changes (mostly additions) in new versions of Excel will not be accounted for in the excel.inc that came with PBWin 10. With the COM Browser a new include for the version of Excel on your PC can be made.
      Dale

      Comment


      • #4
        For the sake of precision, I indicated the file version I had to use, as the one I use daily is much more recent (generated with the latest COM Browser for my Excel 2010) and throws errors at compile time.
        Last edited by Julien Tosoni; 10 Mar 2018, 08:32 AM. Reason: added: "daily"
        Julien Tosoni - Goodyear Dunlop Tires France

        Comment


        • #5
          Originally posted by Julien Tosoni View Post
          Frank, this is how I create the "false" variable Excel expects. Try to replace

          Code:
          vFalse = 0
          with
          Code:
          LET vFalse=0 AS LONG
          POKE$ VARPTR(vFalse),MKWRD$(%VT_BOOL)
          Note that I used the excel.inc file provided with the samples of PbWin 9 to make your code work. I am using 10.04 and the include still works unchanged.
          "For the sake of precision,..." You did not mention the MS Excel version at all! Only where you got the excel.inc file.

          The excel.inc file that comes with PBWin version 10.04 says it is for Excel version 9. Excel 2016 is version 16.

          I did not say anything was wrong with your post 2. I added to be aware of MS Excel version as well. BTW, the excel.inc with PBWin 9 is also for Excel 9; that explains why either will work.

          Thanks for helping Frank. I was not picking on you.
          Dale

          Comment


          • #6
            Originally posted by Dale Yarker View Post
            "For the sake of precision,..." You did not mention the MS Excel version at all! Only where you got the excel.inc file.
            But is may be relevant that his code includes:

            vFileFormat = %xlFileFormat.xlWorkbookNormal ' "Excel 97-2003 Workbook"
            vFileName = "ExcelZoom.XLS"

            Comment


            • #7
              Yes, Frank's code contains that. Not Juliens comments in post 2. Then I made a more generic suggestion that the Excel version may have an effect. In Julien's post 4, (which I was responding to) he mentioned having problems with COM Browser include with Excel 2010. I suppose it would with Frank's Excel 97 (V8) intended code.

              Why are you jumping? Julien jumped in his post 4, and I explained he didn't need to in my post 5.

              And, Frank hasn't replied to anything yet. Is his Excel that old, or does that line you mentioned need changing?

              Cheers,
              Dale

              Comment


              • #8
                Originally posted by Julien Tosoni View Post
                Frank, this is how I create the "false" variable Excel expects. Try to replace

                Code:
                vFalse = 0
                with
                Code:
                LET vFalse=0 AS LONG
                POKE$ VARPTR(vFalse),MKWRD$(%VT_BOOL)
                Note that I used the excel.inc file provided with the samples of PbWin 9 to make your code work. I am using 10.04 and the include still works unchanged.
                Thank you Julien. Your suggestion did fix the problem.
                My thinking is that vFalse will never change for the life of the program so does it make sense to start it off as a variant, then a long and finally as a bool? Is this the only way to set up a bool that Excel is happy with?

                Yes, I was using the Excel.inc that came with 10.04. When I could not get it to work, I tried making a newer version 12 include with PBrow and also Jose' TypeLib. Neither of those attempts made any progress on the problem.

                The reason I stuck with Excel 97-2003 is because it is the version of Excel the President of my company prefers. The layout of the colors and other things holds him back from upgrading.

                Frank

                Comment


                • #9
                  The reason I stuck with Excel 97-2003 is because ...
                  Nothing wrong with that! It' was just that such a difference in age of Excel and the include file could have been a problem. It isn't, so cheers ....
                  Dale

                  Comment


                  • #10
                    Originally posted by Frank Rogers View Post

                    My thinking is that vFalse will never change for the life of the program so does it make sense to start it off as a variant, then a long and finally as a bool? Is this the only way to set up a bool that Excel is happy with?
                    Frank
                    Frank,
                    Yep, vFalse won't change until you decide it should.

                    I had to use this trick because Excel expects vFalse to be a boolean variant, with a value of "false".As variants can be of almost any data type, the variant type has to be set to the appropriate type, which is boolean here. Unfortunately, PbWin (up to 10.04) does not allow boolean data types for variants in a LET statement. The only solution I found long ago was to overwrite the variant data type to boolean instead of the original long (as per the LET statement. Note that I could have used another integer type but that day I felt lazy to try all of them ). Hence the POKE$ into the location of the variant data type. The data value of this variant is 0 as per the LET statement so it is the expected value of "false".

                    The LOCAL vFalse AS VARIANT, LET vFalse=0 AS LONG and POKE$ are all essential for this boolean variant creation...

                    Maybe a future version of PbWin will allow boolean data types in LET statements? Until then, I see no other solution.

                    I hope this helps you understand the rationale.
                    Julien Tosoni - Goodyear Dunlop Tires France

                    Comment


                    • #11
                      Crystal clear Julien. Thank you for the explanation.


                      Frank

                      Comment

                      Working...
                      X