Question Excel Export as Text

[Phillip]

Hi All, I need help with an Excel Export. I'm trying to export a column as text. I need FedEx shipping numbers in the column which have a leading "0" that excel keeps deleting when opened. I used to export as a .dat file, format that column as text, and then save as an excel worksheet. I want to have it automatically format as text so that I can eventually have our website grab from that file at a specific time with no user input.​

​

Here's the code. Everything runs off fine except that I want that column formatted as text. I changed the runoffs to "INFORMATION#" as they are long lines of IF commands to run off my fields. The one in question is in bold:​

​

DEF VAR vchExcel AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkBook AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkSheet AS COM-HANDLE NO-UNDO.
DEF VAR vRow AS INT NO-UNDO.
CREATE "Excel.Application":U vchExcel.

ASSIGN
vchExcel:VISIBLE = false
vchWorkBook = vchExcel:WorkBooks:ADD()
vchWorkSheet = vchExcel:Sheets:ITEM(1).


FOR EACH tracknum WHERE ord-num BEGINS "W" AND s-date = TODAY - 1 AND track-ref > "0" NO-LOCK:
ASSIGN
vRow = vRow + 1
vchWorkSheet:Range("A":U + STRING(vRow)):VALUE = INFORMATION1
vchWorkSheet:Range("B":U + STRING(vRow)):VALUE = INFORMATION2
vchWorkSheet:Range("C":U + STRING(vRow)):VALUE = INFORMATION3
vchWorkSheet:Range("D":U + STRING(vRow)):VALUE = IF gcustom.tracknum.track-ref > "0" THEN gcustom.tracknum.track-ref ELSE ""
vchWorkSheet:Range("E":U + STRING(vRow)):VALUE = INFORMATION5.
END.


vchWorkBook:SaveAs("\\nas\company\IT\WebTracking\TODAY.xls",,,,,,,).

/* RELEASE OBJECT vchWorkSheet. */
/* RELEASE OBJECT vchWorkBook. */
/* RELEASE OBJECT vchExcel. */

vchWorkBook:CLOSE.
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.
vchExcel:QUIT.
RELEASE OBJECT vchExcel.

thank you for the help in advance.​

 

[Stefan]

Does adding a single quote, to indicate text help?

If not, a relatively easy way to have full control over your data types, is to create the Excel sheet in Excel XML 2003 format. For getting started save a simple sheet in this format and keep removing optional data while checking if the sheet still opens properly. The format is documented on msdn.

 

[Phillip]

I got it with this code:

vchWorksheet:COLUMNS("D"):NumberFormat = "@".

The single quote worked but it still keeps that as a value in the table. When I push the excel table to our web server it would display the tracking numbers as '########. The above quote formatted it so that it would display leading 0s.

 

[Suga]

Hi All,

I have a similar problem in exporting to Excel as a Text. When I export the database field value to excel column, it drops the leading 0's which I really need for using of another import program.

I tried it with using STRING function to make the variable to be String before it goes to export.

It did not work

Is there any other way to export with leading 0s.

Desperate !!!!

Any ideas will be appreciated.

Suga

 

[Rob Fitzpatrick]

It did not work

Is there any other way to export with leading 0s.

Other than... ?

Please post your code (in CODE tags) so we know what you tried.

 

[Cringer]

I suspect you are actually exporting to csv, not to excel. Excel deals badly with csv files and you lose the leading 0. Don't use Excel to open csv files.