Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass
 Tags
Subject: VFP Copy to xxxxx.xls Type xls "BIG TROUBLE"
Thread ID: 9939 Message ID: 9939 # Views: 32 # Ratings: 0
Version: Unknown Category: General VFP Topics
Date: Tuesday, April 16, 2002 9:00:40 PM         
   


Hi group... got a BIG PROBLEM ?

I have already spent over 1 week trying to get the activeX control for Excel to
work in VFP. Ken and I tried this and we both ran into a dead end.

Then I said I will go a different route completely and hand code everything and
then copy the preped file to an xls file format.

I have had to write a VFP application to read in a flat achii file "delimeted character |"
into a VFP table. The VFP table is all character fields, as the columns vary and no way
to know which ones will be character and which ones will be numeric.
This is a messy file and I have to add character headings in various location and I
do this from within the VFP table. A lot of parsing also takes place.

One this is all done, I copy out to an xls file with the:
COPY TO MYFILE.XLS TYPE XLS

Now comes my problem. The numeric fields that end up in the Excel spreadsheet
are treated as character based and when I try to perform say a Sum on a range
in Excel it will not work. Also, if I try to select a range and format them as numeric
this will not work either, they stay as character. I can use the =VALUE(A1) in another
cell to force the value of A1 to numeric. I have to do this with every cell in the range and
then I can use math functions on the new range created with the VALUE function.
Unfortunately, the final xls file has 36 columns and ranges split up in various locations
that have to be numeric.
Since the raw source delimited file changes everyday, where the headings appear; I am
totally stumped on how to proceed with this automation.

Has anyone got any idea how to accomplish this back in the VFP table? I don't see
how! This is a unique problem with column headings on row1 being character and
the data in the same column below the heading being numeric. Due to the xls column
structure I cannot apply a macro from within the xls file.

The structure of the file goes something like this:

Character Header Heading
12.55
Character Header Summary (This may be anywhere from 4-5 records up to 199)
199.00
1200.00
1315.85
1460.00
Character Header Details (This can be any length from 1 to 2000-3000 records)
.45
.55
.69
1.02
1.90
3.02

The character headers are populated into the VFP table via 3 free tables holding
all the Header names for all three areas of the spreadsheet. The logic of this is
quite simple and works 100%.

You can see what I am talking about the number of rows under the Summary can
change with every new txt file. The detail rows can change as well with every different
txt file. All the numeric values under each character header are seen as character values
and cannot be changed with the Excel range format command.
To make matters worse the source txt file comes from a Hydro supplier and cannot be
changed. My final xls file is imported into a Hydro monitoring system that cannot be changed.
I am trying to automate the process in the middle.

I have looked at Simon's Excel Class, but do not see anyway to apply this and automatically
convert using VFP manipulating the xls file.

What I really need is some way back in VFP to somehow force certain fields and records
to be recognized as numeric if this is even possible. Or a class that will allow me to
apply the Value function to a range of cells, copying them to a different range location
within the spreadsheet.

Pete from the Great White North ! (Only in Canada, ay...) Over and Out ...


COMPLETE THREAD

VFP Copy to xxxxx.xls Type xls "BIG TROUBLE" Posted by Pete Sass @ 4/16/2002 9:00:40 PM
RE: VFP Copy to xxxxx.xls Type xls "BIG TROUBLE" Posted by Ken Blum @ 4/16/2002 9:23:39 PM
RE: VFP Copy to xxxxx.xls Type xls "BIG TROUBLE" Posted by IvanDj @ 4/17/2002 9:09:39 AM
RE: VFP Copy to xxxxx.xls Type xls "BIG TROUBLE" Posted by Pete Sass @ 4/17/2002 2:56:16 PM
You must pound on my head a little harder... Posted by Ken Blum @ 4/17/2002 8:14:24 PM