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
Comment