problems exporting paradox 4.5 win to excel 2003

I'm having problems with the exportSpreadsheet function in ObjectPal and was wondering if anyone could help. I have simplified the problem and now have single pushButton on a form with following code

if not exportSpreadsheet("invrpt01.db", "invrpt01.xls", TRUE) then

     errorShow()

endif

invrpt01.db is a 5 column, 24 row database. I use the pushButton and exit paradox application. When opening invrpt01.xls I get excel error dialog box "unable to open file", when I hit ok the file opens but is blank. I have also tried writing csv file using exportASCIIVar function but the text file is also blank (no error on opening .txt). I have tried using the exportDlg function in the pushButton but this gives the same blank xls file result.

When I use the export function under main paradox file->utilities menu the export works fine for both the xls and the txt.

I am running Paradox for Win 4.5 and excel 2003 on Windows XP

I'm a little lost as to what is going on here and any suggestions or help would be greatfully received.

Thanks

Hi Mark,

Your syntax is correct. As a test I exported the customer.db files from the :samples; alias and it opened fine in Excel 2003. I was using Paradox 9 at a test (I don't have PDoxWin 4.5 installed). As a test, change your working directory to the :samples: alias and export the customer table to a spreadsheet:

if not exportSpreadsheet("customer.db", "customer.xls", TRUE) then
 errorShow()
endif

If this test works, you might try using Table Repair and repairing your table just in case it's corrupt in a way that's showing up in this export. If the above test fails but Excel otherwise works fine, then try reinstalling Paradox.

I've seen this many times. What's going on is the FileSystem has not finished writing the table when your export to spreadsheet tries to grab the table. Replace your test TableView.Open right before the export with sleep(500) and it'll work.

instead of the Sleep(), try an Isfile()

while not isfile(whatever) loop endwhile

the isfile() forces a table buffer flush as soon as the write is finished.. I'd add a liitle bit of code to allow for a timeout, errors, etc., too, of course..

also, please not that your comment "I can view the table OK, so it's not corrupt" is totally invalid.. being able to open a table doesn't always mean it's OK.. your typical GP abort in a table view context occurs beause paradox doesn't know there's a bad pointer and/or a bad block until it gets there..

--

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

Mark.. two things:

first, you might want to try killing the 'table view" completely (the tv file).. I've never used it, because there were always reports of various problems associated with it.. it's a long-gone concept.. make a copy of the table, kill the tv, and try to work with that copy..

second.. back to the "not corrupt" theory.. as you mentioned, when you pass "bad stuff" from one table to another, the "bad stuff" appears in the new table.. if you think about it, that's completely normal behavior, for many reasons..

when you suspect "bad stuff" that a rebuild didn't kill, you should export to ascii, make a new table from scratch (don't borrow, make it from scratch), then import the ascii to the new table.. that's your best, last-resort option to kill the "bad stuff", whatever it may be..

--

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

Mark.. a few suggestions and points..

first, when you suspect a temp table is damaged, it's the ORIGINAL table that has to be bebuilt with the table repair tool.. as with anything else, you want to attack a problem at the source..

second.. you're using Win 4.5, which is not heavily used or heavily supported by the developer community.. quirks and problems that are specific to that version might not be noticed by others.. the vast majority of long-time users and developers work with 7, 9, or 10.. you might want to check out Bertil Isberg's extensive bug report site, which you can find on my links page..

finally.. if your original table isn't too big, you can e-mail it to me, and I'll run a few tests on it..

--

Steven Green - Myrtle Beach, South Carolina USA
http://www.OasisTradingPost.com

Mike,

Many thanks for swift reply. I tried :sample:customer.db and customer.xls export worked fine. I think the problem may be connected with how I create the table which I want to export. Table looks fine when viewed so no corrupted data.

I use executeQBEString(qstr, ansTblName) to generate my table. I've used this in past on more complex query strings with no problems. The table generated ansTblName is invrpt01.db. I've checked all declarations and the formation of qstr and all looks ok.

I follow the Query with a check for ansTblName.isEmpty() and if all ok move into a switch. One of the switch cases is the exportSpreadsheet(ansTblName, excelOutputFile) .

Interestingly if I place a open table view .... tview.open(ansTblName) directly before the exportSpreadsheet statement it exports to excel fine every time (of course I have to close the tview manually first). It also exports fine every time if I manually open the table using paradox main menu file->open->table after the executeQBEString and before exporting to excel.

Is there something that I've missed here. Does the query operation hold the table open, or lock it in any way, and do I need to do anything else after the executeQBEString before getting into the export.

Thanks in anticipation. Mark

Mike,

Thanks for suggestion. Tried the sleep(500), but didn't solve the problem. I've also tried splitting out the exportsSreadsheet code to separate test pushbutton, which I manually push after the executeQBEString code has done it's bit and written to the table. Waited for quite a while before pushing, but still exporting blank with error on excel open.

Only thing which seems to work is opening and closing table (using TView or main menu) prior to using the exportSpreadsheet function.

Is there any way I can force a table close before I get into the exportSpreadsheet code?

Thanks Mark

Steve,

tried the isFile() suggestion, but didn't work.

Stepped through the ouput records field by field using TCursor. Loaded dynamic array with TCursor field data and viewed using view(). Couldn't see any stange data, or unexpected field values.

Used Add() to copy TC to another tempory file that I then exported to excel & the problem moves to the new file as well.

Used CREATE to specify table structure before any writes occur, to ensure the field structure is sound.

Only thing that seems to fix is a TView.open() & TView close() just before the export.

This one has got me scratching my head.

Regards

Mark

Steve,

Thanks for reply and advice - Just been using tableview, copy,create, TCusor stuff as a way to try to understand what is happening - It doesn't exist in my original code. I know that the tabe is corrupt in some way but can't figure out what I need to do to fix it, or even see which bit is corrupt.

The table that is corrupt is a temporary one that I create via a query in objectPAL and then just export the result to excel, so table rebuild is not relevant here.

my original code creates a file using executeQBEString, it is this new table created by the query that I to export to excel. Directly after the Query I check it is not empty using a table.isBlank() and then use the exportSpreadheet function. The output file is blank. If I export to ASCII instead of to excel then that file is also blank.

If  I exit Paradox completely after the executeQBEString has run, and then restart paradox and run the exportSpreadsheet function on the table created by the query, the excel file created is still blank (so is Ascii file) - so I think locks, timeouts, system writes are all rulled out. I agree that the data in table created by the query must contain something corrupt.

If I open and close the file using Paradox main menu file->open->table, and then run the exportSpreadsheet function the resultant excel file is OK. This open and close table function must be clearing up the corruption somehow, but I don't know what it's doing or how to prevent the problem in the first place. Do I need to do anything after an executeQBEString to close off the ansTbl correctly? Any other suggestions?

Thanks for your continued patience.

Mark