Solved

Calling the Excel .OPENTEXT() method from with VFP

Posted on 2014-01-26
12
1,055 Views
Last Modified: 2014-01-29
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 )
        ...
        ENDWITH

where

    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).
0
Comment
Question by:IainMacb
  • 6
  • 5
12 Comments
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
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.
0
 
LVL 1

Author Comment

by:IainMacb
Comment Utility
Yes, full address of file is in use.

Unsure how I go about creating an HTML file instead. Does this involve using REPORT FORM?
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 350 total points
Comment Utility
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")
DOEVENTS FORCE

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.
0
 
LVL 27

Assisted Solution

by:CaptainCyril
CaptainCyril earned 150 total points
Comment Utility
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(table.id) + '</td>'
    cHTML = cHTML + '<td>' + ALLTRIM(table.name) + '</td>'
    cHTML = cHTML + '</tr>'
ENDSCAN
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.
0
 
LVL 1

Author Closing Comment

by:IainMacb
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
There is a small mistake in my code
SELECT table
SCAN ALL
...
ENDSCAN
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:IainMacb
Comment Utility
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. :-)
0
 
LVL 1

Author Comment

by:IainMacb
Comment Utility
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!
0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
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.

http://msdn.microsoft.com/en-us/library/Aa155477

You can also save the Excel sheet as an HTML file and open it with am Editor and read what's written inside.
0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
0
 
LVL 1

Author Comment

by:IainMacb
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:CaptainCyril
Comment Utility
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 :).
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now