Calling the Excel .OPENTEXT() method from with VFP
Posted on 2014-01-26
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
.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
I'm using VFP9. I'm running here under Windows 8 (alas).