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:
' 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):
hstmt = dbExecSQL(pdb,"UPDATE [Tabela$] SET F11='change1' WHERE F11='<STANDARD1>'")
Thanks in advance
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.
Oh... Ok, thank you. Guess I will spend sometime more googling for a solution before give up.
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 :)
pdb = dbConnect("Microsoft Excel Driver (*.xls)",ficheiro,"ReadOnly=0")