Calling the Excel .OPENTEXT() method from with VFP

I'm having fun and games with a problem that appears in some places in my code but not in others.

At several points in my software I have a control button that outputs the current display(s) to Excel. This works by

(1)  outputting the fields and records from the current display to a CSV file;

(2)  opening Excel (or another workbook with an already-open Excel); and

(3)  importing the contents of the CSV file using the Excel .OpenText method.

OK, "why are you using a CSV file?" I hear you ask. Well, I have another routine that opens the Excel and writes the current display across cell-by-cell. And it is slow, slow, slow. I found that the intermediate CSV file was much quicker.

And using a CSV file means that I can add a lot of headers, column headings and a footer - in a way that I wouldn't be able to do if I were to use other formats for the intermediate file.

Anyway, most of the time this works fine.

But I have one display where it fails. It works OK if I'm running within VFP - but fails in the compiled version of the code. The error message is

    OLE error code 0x80004005: Unspecified error.

The line of code where it fails is

    WITH oExcel
        .Workbooks.OpenText( lfCSV, A_STRING, @lzColumns )


    lfCSV is the name of the CSV file that is to be imported

    A_STRING is a pre-processor constant, set to

        #define A_STRING            2,1,1,1,.F.,.F.,.F.,.T.,.F.,.F.,[]

        This deals with a whole series of constants that have to be passed to the .OPENTEXT() method

    lZCOLUMNS is an Nx2 array, where N is the number of columns that are being passed. First column of this contains a column number; the second contains the conversion code, which is always 1 (='General'). So a simple case would be

        lZColumns[1,1] = 1
            [1,2] = 1
            [2,1] = 2
            [2,2] = 1
            [3,1] = 3
            [3,2] = 1

Any suggestions?

I'm using VFP9. I'm running here under Windows 8 (alas).
Who is Participating?
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
Since Office 2007 you get an early reposonse from creating the Application OLE Server Excel.Application (also true for Word, Outlook, etc).

That means while you already have an oExcel reference, the object is no finished and addressing any property or method often works, sometimes not.

Try this:
oExcel = CREATEOBJECT("Excel.Application")

The DOEVENTS with FORCE clause forces Windows to process any pending events, which causes the Excel server to be completed. Afterwards it's safe to use.

I can't guarantee this is the solution, but it's well worth tying, this is something I used successfully since Office 2007 on Vista, I doubt it's a new Win8 problem.

If I were you I'd also log the values of the parameters to make double sure the call to Excel is correct. The OpenText parameterization seems okay, you open a CSV text with comma separated values this way.

The lzColumns array should be an array of arrays, by the VBA reference, but the inner arrays are 2 element arrays, that should be okay as is, considering it works mostly. You might try out COMARRAY, but I'm quite confident DOEVENTS FORCE will help.

Bye, Olaf.
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
Do you have the full address of the file when creating it? Maybe when it's running in compiled mode it has another current directory or the directory is not valid.

Why not create an HTML file instead and rename it to xls and open it in Excel? It can be formatted and extremely fast.
IainMacbAuthor Commented:
Yes, full address of file is in use.

Unsure how I go about creating an HTML file instead. Does this involve using REPORT FORM?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

CaptainCyrilConnect With a Mentor Founder, Software Engineer, Data ScientistCommented:
cHTML = '<html>'
cHTML = cHTML + '<table>'
    cHTML = cHTML + '<tr style="background:#C0C0C0">'
    cHTML = cHTML + '<th>Id</th>'
    cHTML = cHTML + '<th>Name</th>'
    cHTML = cHTML + '</tr>'
SCAN table
    cHTML = cHTML + '<tr>'
    cHTML = cHTML + '<td>' + TRANSFORM( + '</td>'
    cHTML = cHTML + '<td>' + ALLTRIM( + '</td>'
    cHTML = cHTML + '</tr>'
cHTML = cHTML + '</table>'
cHTML = cHTML +  '</html>'
= STRTOFILE(cHTML,'myhtml.xls')

You can use style or font or <b> or whatever is in basic HTML to format the table. This is the fastest way to export formatted reports.
IainMacbAuthor Commented:
Olaf's recommendation of including DOEVENTS FORCE makes this work properly. I inserted this in front of the problematic line in the code and suddenly everything works!

Meanwhile, Captain Cyril's suggestion of output to an HTML file looks as if it might be a better long-term change.

Many thanks to both of you.
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
There is a small mistake in my code
SELECT table
IainMacbAuthor Commented:
I twigged that - though I did look it up in the help text to see if it was a feature that I'd missed in the past. :-)
IainMacbAuthor Commented:
I've spent a bit of time in looking at Captain Cyril's suggestion of using an intermediate file in HTML format.

It's a neat solution, but has one problem that means that I'm likely to stick with the CSV file. The data that I have to work with is full of numeric codes, some of which have leading spaces and/or zeroes. When the data is imported into Excel from the HTML file, then I lose these important characters. With a CSV file, I get the opportunity to specify some formatting information to Excel - so I can force it to treat these columns as containing text rather than numbers.

I've searched for a solution (and there's a lot of people out there asking the question); the best one that I can find is to add some non-numeric character to the output so as to force Excel to treat the column as text, the best one being to add "&nbsp;" to the right-hand end of the numeric code. This adds a non-breaking space, CHR(160). That works (in terms of preserving the leading spaces/zeroes) but it's going to confuse any subsequent look-ups within the Excel environment.

So I'm minded to stick with my rather old-fashioned use of an intermediate file in CSV format.

Oh Mr Gates, why can you not understand that there's a difference between a number and a text string that only contains digits and spaces? My view is that a number has arithmetic meaning. Can I add part number "0123456" to part number " 345" and get part number "123001"? I think not!
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
I believe there are Office HTML tags that configure those things. Here is one which I use often:

<br style="mso-data-placement:same-cell;">

It adds a line break inside the same cell.

You can also save the Excel sheet as an HTML file and open it with am Editor and read what's written inside.
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
IainMacbAuthor Commented:
I put a timer into the routine, and got some surprising results.

For outputting a fairly large one of my displays, 13 columns x 8544 rows:

- using a CSV file 2.4 seconds from click the [Output] button to display the Excel
- using an HTML file 28.6 seconds

That seemed rather a drastic difference - and I couldn't see where the difference was coming from within the code.

So I split the timer into the time taken to output the intermediate file and the time taken to display the Excel. That gave

- CSV file : 1.0 seconds for output plus 1.6 seconds for Excel display
- HTML file : 1.4 + 27.0

So, not much difference at 'my' end, where it's my code that's doing the output. But a huge difference at the Excel end, where reading a CSV file seems to be an order of magnitide faster than an HTML one. I can only assume that Excel is taking its time to interpret the contents of each of the 13 x 8544 table cells.
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
CSV opens much faster than HTML cause HTML can contain a whole lot of formatting. CSV does not have any formatting.

When exporting in HTML with enough CSS attributes, the user does no need to change anything whereas he has to adjust font and column width in CSV which takes some time :).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.