Hi Steve
Programmatically Use OLE to open the workbook
Create Variable for workbook
Local lcFileName
lcFileName = Path + Filename
oleExcel = CreateObject("Excel.Application")
Open the Previously copied workbook
oleExcel.Workbooks.Add(lcFileName)
Switch off display zeroes for this workbook
oleExcel.ActiveWindow.DisplayZeros = .F.
Save Workbook
oleExcel.ActiveWorkbook.SaveAs(lcFileName)
Close Excel
oleExcel.Quit
Above code is for Excel 2007 but might work OK with 2003 and earlier.
Easiest way to get the code is open Excel, Start recording a macro after
doing all you want stop recording macro and the edit it. Copy code into
VFP and correct the syntax.
Other way is to open the sheet in excel and change the options to not
display zero's.
Help Blank if zero should give you the correct way for the version of
Excel you are using.
Be aware that there can be a problem with excel if you save a changed
sheet using OLE as it may prompt with a save as dialogue.
Cheers
Peter
Peter Hart Computers
On Behalf Of Steve Ellenoff
Sent: 17 September 2009 21:01
Subject: How to hide 0 values for exported VFP data in Excel and
stillsum the column
I have an app that exports a bunch of data to excel using the COPY TO
XL5 command. The client recently asked if the numeric fields could be
blank when the value was zero for easy reading. I couldn't find any
easy way to do this in Excel through formatting... so I changed the
exported data to a char field to accommodate this, but now they are
not happy that they can no longer use the =SUM() on the column.
I told them I could add a numeric version of the column near the end
for totaling, but they really don't love this solution.
Is there a better way to handle this?
Thanks for all suggestions
-Steve
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/a57fa4cf19531343a2ee11b57db8e3af04c...@server.peterhartcomputers.local
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
