Rafael, Thanks again for your feedback. I actually applied suggestions by others - and largely got things working. And, yes, once I had NULL in a field - it would simply output as Blank in Excel - which is Exactly what I was looking for. So - yes, the use of NULLS for this project works - giving the users what they actually wanted.
Regards, Kurt Wendt Senior Systems Analyst Tel. +1-212-747-9100 www.GlobeTax.com -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of rafael copquin Sent: Thursday, June 16, 2016 7:32 AM To: [email protected] Subject: Re: A Blank Value for a Number? I felt I did not understand the issue but replied anyways. However, check the SET NULL command in help. If ON it will allow you to store a null value. Also you can create a cursor with fields that accept null values thus: create cursor blabla(number I null,desc C(10)) append blank replace number with null, desc with 'null value' browse will show null in the number column and 'null value' in the desc column Then, when you save the cursor as an excel file _perhaps_ you will have a null value in the applicable cell (_this I did not try_) Rafael On 15/06/2016 9:57, Kurt Wendt wrote: > Rafael - its quite the reverse. Its manipulating data in VFP - then simply > Exporting the data to an Excel file using COPY TO command. > > Regards, > Kurt Wendt > Senior Systems Analyst > > > Tel. +1-212-747-9100 > www.GlobeTax.com > > > -----Original Message----- > From: ProfoxTech [mailto:[email protected]] On Behalf Of > rafael copquin > Sent: Tuesday, June 14, 2016 6:27 PM > To: [email protected] > Subject: Re: A Blank Value for a Number? > > I'm not sure I understand your problem, but I often have to read an > Excel file and pass the data to a table. Sometimes there is no value > in a cell that should have a certain value (character or numeric) > > Reading it line by line in those cases Excel sends a NULL value. > > I just do something like this: > > cFile = 'c:\exceldata\myexcelfile.xls' > > this.oExcel=createobject("Excel.Application") > > With this.oExcel > > .Workbooks.Open("&cFile") > > .Range("A27").Select > > oRange = .Range("A27:L27") > > Do while .t. > > with oRange > > cDescription = Nvl(Alltrim(.Columns[3].value),'') > > etc., etc. && read more cells into ad hoc variables > > endwith > > oRange = oRange.Offset(1,0) && jump to next > line on Excel sheet > > EndDo > > and then it is a simple thing to replace the dbf DESCRIP field with > cDescription > > I re read your post and see that you want to blank certain cells > automatically. Following the above technique it should be an easy > thing to do. Just look at my example with the cDescription variable. > The nvl function will give it a value of '' if it contains a null > value, or the actual value if it is not null > > > Rafael Copquin > > > On 14/06/2016 17:02, Kurt Wendt wrote: >> I know - it's a Crazy request. Trying to figure out the Easiest way to try >> and do this - and hoping to Avoid having to do more complicated solutions >> like Office Automation to manipulate an Excel file. >> >> So - here's the deal. This system I work on - it outputs this Excel file. >> And, in the past - if there are certain records that have a Zero in a >> particular column - they would Manually wipe out the Zero - and also blank >> out several of the other fields in that Row. >> >> But, the new request is to make those things happen automatically during >> process when the Excel file gets exported. So that they do NOT have to >> manually blank out certain fields. But, of course, therein lies the problem. >> For the other rows of data - this particular field is supposed to be a >> numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor >> that has a Numeric field - and yet have blank values in it. >> >> As such, I suspect the answer is that this can't be done - to have a numeric >> data field hold any kind of blank data. They even suggested just a Dash. >> But, again - I figured it's not possible. However, I figured for the hell of >> it - I would post here - in case someone had an interesting insight that I >> hadn't thought of... >> >> Kurt Wendt >> Senior Systems Analyst >> [GlobeTax_SecondaryLogo_TwoColor_Lo]<https://www.globetax.com/> >> One New York Plaza - 34th Floor >> New York, NY 10004-1936, USA >> Tel. +1-212-747-9100 >> Direct +1-212-405-8246 >> www.GlobeTax.com<https://www.globetax.com/> >> >> GlobeTax does not provide or offer, and this is not, tax, investment or >> legal advice. This email and any attachments hereto is intended only for use >> by the addressee(s) and may contain confidential information. If you are not >> the intended recipient of this email, please immediately notify the sender >> at +1-212-747-9100 and permanently delete the original and any copies of >> this email. >> >> >> >> --- StripMime Report -- processed MIME parts --- multipart/related >> multipart/alternative >> text/plain (text body -- kept) >> text/html >> image/png >> --- >> [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/80838f1ca795b14ea1af48659f35166f2bd...@drexch02.corp.globetax.com ** 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.
