Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cyn Koubeck
  Where is Cyn Koubeck?
 
 Ohio - United States
 Cyn Koubeck
 To: Yuri Rubinov
  Where is Yuri Rubinov?
 Philadelphia
 Pennsylvania - United States
 Yuri Rubinov
 Tags
Subject: RE: suppress zeros in excel
Thread ID: 15637 Message ID: 15698 # Views: 4 # Ratings: 0
Version: Unknown Category: General VFP Topics
Date: Friday, September 27, 2002 3:05:54 PM         
   


Yuri,
Thanks! I'd never tried "datatoclip" before. I suppose what I'm trying to say is - how can I show a blank cell in excel if the val of the $ =0.00? Unfortunately, I can't specify a single cell as I'm taking a raw data report from the mainframe (delimited by ^) appending it into a table, converting the characters to numeric as necessary. Then when the table is "clean", convert it into excel, with all the formatting - bold column headers, etc. It's when it gets to excel that the data in the cells show the zero in the currency columns. Zeros are ok in the other columns.

The following is what I use to convert a table into excel: ( a shortened version of this program follows:)

lcTableAlias = ALIAS()
*!* The following code determines the derived Excel file name and location. *!*
lcTablePath = LEFT(DBF(), RAT("\", DBF()))
lcExcelFile = lcTablePath + lcTableAlias + ".xls"
ERASE (lcExcelFile) && Erase the existing file *!*


*!* The following code will erase any previously created temporary excel *!*
*!* file created by this program *!*
erase c:\myfox\inv_track\tempexcel2.xls
*!* The following code creates the temporary Excel file that will be used *!*
*!* for the derived Excel file *!*
copy fields br,beg_date,end_date,rc_05,rc_06,rc_10,rc_11,rc_20,rc_25,rc_26,rc_27,rc_35,rc_40,rc_41,rc_42,rc_43,rc_44,tot_rc_amt,dbt_memos,open_dmemo,lost_prod,rej_amt to c:\myfox\inv_track\tempexcel2.xls TYPE XL5

*!* The following code commences the OLE Automation process. *!*
oExcelObject = CREATEOBJECT('Excel.Application')

*!* The following code opens the "VFP_to_Excel" file that was created by the *!*
*!* "COPY TO" command *!*
oExcelWorkbook = ;
oExcelObject.Application.Workbooks.Open("c:\myfox\inv_track\tempexcel2")
*!* The following code activates the Worksheet which contains the "COPY TO" *!*
*!* data *!*
oActiveExcelSheet = oExcelWorkbook.Worksheets("tempexcel2").Activate
*!* The following code establishes an Object Reference to the "VFP_to_Excel" *!*
*!* worksheet *!*
oExcelSheet = oExcelWorkbook.Worksheets("tempexcel2")



#DEFINE RPT_FONTNAME "Ariel MS"
#DEFINE RPT_FONTSIZE 12
#DEFINE xlsum -4157

m.end1=m.end1+4

m.end="m"+ltrim(str(m.end1))

with oExcelObject


WITH .Range("A1:v1")
.delete
endwith


WITH .Range("A1:v4")
.insert
endwith
with .sheets(1)
.select
.name = m.br
endwith

WITH .Range("A1")
.Value = "INV_TRACK_EXCEL"
WITH .Font
.BOLD = .T.
.Size = 14

ENDWITH
ENDWITH

with .range("a2")
.value = "=now()"
.numberformat = "m/d/yy"
endwith


WITH .Range("A3")
.Value = "ILS Location: "+m.br

ENDWITH


.Range("A5").Value = "Br. #"
.Range("B5").Value = "Begin Date"
.Range("C5").Value = "End Date"
.Range("D5").Value = "RC 05"
.Range("E5").Value = "RC 06"
lcRange = "A5:V5"

WITH .Range(lcRange)
.Font.Bold = .T.
.Font.Size = RPT_FONTSIZE
.Font.Name = RPT_FONTNAME
ENDWITH


lcRange = 'd1:d'+ltrim(str(m.end1))
WITH .Range(lcRange)
.NumberFormat = "#,##0_);[Red](#,##0)"
ENDWITH
lcRange = 'e1:e'+ltrim(str(m.end1))
WITH .Range(lcRange)
.NumberFormat = "#,##0_);[Red](#,##0)"
ENDWITH
lcRange = 'u1:u'+ltrim(str(m.end1))
WITH .Range(lcRange)
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ENDWITH
lcRange = 'v1:v'+ltrim(str(m.end1))
WITH .Range(lcRange)
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ENDWITH
.Cells(m.end1 + 3, 1).Value = "Totals"

WITH .Cells(m.end1 + 3, 4)
.Value = "=SUM(d5:d" + ALLT(STR(m.end1-1)) + ")"
.NumberFormat = "#,##0_);[Red](#,##0)"
ENDWITH
WITH .Cells(m.end1 + 3, 5)
.Value = "=SUM(e5:e" + ALLT(STR(m.end1-1)) + ")"
.NumberFormat = "#,##0_);[Red](#,##0)"
ENDWITH
WITH .Cells(m.end1 + 3, 21)
.Value = "=SUM(u5:u" + ALLT(STR(m.end1-1)) + ")"
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ENDWITH
WITH .Cells(m.end1 + 3, 22)
.Value = "=SUM(v5:v" + ALLT(STR(m.end1-1)) + ")"
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ENDWITH



Your program worked, but I'm unclear how to incorporate it into this program. Is there a way to get a range of cells ( or any cell) that the currency cell =0 without showing $0.00?

Again, many thanks in advance!!!
Cyn

ENTIRE THREAD

suppress zeros in excel Posted by Cyn Koubeck @ 9/26/2002 5:11:14 PM
RE: suppress zeros in excel Posted by Yuri Rubinov @ 9/26/2002 7:26:12 PM
RE: suppress zeros in excel Posted by Cyn Koubeck @ 9/27/2002 3:05:54 PM
RE: suppress zeros in excel Posted by Yuri Rubinov @ 9/27/2002 8:01:47 PM
RE: Speaking of Excel - Animal abuse Posted by Patrick Dowd @ 9/27/2002 8:30:56 PM
Suppressing Zero fields in Excel Posted by Simon Arnold @ 9/28/2002 12:54:38 AM
RE: Suppressing Zero fields in Excel Posted by Cyn Koubeck @ 10/4/2002 8:26:10 PM
RE: Suppressing Zero fields in Excel Posted by Jill Lindgren @ 4/8/2006 11:31:00 PM
RE: Suppressing Zero fields in Excel Posted by Simon Arnold @ 4/10/2006 12:37:23 PM