Thanks for the code Peter..
I stopped using automation in my production apps years ago since
every time a user changed versions, my app broke and it was always
"my fault" that they upgraded and the automation code no longer
worked and they wanted me to fix it for free..
It's good to know there's an automated way to do this in case I am
ever crazy enough to back to automation.
Regards-
Steve
At 04:23 PM 09/17/2009, Peter Hart wrote:
>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
>
>
[excessive quoting removed by server]
_______________________________________________
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/[email protected]
** 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.
