No announcement yet.

Excel Ver 12 () oExcelApp.Visible

  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Ver 12 () oExcelApp.Visible

    Finally, someone in our office updated their Excel to 2010 (Version 16).

    I've written a few app to create Excel reports. We don't want Excel visible while these files are being created, so I use the following:
    vBool = %FALSE
    OBJECT LET oExcelApp.Visible = vBool
    This does not work in Excel version 16. I tried this
    oVnt = %xlMinimized
    OBJECT LET oExcelApp.WindowState = oVnt
    and it doesn't work either.

    Everything else is working fine. I've requested an update to my Office collection so I can do some further research. But it may take a while.

    I've included example code. It is a slightly modified version of the sample program that comes with PB: ExcelAp1.bas

    This example was compiled in PBWIN 10. The issue happens in both PB10 and PBCC 6. I use José's includes.

    '  Slightly modified version of ExcelAp1.bas
    '  Changes noted with <<< coments >>>
    '  The code:
    '       vBool = %FALSE
    '       OBJECT LET oExcelApp.Visible = vBool
    '  is not working in Excel version 16.
    #COMPILE EXE  "AAA-test.exe"
    #DIM ALL
    #INCLUDE ""
    ' Main application entry point...
      ' Object Variables
      DIM oExcelApp       AS Excel_Application
      DIM oExcelWorkbook  AS Excel_Workbook
      DIM oExcelWorkSheet AS Excel_WorkSheet
      DIM oExcelChart     AS Excel_Chart
      ' General Object param variables
      DIM vBool           AS VARIANT
      DIM vVnt            AS VARIANT
      DIM oVnt            AS VARIANT
      DIM vText           AS VARIANT
      DIM vRange          AS VARIANT
      DIM vX              AS VARIANT
      DIM vY              AS VARIANT
      DIM a()             AS WSTRING
      DIM b()             AS CURRENCY
      DIM y               AS LONG
      ' Chart Object param variables
      DIM vSource         AS VARIANT
      DIM vGallery        AS VARIANT
      DIM vFormat         AS VARIANT
      DIM vPlotBy         AS VARIANT
      DIM vCatLabels      AS VARIANT
      DIM vSerLabels      AS VARIANT
      DIM vHasLegend      AS VARIANT
      DIM vTitle          AS VARIANT
      ' SaveAs Object param variables
      DIM vFile           AS VARIANT
      vFile = EXE.PATH$ & "AAATest.xlsx"  ' Set output filename
      ' Open an instance of EXCEL
      ' oExcelApp = ANYCOM $PROGID_Excel_Application      '<<< changed to GETCOM >>>
        oExcelApp = GETCOM $PROGID_Excel_Application              ' Get the running Excel handle
        IF ISFALSE ISOBJECT(oExcelApp) THEN                       ' If Excel is not running,
            iNew = 1                                              ' flag as new
            oExcelApp = NEWCOM $PROGID_Excel_Application          ' and start a new instance of Excel
            IF ISFALSE ISOBJECT(oExcelApp) OR ERR THEN            ' Could EXCEL be opened? If not, terminate this app
                ? "Excel could not be opened. Please check that Excel and VBA are installed."
                CLOSE #1
                EXIT FUNCTION
            END IF
        END IF
        OBJECT GET oExcelApp.version TO oVnt
      ' <<< ADDED Set Visible=FALSE and DisplayAlerts=FALSE                               >>>
      ' <<< Visible=FALSE works in Excel version 12 (2007), but not in version 16 (2010). >>>
      vBool = %False
      OBJECT LET oExcelApp.Visible = vBool           ' False = Turn off view so Excel is not visible while doing its thing.
      OBJECT LET oExcelApp.DisplayAlerts = vBool     ' False = Turn off Excel alerts (save? overwrite? etc.)
      ' Create a new workbook in EXCEL
      OBJECT CALL oExcelApp.WorkBooks.Add TO oExcelWorkBook
        ? "Excel could not open a new workbook. Please check that VBA is installed."
        GOTO Terminate
      END IF
      OBJECT CALL oExcelWorkBook.WorkSheets.Add TO oExcelWorkSheet
        ? "Excel could not open a new worksheet. Please check that VBA is installed."
        GOTO Terminate
      END IF
      ' Format and send data for cells A1:G1
      LET vRange = "A1:G1"$$
      ' Create a Day of the Week array for A1:G1
      DIM a(1 TO 7) AS WSTRING
      a(1) = "Monday"    : a(2) = "Tuesday"
      a(3) = "Wednesday" : a(4) = "Thursday"
      a(5) = "Friday"    : a(6) = "Saturday"
      a(7) = "Sunday"
      LET vText = a()
      OBJECT LET oExcelWorkSheet.Range(vRange).Value = vText
      ' Center the column title text
      LET vVnt = %XlHAlign.xlHAlignCenter
      OBJECT LET oExcelWorkSheet.Range(vRange).HorizontalAlignment = vVnt
      ' Set the font attributes and border for this header line
      OBJECT LET oExcelWorkSheet.Range(vRange).FONT.Bold = vBool
      ' Color the text Blue
      LET vVnt = RGB(0,0,255)
      OBJECT LET oExcelWorkSheet.Range(vRange).FONT.COLOR = vVnt
      ' Set the column width to approx 11 characters
      LET vVnt = 11
      OBJECT LET oExcelWorkSheet.Range(vRange).ColumnWidth = vVnt
      ' Enclose the table with a border
      LET vRange = "A1:G2"$$
      LET vVnt = %XlBorderWeight.xlMedium        ' Medium density border
      OBJECT CALL oExcelWorkSheet.Range(vRange).BorderAround(Weight = vVnt)
      ' Format and send data for cells A2:G2
      LET vRange = "A2:G2"$$
      ' Create a sales value array for each day of the week
      DIM b(1 TO 7) AS CURRENCY
      FOR y = 1 TO 7
          b(y) = RND(1,100000@)
      NEXT y
      LET vVnt = b()
      OBJECT LET oExcelWorkSheet.Range(vRange).Value = vVnt
      ' Format these cells in money (currency) format, ie: "$#.##"
      LET vText = "Currency"$$
      OBJECT LET oExcelWorkSheet.Range(vRange).Style = vText
    '' <<< REMOVED THIS: don't want it visible to user. >>>
    ''  ' Now we can make EXCEL visible to the user
    ''  LET vBool = 1
    ''  OBJECT LET oExcelApp.Visible = vBool
      ' Prepare the chart
      OBJECT CALL oExcelApp.Charts.Add TO oExcelChart'oVnt
      ' Set the range of cells to use in the table. Here it's 7x2
      LET vRange = "A1:G2"$$
      OBJECT GET oExcelWorkSheet.Range(vRange) TO vSource
      ' Set the Chart parameters
      LET vGallery   = %XlChartType.xl3DPie
      LET vFormat    = 7
      LET vPlotBy    = %XlRowCol.xlRows
      LET vCatLabels = 1
      LET vSerLabels = 0
      LET vHasLegend = 1
      LET vTitle     = "Sales percentages"$$
      ' Launch the Chart
      OBJECT CALL oExcelChart.ChartWizard(vSource, vGallery, vFormat, vPlotBy, vCatLabels, vSerLabels, vHasLegend, vTitle)
      OBJECT CALL oExcelWorkbook.SaveAs(vFile)
      ' Close the current Worksheet.
      ' <<< ADDED logic to leave EXCEL open if it was open before this app ran. >>>
      IF ISTRUE(iNew) THEN                               ' Only close EXCEL if it a new instance.
          OBJECT CALL oExcelApp.ActiveWindow.Close
          OBJECT CALL oExcelApp.Quit
      ELSE                                               ' If Excel was opened before running,
          OBJECT CALL oExcelWorkbook.Close(vFile)        ' Close this file
          vBool = %True                                  '
          OBJECT LET oExcelApp.Visible = vBool           ' Make Excel visible again
      END IF
      ' Release the interfaces.  We could omit this since the
      ' app is about to close, but "best practice" states we
      ' should clean our house before moving out.
      oExcelApp       = NOTHING
      oExcelWorkbook  = NOTHING
      oExcelWorkSheet = NOTHING
      oExcelChart     = NOTHING
    ... .... . ... . . ... ... .... . .. ... .... .. ... .... .. .... ..

    n6jah @

  • #2
    Have you been thinking about using Libxl?
    /Fim W.
    Fim Wästberg


    • #3
      Hi Fim,
      Yes, but the boss doesn't want to spend the money. I'm considering using my own money.

      You may have heard the old saying, "Penny wise, pound foolish". Yes, I'm dealing with that mentality.
      ... .... . ... . . ... ... .... . .. ... .... .. ... .... .. .... ..

      n6jah @


      • #4
        Hi Jim

        Why don't you use

        DIM vTrue        AS VARIANT
        DIM vFalse       AS VARIANT
        vTrue = -1
        vFalse = 0
        Watch out for some functions that require a variant-boolean-variable such as PageSetup.Zoom or FitToPages.