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


Try this:

objexcel.columns("B:B").NumberFormat ="$#,##0.00_);[Red]($#,##0.00);;@"

> 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