Announcement

Collapse
No announcement yet.

Excel Tested Example Code using PBWIN10 and PB Samples Excel.inc

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

  • Excel Tested Example Code using PBWIN10 and PB Samples Excel.inc

    I am working on converting a VS2008 project to PBWIN10. To accomplish this I have to interface with Access and Excel.

    Enough said. I wanted to share some of the short excel code with those who might like some tested short routines which work with the PBWIN10 api and use the PBWIN10 Samples/Objects/Excel/Excel.inc files.

    I have provided the code. You NEED TO CHANGE the IFILENAME in the code to reflect the path and location of your filename.xls file to use with this code. I have left the IFILENAME path and location to my file I used for testing in the code so you simply need to substitute your path and filename and make sure your excel file is there.

    I hope this helps those looking for this.

    I really take no credit for any of this code although I am sure there are better ways and faster to do it. Most of the code has been obtained through POFF posts and massaged and adjusted for my needs. Thanks to all those whom have contributed to the POFFS.

    Comments welcome.

    Wayne.

    Code:
     
    '==============================================================================
    ' Tested Excel Code
    '==============================================================================
    'Compiler Directives
    '----------------------------------
    #COMPILER PBWIN 10
    #COMPILE EXE
    #DIM ALL
    #INCLUDE ONCE "Excel.inc"
    '------------------------------------------------------------------------------
    'Main Program
    '-----------------------------------
    FUNCTION PBMAIN
    '-----------------------------------
    'Variables
    '-----------------------------------
    ' Object Variables
    '-------------------
      DIM oExcelApp       AS Excel_Application
      DIM oExcelWorkbook  AS Excel_Workbook
      DIM oExcelWorkSheet AS Excel_WorkSheet
      DIM oExcelChart     AS Excel_Chart
    '-------------------------------------------------------------------------------
    ' Open an instance of EXCEL
    '-------------------------------------------------------------------------------
    'Variables
    '--------------------------
    LOCAL IFILENAME AS WSTRING
    LOCAL sExcelPath AS WSTRING
    '------------------------------------
    'Location of S/S provided by user
    'in this case includes path also.
    'Put your path & filename.xls here.
    'Make sure your file is there also!
    '------------------------------------
    IFILENAME = "C:\UTILITY\MODDFW.xls"
    '------------------------------------
    sExcelpath = IFILENAME
    ? "FILENAME TO OPEN: " & IFILENAME
    '-------------------------------------------------------------------------------
      oExcelApp = ANYCOM $PROGID_Excel_Application
    '---------------------------
    ' Could EXCEL be opened? If not, terminate this app
    '---------------------------
      IF ISFALSE ISOBJECT(oExcelApp) OR ERR THEN
        MSGBOX "Excel could not be opened. Please check that Excel and VBA are installed."
        EXIT FUNCTION
      END IF
    '-------------------------------------------------------------------------------
    'Open user selected Excel File
    '-------------------------------------------------------------------------------
      OBJECT CALL oExcelApp.workbooks.Open(sExcelpath) TO oExcelWorkBook
      IF ISFALSE ISOBJECT(oExcelWorkbook) OR ERR THEN
        MSGBOX "Excel could not open the workbook."
        GOTO Terminate
      END IF
    '--------------------------------------------------------------------------------
    'Routine gets the # of sheets and
    'Returns the Sheet # and Name
    '--------------------------------------------------------------------------------
    'Variables
    '---------------------------------------
        LOCAL nVnt AS VARIANT
        LOCAL nSheets AS LONG
        LOCAL ns AS LONG
        LOCAL vExSheet AS VARIANT
        LOCAL vExcelWorkSheet AS VARIANT
    '---------------------------------------
    OBJECT GET oExcelWorkBook.WorkSheets.Count TO nVnt
        nSheets = VARIANT#(nVnt)
        REDIM    SheetNames(1 TO nSheets)        AS STRING
        FOR ns = 1 TO nSheets
            vExSheet = ns
            OBJECT GET oExcelWorkBook.WorkSheets.Item(vExSheet) TO vExcelWorkSheet
            SET oExcelWorkSheet = vExcelWorkSheet
            OBJECT GET oExcelWorkSheet.Name TO nVnt
            SheetNames(ns) = VARIANT$(nVnt)
            ? "SHEET #: " & STR$(ns) & " SHEET NAME: " & sheetnames(ns)
        NEXT
    '---------------------------------------------------------------------------------
    ' Select the first sheet
    '---------------------------------------------------------------------------------
    'Variables
    '---------------------------------------
    LOCAL vWork AS VARIANT
    LOCAL vFalse AS VARIANT
    LOCAL vTrue  AS VARIANT
    '---------------------------------------
          vFalse = 0
          vTrue = 1
          vWork   =   1 'Worksheet # to select
          OBJECT CALL   oExcelWorkBook.WorkSheets  (  vWork  )  .SELECT
          OBJECT GET   oExcelWorkbook.Activesheet   TO   vWork
        oExcelWorkSheet   =   vWork
          '? "Selected sheet #1
    '---------------------------------------------------------------------------------
    'Disable Warning Messages from Excel
    '---------------------------------------------------------------------------------
    OBJECT LET oExcelApp.DisplayAlerts   =   vFalse
    '---------------------------------------------------------------------------------
    'Get specified Cell Content - Example Get Text Headers and store for later use.
    '---------------------------------------------------------------------------------
    'Variables
    '----------------------------
          LOCAL vrow  AS INTEGER
          LOCAL vcol  AS INTEGER
          LOCAL vText AS WSTRING
          LOCAL CCELL AS WSTRING
          LOCAL GHDRTEXT AS INTEGER
          LOCAL vColEnd AS INTEGER
          DIM HeaderColumn(1 TO 26) AS WSTRING
    '---------------------------------
    'User Provides Start vRow and vCol
    'where s/s headers reside on s/s
    '---------------------------------
          LET vrow = 10 :LET vcol = 3
          '---------------------------
          'Get Text Headers for all
          'columns up to 26 which have
          'text headers.
          '---------------------------
          FOR GHDRTEXT = vcol TO vcol + 26
              CCELL =  CHR$(VAL(STR$(vcol + 64))) + STR$(GHDRTEXT)
              OBJECT GET oExcelWorkSheet.Cells(vrow, GHDRTEXT).value TO vText
                  '---------------------------
                  'If no text header were done
                  'Identify the last Header
                  'Column Number.
                  '---------------------------
                  IF vText = "" THEN
                        vColEnd = GHDRTEXT
                        EXIT FOR
                  END IF
                  '------------------------------
                  'Assign Text Headers to Storage
                  'for late comparison for columns
                  'to process based on headers.
                  '------------------------------
                  HeaderColumn(GHDRTEXT) = vText
                  '? "Cell Content at: " & CCELL & " " & HeaderColumn(GHDRText)
          NEXT
          ? "All Header Text Processed."
          FOR GHDRTEXT = vcol TO vColEnd
              ? "Header #" + STR$(GHDRTEXT) & " " & HeaderColumn(GHDRTEXT)
          NEXT
          ? "All Header Text Processed."
    '---------------------------------------------------------------------------------
    'Routine to determine the number of rows of data used in the Worksheet
    '-----------------------------------------------------------------------------------
    LOCAL vRows AS INTEGER
    LOCAL vCols AS INTEGER
    LOCAL vEndRow AS INTEGER
    LOCAL TESTROW AS INTEGER
    LOCAL vData AS WSTRING
    LOCAL vCount AS INTEGER
    '---------------------------------
    'The below info is user supplied
    'upon request based on spreadsheet
    'consistant format
    '------------------------------------------------------
      nSheets = 1 ' # sheet to use
      vCols = 3   ' Column for Data to test
      vRows = 10  ' Starting row to test - skip logos etc.
    '------------------------------------------------------
      FOR ns = 1 TO nSheets
       vExSheet = ns
       OBJECT GET oExcelWorkBook.WorkSheets.Item(vExSheet) TO vExcelWorkSheet ' make sheet active
       SET oExcelWorkSheet = vExcelWorkSheet
       FOR TESTROW = vRows TO 1000
          CCELL =  CHR$(VAL(STR$(vcol + 64))) + STR$(TESTROW)
              OBJECT GET oExcelWorkSheet.Cells(TESTROW, vCols).value TO vData
                  '---------------------------
                  'If no data then were done.
                  'Identify the last data row
                  '---------------------------
                  IF vData = "" THEN
                        vEndRow = TESTROW
                        EXIT FOR
                  END IF
                  '-----------------------------
                  'Counts rows with data in them
                  '-----------------------------
                  vCount = vCount + 1
                  '--------------------------------------
                  'Uncomment below line to show cell data
                  '--------------------------------------
                  '? "Cell Content at Row: " & str$(TESTROW-vRows) & " - " & vData
                  '--------------------------------------
       NEXT
                  ? "Last Data Row for sheet is: " & STR$(vEndRow) & " Total Rows with data: " & STR$(vCount)
      NEXT
    '---------------------------------------------------------------------------------
    'Routine to determine the number of columns of data used in the Worksheet
    '-----------------------------------------------------------------------------------
    'Variables
    '------------------------------
    LOCAL vCRows AS INTEGER
    LOCAL vCCols AS INTEGER
    LOCAL vEndCol AS INTEGER
    LOCAL TESTCOL AS INTEGER
    LOCAL vCData AS WSTRING
    LOCAL vCCount AS INTEGER
    '---------------------------------
    'The below info is user supplied
    'upon request based on spreadsheet
    'consistant format
    '------------------------------------------------------
      nSheets = 1 ' # sheet to use
      vCCols = 3   ' Column for Data to test
      vCRows = 10  ' Starting row to test - skip logos etc.
    '------------------------------------------------------
      FOR ns = 1 TO nSheets
       vExSheet = ns
       OBJECT GET oExcelWorkBook.WorkSheets.Item(vExSheet) TO vExcelWorkSheet ' make sheet active
       SET oExcelWorkSheet = vExcelWorkSheet
       FOR TESTCOL = vCCols TO vCCols + 26
          CCELL =  CHR$(VAL(STR$(vCCols + 64))) + STR$(TESTCOL)
              OBJECT GET oExcelWorkSheet.Cells(vcRows, TESTCOL).value TO vCData
                  '---------------------------
                  'If no data then were done.
                  'Identify the last data row
                  '---------------------------
                  IF vCData = "" THEN
                        vEndCol = TESTCOL
                        EXIT FOR
                  END IF
                  '-----------------------------
                  'Counts rows with data in them
                  '-----------------------------
                  vCCount = vCCount + 1
                  '--------------------------------------
                  'Uncomment below line to show cell data
                  '--------------------------------------
                  '? "Cell Content at Column: " & str$(TESTCOL-vCCols) & " - " & vCData
                  '--------------------------------------
       NEXT
                  ? "Last Data Column for sheet is: " & STR$(vEndCol) & " Total Cols with data: " & STR$(vCCount)
      NEXT
    '-----------------------------------------------------------------------------------
    'Routine Makes Excel Visible
    '---------------------------------------------------------------------------------
    OBJECT LET oExcelApp.Visible = vTrue
    '---------------------------------------------------------------------------------
    'Routine Cleans Up and Ends Excel Applicaton
    '---------------------------------------------------------------------------------
    Terminate:
      MSGBOX "Click the OK button to close Excel and exit the application"
    '----------------------------------------------
    ' Close the current Worksheet and close EXCEL
    '----------------------------------------------
      OBJECT CALL oExcelApp.ActiveWindow.Close
      OBJECT CALL oExcelApp.Quit
    '----------------------------------------------
    'Cleanup and Release Interfaces.
    '----------------------------------------------
      oExcelApp       = NOTHING
      oExcelWorkbook  = NOTHING
      oExcelWorkSheet = NOTHING
      oExcelChart     = NOTHING
    '---------------------------------------------------------------------------------
    END FUNCTION
    LEARNING EXPERIENCE: What you get when you didn't get what you THOUGHT you wanted!

  • #2
    Argument to Excel macro runing twice?

    I think I tried most combinations to get the arguments to an Excel macro to work properly.
    It is precisely the arguments that do not work. Function works but runs twice. If I do not send in any argument, it runs only once.
    But what am I doing wrong to only be run once with arguments?

    I'am using: PB10 Excel: 2010

    ------ This is Excel macro ---------
    Sub Test1(sInd As String)
    MsgBox "Answer test1 " & sInd
    End Sub


    Code:
    #COMPILE EXE
    #DIM ALL
    
    #INCLUDE "Excel.inc"
    
    FUNCTION PBMAIN()
       LOCAL oExcelApp          AS Int__Application ' Application Interface
       LOCAL oExcelWorkBook     AS Int__Workbook    ' Document Interface
    
       LOCAL vFalse, vTrue      AS VARIANT
       LOCAL VBAmacro           AS STRING
       LOCAL sFileName          AS STRING
       LOCAL vInFile            AS WSTRING
    
       LET vFalse     = 0
       LET vTrue      = 1
    
       sFileName = CURDIR$ & "\test.xlsm" ' with macros
    
       LET vInFile = sFileName ' do not forget drive/directory
    
       oExcelApp = ANYCOM CLSID $CLSID_Application
       ' Could MSExcel be opened? If not, terminate this exe
       IF ISFALSE ISOBJECT(oExcelApp) THEN
         EXIT FUNCTION
       END IF
    
       OBJECT CALL oExcelApp.WorkBooks.Open( vInFile, UpdateLinks=vFalse) TO oExcelWorkBook
         ' Make MSExcel visible and active window
       OBJECT LET oExcelApp.Visible = vTrue
       OBJECT LET oExcelApp.EnableEvents = vFalse
       ' Run macro with argument
       VBAmacro = UCODE$(  "'test.xlsm'!thisworkbook.test1( ""BBB"" )"   )
       OBJECT CALL oExcelApp.Run (VBAmacro  )
    
       oExcelApp       = NOTHING
       oExcelWorkBook  = NOTHING
    
      EXIT FUNCTION

    Comment


    • #3

      The doc at https://msdn.microsoft.com/en-us/lib...ice.14%29.aspx (for Excel 2010) suggests the RUN method call line should be
      Code:
       OBJECT CALL  oExcelApp.Run  (macroname, argument_to_function [,argument_to_function]... )
      This suggests you need a comma between the macro identification and the argument. I'd also think about making that argument a variable of type VARIANT
      Code:
      LET vbMacro         =  CURDIR$ & "\test.xlsm!thisworkbook.test1"  AS WSTRING
       ' I assume this would be the syntax for macro identification but I'd double check
      Let vbMacroArgument = "BBB" AS WSTRING
      OBJECT CALL ExcelApp.Run (VbMacro, vbMacroArgument)
       IF OBJRESULT OR ERR
           perform your error reporting
      ELSE
         MSGBOX "'RUN' method success"
       END IF
      I have no idea why this would run twice but maybe you managed to confuse the application by pounding all that stuff into a single string?

      Worth a shot?

      MCM
      Michael Mattias
      Tal Systems Inc.
      Racine WI USA
      mmattias@talsystems.com
      http://www.talsystems.com

      Comment


      • #4

        Thanks champion, now it works! I have spent last 2-3 hours to find this. Has tested similar to your solution, and been on the website too.
        The key was to divide it and use UNICODE$ But still don't understand why it worked but was run twice?
        Thanks
        Code:
        LET vbMacro         = UCODE$("'test.xlsm'!thisworkbook.test1" )  ' I assume this would be the syntax for macro identification but I'd double check    LET vbMacroArgument = UCODE$("BBB")   OBJECT CALL oExcelApp.Run (VbMacro, vbMacroArgument)

        Comment


        • #5
          Hi Wayne

          Maybe a simpler solution to determine the last used row / column - and you don't have to worry whether your spreadsheet ends at row 1000 or row 765332

          Code:
          DIM MyRows       AS VARIANT
          DIM AllRows      AS VARIANT
          DIM MyCols       AS VARIANT
          DIM AllCols      AS VARIANT
          DIM v1           AS VARIANT 
          
          'xls constants
          DIM xlUp        AS VARIANT
          DIM xlToLeft    AS VARIANT
          
          xlUp     = -4162
          xlToLeft = -4159
          
          v1 = 1
          
             'Number of used rows
              OBJECT GET oExcelWorkSheet.Rows.Count TO AllRows
              OBJECT GET oExcelWorkSheet.Cells(AllRows, v1).End(xlUp).Row TO MyRows
          
             'Number of used columns
              OBJECT GET oExcelWorkSheet.Columns.Count TO AllCols
              OBJECT GET oExcelWorkSheet.Cells(MyRows, AllCols).End(xlToLeft).Column TO MyCols
          (assuming you're testing for first column (v1) and last row (MyRows))

          Comment


          • #6
            > The key was to.....use [UCODE$]

            You would not have had to do that had you ..
            Code:
             LOCAL vbMacro  AS  WSTRING
            If you assign a quoted literal to a Wide string var, it is stored wide even though you type it using ANSI (SBCS). It's one of the "automatic" fringe benefits of the 10x compiler.

            Since the 10x compiler is natively wide, and the DISPATCH interface uses wide params (all COM does), why not just make the entire application use Wide Strings? That's the way I've been approaching it.

            My only problem (???) is that I have a huge library of code from which I can cut and paste... but all that code uses STRING and ASCIIZ instead of WSTRING and WSTRINGZ .... so that's really not so bad. .



            Michael Mattias
            Tal Systems Inc.
            Racine WI USA
            mmattias@talsystems.com
            http://www.talsystems.com

            Comment

            Working...
            X