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/[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.
