export memo field to excel

[codetyko]

Firstly, Is there a way to export fpw26a memo field to excel? It seems that the command to insert the memo field is ignored despite other fields being correctly created in xls format.

Secondly, Let's say I've created a blank excel sheet with the headers and nicely formatted columns. How do I append fields from dbf to this worksheet rather than having to create a new sheet everytime? This is the first time I'm trying to do this so I need feedback from those who has been there to avoid wasting my time trying to achieve something that is not allowed BY DESIGN.

 

[rgbean]

While I have seen FPW filling an Excel worksheet using DDE, this is really not that easy to do, and may vary depending on the version of Excel present. Have you considered upgrading to VFP? Using automation under VFP is quite straight forward and many examples exist. When you try to mix the 16-bit technology of FPW, with the 32-bit technology of Excel, you only complicate things for yourself.

Rick

P.S. I'll try to find the DDE code (I believe it's zipped up in some archived directory), IF you really want it.

 

[JRB-Bldr]

I am sure than one of the other people might have a more
elegant method for doing this, but I have run a pre-export
function which parsed the Memo field data into fields in a
temporary table and combined this information with
non-Memo field data.

I then exported the temporary table to Excel with good
results. One problem that I did encounter is that
if there were both text data and numeric data, the
numeric data was most often converted to text within
the Excel file.

Good Luck,
jrbbldr
jrbbldr@yahoo.com

 

[rgbean]

Well, I found the FPW / Excel DDE code, I'll show it just as I got it.

* DDEXL1.PRG
*
* This simple program shows the basic steps
* needed for FoxPro to access another application
* acting as a DDE server. To see how it works,
* single step through the code, reading the comments
* as you go. It shows the use of DDEInitiate,
* DDERequest, DDEPoke, DDETerminate and DDELastError.

****************************************************

* The first thing we have to do is start a
* conversation with the server. In this case,
* we're going to "talk" to Microsoft Excel.

* To do this we start or "initiate" the conversation
* by opening up a "channel" to Excel. (Note, if
* Excel doesn't start on the next step, then either
* start it manually and retry, or place it in your
* MS-DOS path before starting Windows.)

channel = DDEInitiate("Excel", "Sheet1&quot
? "Channel is:", channel

* This command will try and start Excel if it isn't
* already running and then tell it that it wants to
* talk about "Sheet1". This is called the "topic"
* of the conversation. Every conversaction is
* about only one topic. Only certain topics are
* understood by the server and only the server
* documentation can tell you what those are. In
* the case of Excel, we are asking to talk about
* a spreadsheet called "Sheet1" -- the default
* empty spreadsheet that Excel opens.

* Let's ask Excel what is in cell R1C1 of the Sheet1.
* We do this with the DDERequest command, like so:

? &quot;<&quot; + DDERequest(channel, &quot;R1C1&quot + &quot;>&quot;

* Notice how the cell is empty. If we switch over
* to Excel and put something in the cell, we can
* try again.

? &quot;<&quot; + DDERequest(channel, &quot;R1C1&quot + &quot;>&quot;

* This time we get the value of the cell. Notice
* how this is returned as a string. Everything
* returned by a simple DDERequest like this will be
* a string, so if you're expecting numbers, you'll
* have to convert them into numbers explicitly.

* In the simple DDERequests above, the second
* parameter passed to the function is called the
* &quot;Item&quot;. The Item defines what this part of the
* conversation is about. Remember that we already
* know that the main topic of conversation is
* &quot;Sheet1&quot;. The Item is a sub-topic within
* the main topic.

* Another DDE command that Excel understands in a
* conversation about a spreadsheet is the &quot;Poke&quot;
* command. The DDEPoke command below tells Excel
* &quot;Here, take this value and do something with it&quot;.
* We provide an Item to help clarify exactly what
* to do with it. Thus:

? DDEPoke(channel, &quot;R1C2&quot;, &quot;2&quot

* will place the value 2 into cell R1C2. The &quot;R1C1&quot;
* parameter is still called the &quot;Item&quot;. The &quot;2&quot;
* parameter is called the &quot;Data&quot;. Note that it is
* string value. Just as DDERequest only returns
* strings, DDEPoke only sends strings.

* We can also 'poke' a formula into a cell, e.g.

? DDEPoke(channel, &quot;R1C3&quot;, &quot;=A1+B1&quot

* (Note that the Topic always uses RC notation, but
* the Data uses whatever mode Excel is currently in.)
* Switching to Excel shows that this works. In
* fact, we can now retrieve the summed cells from
* Excel using:

? &quot;<&quot; + DDERequest(channel, &quot;R1C3&quot + &quot;>&quot;

* When we're finished with a conversation, we can
* end it by using the &quot;DDETerminate&quot; function. I.e.

? DDETerminate(channel)

* Notice how all the DDE commands are actually
* functions. I.e., they all return a value.
* Typically this value will be .T. If an error
* occurs, then .F. will be returned. You can then
* use the DDELastError function to determine why it
* failed. For example, if I now try and Poke to
* the channel I just closed:

? DDEPoke(channel, &quot;R1C3&quot;, &quot;=A1+B1&quot

* I get .F. returned. By checking DDELastError

? DDELastError()

* and looking up the help entry for it, you can see
* what went wrong.

Rick