Announcement

Collapse
No announcement yet.

EXCEL COM & Timer

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

  • EXCEL COM & Timer

    I have used the numerous examples in this forum to access data in excel, successfuly. The read excel routines are in a seperate sub. When I call this sub from a timer, I get an error that the cells were not found. When I call the routine directly from pbmain, the data is read and written to a text file, properly. Here is the code:
    Code:
    #COMPILE EXE
    #DIM ALL
    #INCLUDE "WIN32API.INC"
    #INCLUDE "ExcelApp.inc"
    
    '------------------------------------------------------------------------------
    ' Equates
    '------------------------------------------------------------------------------
    $AppTitle = "Pickup Data Example"
    %ID_TIMER = 105
    %ID_CANCELTIMER = 110
    %ID_END = 115
    
    '------------------------------------------------------------------------------
    ' Callback Declarations
    '------------------------------------------------------------------------------
    DECLARE CALLBACK FUNCTION StartTimerCallBack()
    DECLARE CALLBACK FUNCTION CancelTimerCallBack()
    DECLARE CALLBACK FUNCTION EndCallBack()
    DECLARE SUB TimerProc()
    
    GLOBAL htimer AS LONG
    
    FUNCTION PBMAIN () AS LONG
    
    LOCAL    hDlg    AS DWORD
    
    DIALOG NEW 0, $AppTitle, 77, 22, 300, 167, _
            %DS_CENTER OR %WS_CAPTION OR %WS_SYSMENU, 0 TO hDlg
    
        ' Abort if the dialog could not be created
        IF hDlg = 0 THEN EXIT FUNCTION  ' Error occurred
    
        DIALOG SET COLOR hDlg, -1&, RGB(255,255,222)
    
    ' Add the command buttons
        CONTROL ADD BUTTON,   hDlg, %ID_TIMER, "Start Timer",  10, 133,  60,  24 CALL StartTimerCallBack
        CONTROL ADD BUTTON,   hDlg, %ID_CANCELTIMER,   "Cancel Timer",  75, 133,  60,  24 CALL CancelTimerCallBack
        CONTROL ADD BUTTON,   hDlg, %ID_END,      "END",  155, 133,  60,  24 CALL EndCallBack
    
        ' Start up our GUI, and run until the user quits
        DIALOG SHOW MODAL hDlg
    
    'when I call TimerProc directly (below) the excel data is accessed and written to file
    
    CALL TimerProc()
    
    CALL TimerProc()
    
    
    END FUNCTION
    
    '------------------------------------------------------------------------------
    ' Main dialog callback
    '------------------------------------------------------------------------------
    CALLBACK FUNCTION StartTimerCallBack
    ' set the timer
      ' 1 second = 1000 milliseconds. the next line has 10 second timer
     '                     Delay      Resolution                     UserValue
     hTimer = TimeSetEvent(BYVAL 10000, BYVAL 1000, CODEPTR(TimerProc), BYVAL 0, BYVAL %TIME_PERIODIC)
    END FUNCTION
    
    CALLBACK FUNCTION CancelTimerCallBack
    TimeKillEvent hTimer
    END FUNCTION
    
    CALLBACK FUNCTION EndCallBack
    
    DIALOG END CBHNDL
    
    END FUNCTION
    
    
    SUB TimerProc()
    
    
    LOCAL oExcelApp AS ExcelApplication
    LOCAL oExcelWorkbook AS ExcelWorkbook
    LOCAL oExcelWorkSheet AS ExcelWorkSheet
    LOCAL vRange,vText,vVnt,vBool, vInFile, vInSheet AS VARIANT
    
    LOCAL strProgramID AS STRING
    
    REGISTER i AS LONG
    LOCAL txtfilenum AS LONG
    
    LOCAL aa AS DOUBLE
    
    txtfilenum = FREEFILE
    ' file to which I wish to append the excel data
    OPEN "april11.txt" FOR APPEND AS #txtfilenum
    
      strProgramID=PROGID$(CLSID$("Excel.Application"))
      SET oExcelApp = ExcelApplication IN strProgramID
      vBool = 1
      OBJECT LET oExcelApp.Visible = vBool
    
       '----------------------------------------------------------------
        ' Open workbook
        vInFile = "f:\mywork\pb_code\ddelink\dde1"
    
        OBJECT GET oExcelApp.ActiveWorkBook TO vVnt
    
        SET oExcelWorkbook = vVnt
        IF OBJRESULT OR ERR THEN
            MSGBOX "Excel could not open workbook."
            EXIT SUB
        END IF
    
        'I know the workbook & sheet names
        LET vInSheet = "Sample"
        OBJECT GET oExcelApp.ActiveSheet TO vVnt
        SET oExcelWorkSheet = vVnt
    
    ' pick up data from column B, rows 2 to 9
    FOR I = 2 TO 9
          vRange="B" & TRIM$(STR$(i))
    
          OBJECT GET oExcelWorkSheet.Range(vRange).Value TO vText
    
          IF OBJRESULT OR ERR THEN
                      MSGBOX "Failed getting cell" + STR$(OBJRESULT) + "  ERR:" + STR$(ERR)
    
          END IF
    
    aa = VARIANT#(vText)
    PRINT #txtfilenum, aa
    
    NEXT I
    
      'OBJECT CALL oExcelApp.Quit
      SET oExcelApp = NOTHING
      SET oExcelWorkbook = NOTHING
      SET oExcelWorkSheet = NOTHING
    
    CLOSE #txtfilenum
    
    
    END SUB
    Last edited by Sudarshan Sukhani; 11 Apr 2008, 06:42 AM. Reason: correcting a remark, putting code in square brackets

  • #2
    I know little about Excel, but one thing I do know is that if you wrap your code inside {code} and {/code} delimiters (use square braces not curly ones) it becomes much easier to read.

    Comment


    • #3
      Your timerproc procedure has the wrong header.

      For any other value of fuEvent, the lpTimeProc parameter is interpreted as a function pointer with the following signature: void (CALLBACK)(UINT uTimerID, UINT uMsg, DWORD_PTR dwUser, DWORD_PTR dw1, DWORD_PTR dw2);
      Since the timerproc is actually being called with five parameters pushed onto the stack, and you told pb there were no parameters , I'm surprised the negative results are limited to "bad data."

      [ADDED]
      "For your consideration," as might have been said by the late Rod Serling.... "a callback procedure with no parameters seems just a bit unusual.... except, perhaps, in TheTwilight Zone...."

      MCM
      Last edited by Michael Mattias; 11 Apr 2008, 08:29 AM.
      Michael Mattias
      Tal Systems (retired)
      Port Washington WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        EXCEL, Com & Timer issues resolved

        Michael pointed out that my callback procedure declarations were incorrect. I corrected that, but the problem was not resolved. Finally, I removed the TimeSetEvent command and put in the SetTimer command. My timer now works, picking up data at required intervals from an excel spreadsheet. Here is the finished code. (I think the cancel button does not stop the timer, but I can always close the application).
        Code:
        #COMPILE EXE
        #DIM ALL
        #INCLUDE "WIN32API.INC"
        #INCLUDE "ExcelApp.inc"
        
        '------------------------------------------------------------------------------
        ' Equates
        '------------------------------------------------------------------------------
        $AppTitle = "Pickup Data Example"
        %ID_TIMER = 105
        %ID_CANCELTIMER = 110
        %ID_END = 115
        
        '------------------------------------------------------------------------------
        ' Callback Declarations
        '------------------------------------------------------------------------------
        DECLARE CALLBACK FUNCTION StartTimerCallBack()
        DECLARE CALLBACK FUNCTION CancelTimerCallBack()
        DECLARE CALLBACK FUNCTION EndCallBack()
        DECLARE SUB TimerProc()
        DECLARE FUNCTION T10 ( BYVAL uID AS LONG, BYVAL uMsg AS LONG, _
        BYVAL dwUser AS LONG, BYVAL dw1 AS LONG, BYVAL dw2 AS LONG) AS LONG
        
        GLOBAL htimer AS LONG
        GLOBAL hDlg AS DWORD
        
        FUNCTION PBMAIN () AS LONG
        
        DIALOG NEW 0, $AppTitle, 77, 22, 300, 167, _
                %DS_CENTER OR %WS_CAPTION OR %WS_SYSMENU, 0 TO hDlg
        
            ' Abort if the dialog could not be created
            IF hDlg = 0 THEN EXIT FUNCTION  ' Error occurred
        
            DIALOG SET COLOR hDlg, -1&, RGB(255,255,222)
        
        ' Add the command buttons
            CONTROL ADD BUTTON,   hDlg, %ID_TIMER, "Start Timer",  10, 133,  60,  24 CALL StartTimerCallBack
            CONTROL ADD BUTTON,   hDlg, %ID_CANCELTIMER,   "Cancel Timer",  75, 133,  60,  24 CALL CancelTimerCallBack
            CONTROL ADD BUTTON,   hDlg, %ID_END,      "END",  155, 133,  60,  24 CALL EndCallBack
        
            ' Start up our GUI, and run until the user quits
            DIALOG SHOW MODAL hDlg
        
        END FUNCTION
        
        '------------------------------------------------------------------------------
        ' Main dialog callback
        '------------------------------------------------------------------------------
        CALLBACK FUNCTION StartTimerCallBack
        LOCAL TimerEvent AS LONG
        
        hTimer = SetTimer(hDlg, TimerEvent,3000,CODEPTR(T10))
        END FUNCTION
        
        CALLBACK FUNCTION CancelTimerCallBack
        LOCAL result AS LONG
        result = KillTimer(hDlg,hTimer)
        
        END FUNCTION
        
        CALLBACK FUNCTION EndCallBack
        
        DIALOG END CBHNDL
        
        END FUNCTION
        
        
        SUB TimerProc()
        
        
        LOCAL oExcelApp AS ExcelApplication
        LOCAL oExcelWorkbook AS ExcelWorkbook
        LOCAL oExcelWorkSheet AS ExcelWorkSheet
        LOCAL vRange,vText,vVnt,vBool, vInFile, vInSheet AS VARIANT
        
        LOCAL strProgramID AS STRING
        
        REGISTER i AS LONG
        LOCAL txtfilenum AS LONG
        
        LOCAL aa AS DOUBLE
        
        txtfilenum = FREEFILE
        ' file to which I wish to append the excel data
        OPEN "april11.txt" FOR APPEND AS #txtfilenum
        
          strProgramID=PROGID$(CLSID$("Excel.Application"))
          SET oExcelApp = ExcelApplication IN strProgramID
          vBool = 1
          OBJECT LET oExcelApp.Visible = vBool
        
           '----------------------------------------------------------------
            ' Open workbook
            vInFile = "f:\mywork\pb_code\ddelink\dde1"
        
            OBJECT GET oExcelApp.ActiveWorkBook TO vVnt
        
            SET oExcelWorkbook = vVnt
            IF OBJRESULT OR ERR THEN
                MSGBOX "Excel could not open workbook."
                EXIT SUB
            END IF
        
            'I know the workbook & sheet names
            LET vInSheet = "Sample"
            OBJECT GET oExcelApp.ActiveSheet TO vVnt
            SET oExcelWorkSheet = vVnt
        
        ' pick up data from column B, rows 2 to 9
        FOR I = 2 TO 9
              vRange="B" & TRIM$(STR$(i))
        
              OBJECT GET oExcelWorkSheet.Range(vRange).Value TO vText
        
              IF OBJRESULT OR ERR THEN
                          MSGBOX "Failed getting cell" + STR$(OBJRESULT) + "  ERR:" + STR$(ERR)
        
              END IF
        
        aa = VARIANT#(vText)
        PRINT #txtfilenum, aa
        
        NEXT I
        
          'OBJECT CALL oExcelApp.Quit
          SET oExcelApp = NOTHING
          SET oExcelWorkbook = NOTHING
          SET oExcelWorkSheet = NOTHING
        
        CLOSE #txtfilenum
        
        
        END SUB
        
        FUNCTION T10 ( BYVAL uID AS LONG, BYVAL uMsg AS LONG, _
        BYVAL dwUser AS LONG, BYVAL dw1 AS LONG, BYVAL dw2 AS LONG) AS LONG
        
        CALL TimerProc()
        
        END FUNCTION

        Comment


        • #5
          TimeSetEvent runs in its own thread, so if you use it, you have to initialize the COM library with CoInitializeEx and uninitialize it with CoUninitialize for that thread.
          Forum: http://www.jose.it-berater.org/smfforum/index.php

          Comment


          • #6
            TimeSetEvent runs in its own thread, so if you use it, you have to initialize the COM library with CoInitializeEx and uninitialize it with CoUninitialize for that thread.
            I don't use much (OK, so I don't use any) COM so I don't know if this is a good idea or not....

            Since the compiler already automatically initializes the COM library (using CoInitialize) for the primary thread, shouldn't it also automatically do so for each additional thread of execution launched?

            Of course the problem here is not addressed, since the compiler can't possibly know about additional threads of execution launched by calls to external libraries (in this case the winapi).

            But I wonder if CoInitialize is called automatically when a THREAD CREATE is executed?

            If not, should it, to be consistent?

            MCM
            Michael Mattias
            Tal Systems (retired)
            Port Washington WI USA
            [email protected]
            http://www.talsystems.com

            Comment


            • #7
              But I wonder if CoInitialize is called automatically when a THREAD CREATE is executed?
              As far as I have heared from Jose, its called automatically by PB for the Main Programm, but not for Threads.

              Comment


              • #8
                As far as I have heared from Jose, its called automatically by PB for the Main Programm, but not for Threads.
                This was true for previous versions of the compilers. PBWIN 9.+ and PBCC 5-+ call CoInitialize/CoUninitialize for each thread.
                Forum: http://www.jose.it-berater.org/smfforum/index.php

                Comment

                Working...
                X