Editing XLS files using EBASIC database functions

[JoaoAfonso]

Good evening.
I am posting here for 2 reasons: 1st because I need to have this program finished before monday, and 2nd because noone posted here before.
I need to output some data to an EXCELL file. The way I am trying to do it is have a XLS file, then make a clone of it in the destination choosen by the user, and make updates on that clone.
I've checked this code to read EXCELL file data:

Code Select Expand

' How to read EXCEL Workbooks and -sheets with the DataBase pak
' By Michael Hartlef, based on the original database_test.iba file
' Ibasic pro code, you need the database pak

OPENCONSOLE
'These are the variables that take in the values of the columns
DEF name,code as STRING
DEF amount as INT
DEF pDrivers,pTables,pBindings as POINTER
pDrivers = dbEnumDrivers()
IF pDrivers <> NULL
   PRINT "Available database drivers:"
   FOR temp = EACH pDrivers as STRING
      PRINT #temp
   NEXT
   ListRemoveAll(pDrivers,TRUE)
ELSE
   PRINT "Unable to enumerate drivers"
ENDIF
PRINT:PRINT
'dump the layout (schema) of the database and retrieve the data from a table.
pdb = dbConnect("Microsoft Excel Driver (*.xls)",GETSTARTPATH + "test.xls","")
IF pdb <> NULL
   PRINT "Connection established to " + GETSTARTPATH + "test.xls"
   pTables = dbListTables(pdb)
   PRINT "Tables:"
   IF pTables <> NULL
      FOR temp = EACH pTables as STRING
         PRINT #temp
         PRINT "Cardinality:",dbCardinality(pdb,#temp)
         PRINT "\tColumns:"
         pColumns = dbListColumns(pdb,#temp)
         IF pColumns <> NULL
            FOR temp2 = EACH pColumns as STRING
               PRINT "\t\t",#temp2
            NEXT
            ListRemoveAll(pColumns,TRUE)
         ENDIF
      NEXT
      ListRemoveAll(pTables,TRUE)
   ELSE
      PRINT "Unable to list tables"
   ENDIF
   'list the data in the "Tabelle1" worksheet
   PRINT
   PRINT "Data :"
   'example SQL statements to retrieve datasets
   'dbExecSQL returns a handle to the executed statment or zero on failure.
   'A valid statement handle is returned even on an empty result set.
   hstmt = dbExecSQL(pdb,"SELECT * FROM [Tabelle1$]")
   error = dbGetErrorCode(hstmt)
   IF LEN(error)
      PRINT
      PRINT "Error Code: ", error
      PRINT "Error Text: ", dbGetErrorText(hstmt)
      PRINT
   ENDIF
   IF hStmt
      'bind the columns in the table to appropriate variables
      'column numbers start at 1 starting from the left.
      dbBindVariable(hstmt,1,name)
      dbBindVariable(hstmt,2,code)
      dbBindVariable(hstmt,3,amount)
      'dbGet returns TRUE if data has been retrieved and stored
      'in the bound variables. Or FALSE if the end of data has been reached or an error occured
      WHILE dbGet(hstmt)
         PRINT "Name:",name
         PRINT "Code:",code
         PRINT "Amount:",amount
         PRINT
      ENDWHILE
      dbFreeSQL(hstmt)
   ENDIF
   dbDisconnect(pdb)
ELSE
   PRINT "Unable to connect to database"
ENDIF
PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END

I need to UPDATE data in an excell file. The point is read data works ok, but update is not working.
As far as I could understand, reading data from a XLS file will make columns name F1, F2, F3, etc, until it reaches the last column. In this way I am trying to update data this way, which should work (consider that I want to change "<STANDARD1>" to "change1", and that is in column K, line 20 in an excell table):

Code Select Expand

hstmt = dbExecSQL(pdb,"UPDATE [Tabela$] SET F11='change1' WHERE F11='<STANDARD1>'")

Thanks in advance

[Ionic Wind Support Team]

Sorry but I don't have an answer for you.  I am not sure if the XLS driver even allows updates.  You can do it through dispatch helper though.

Paul.

[JoaoAfonso]

Oh... Ok, thank you. Guess I will spend sometime more googling for a solution before give up.

[JoaoAfonso]

Guess I could solve the problem. The pattern of the ODBC driver is open in read only mode. In this way, adding "ReadOnly=0" in the options of dbConnect, it will let me make de updates.

Hope this can be useful for others

Code Select Expand

pdb = dbConnect("Microsoft Excel Driver (*.xls)",ficheiro,"ReadOnly=0")