Creating Excel application 3

[Jerim65]

I am using

oExcel = Createobject("Excel.Application")

in my application.

An xls file is created but when trying to open it in Excel 2007 I get a warning that the file maybe corrupted etc.

It does open correctly though when ignoring the warning.

As I am automating the opening of the created xls file this warning is not welcome.

Why is this happening please? Is there any solution.

Thanks

Coldan

 

[bborissov]

Try with:

oExcel = Createobject("Excel.Application")

oExcel.DisplayAlerts = .f.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Jerim65]

Thanks Boris,

However I am using

   ShellExecute(0,cAction ,tcfilenamepath,"","",1)

in my application to open the spreadsheet.

Further, I now find that a spreadsheet created this way is not correctly treated by the import code into a table that I have created.

tcimportfile is the selected xls file.

   Append From (tcimportfile ) Type Xls

In my app I am trying to create a 'black box' that will import from csv or xls and export to csv or xls.

The csv parts operate correctly.

Any thoughts?

Coldan

 

[Mike Lewis]

Coldan,

You've go two separate problems.

First, when you open the file via ShellExecute(), make sure it is not still open in the instance of Excel that you created with CREATEOBJECT().

Within your Automation session (that is, the code that works with CREATEOBJECT() and oExcel), close the file when you have finished with it (and, ideally, quit Excel). Don't execute ShellExecute() until you've done that.

The APPEND FROM issue is a separate problem. You say the file is "not correctly treated". There are many possible reasons for that, but, in general, APPEND FROM will only work the way you expect if the Excel data is reasonably tabular, that is, if it consists of rows and columns, and also if there is a reasonable consistency in the data types.

Perhaps you could let us know in what way it is "not correctly treated".

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Jerim65]

Mike,

The new Excel worksheet was closed prior to opening via ShellExecute(). Viz

oExcel.ActiveWorkBook.SaveAs(myfile)
oExcel.DisplayAlerts = .f.
oExcel.ActiveWorkBook.Close
oExcel.Quit
[code]

so that's not the problem.

The 'append from' of the previously created xls file had no information in my 'bucket' table.

Before I release my app I need to be able to re-import xls files created via the 

oExcel = Createobject("Excel.Application") code.

My 'black box' provides processing of the data fields in the timeline table. So Import and Export must work perfectly.

The table is always 4 fields N.N.N and a Memo. 

Thanks

Coldan

 

[Olaf Doschke]

There you have your memo problem again, you cannot import memo fields with APPEND, also APPEND does only work on old excel format.

You better not make use of COPY TO and APPEND FROM or IMPORT, but also reimport xls via Excel automation, there is no way around this unless you revert to Excel 8, which is Excel 97.

If you not only need to write out to xls but also read them back in you better use Excel via OleDB. That is one solution working both ways.

This post of Cetin contains both reading and writing xls via OleDB, more precise you need the parts of Cetins post working via Microsoft.ACE.OLEDB.12.0

http://www.foxite.com/archives/vista-excel-compatibility-w-worksheets-0000153776.htm

Actually Cetin's export to Excel is not making use of the Excel OleDB Provider, but of VFPs, Excel is importing a dbf via VFPs OleDB Provider, so that would need to be installed.
I think it's not needed this way, as Microsoft.ACE.OLEDB.12.0 like VFPs OleDB Provider is able to read and write to XLS sheets.

Bye, Olaf.

 

[Jerim65]

Olaf,

Thanks for the link to the Foxite thread.

Before I test it can you tell me how to install the OleDB provider such that It can be included in my Setup file I use to distribute my application.

Obviously my simple 'black box' idea is fraught with difficulties - the process that I am stumbling over starts with a timeline(s) in a dbf format created by a number of individuals over the last 10 years.

My app should be able to read them in individually, allow any processing the user might want to do ( those facilities are already working in my program) and write out to xls,dbf or csv whichever the user might want to do without any loss of information within the timeline.

However,some users might prefer to make changes in an xls or csv format file instead of within my application and then using my utility recreate the dbf needed to use them in the 3rd party application my utility works with.

Regards

Coldan

 

[Jerim65]

After further experimentation - testing the re-import of an xls exported from my app.

Instead of using the ShellExecute() function to open the spreadsheet after creating the new xls, I can give the user the same experience by leaving Excel open after creation

oExcel.ActiveWorkBook.SaveAs(myfile)
oExcel.DisplayAlerts = .f.

- so this prevents the initial opening error msg.

Now for correct re-import using the 'append to xls' a

Save As to a Office 95 format works perfectly for import.

How can I 'save as' my Excel object to this format before it opens?

Regards

Coldan

oExcel.ActiveWorkBook.SaveAs(myfile)

 

[Olaf Doschke]

use intellisense at the command window, and you'll see, that a file format constant is another parameter of SaveAs().

Actually it's the second parameter.

See

http://www.convert-files.com/SII/Co...rhid_excel_conversion_file_type_constants.htm

You may try 43 for Excel95/97 XLS, but others before you have come to the conclusion this doesn't work and doesn't create 100% compatible files.

Even if it does you still have the problem, that memo fields are not appended to, like the other way around, so you CAN'T use APPEND anyway.

If APPEND works for you, you don't have memo fields in the import table/cursor. I then wonder, why you need memo fields in the initial tables, if the data never exceeds C(254)

Bye, Olaf.

 

[Jerim65]

Hi Olaf,

Thanks once again for your great information.

There are some givens - the 3rd party app requires N,N,N,Memo to operate it's own usage of the timeline tables (tt) internally. I have no say in that.

My app initially only allowed creation of timelines from 3rd party app data - then it growed and growed.

Next a user asked me to provde import of an xls file into my app and create a tt fom it - this I did and added csv import.

Tt tables are also distributed with the 3rd party app and I though it a good idea to export these to xls and csv to allow the 3rd party users an easier way of editing these tables.

So I've been pestering the forum to help me get up to speed with these new concepts which I haven't used before - so its
all new to me....

I'm trying everything you suggest, hoping I will be able to ultimately export and import freely from and to xls and csv.

Regards

Coldan

 

[Olaf Doschke]

That's okay, Coldan,

I simply don't understand, why you overlook what's already been said. It's not that you need to know that, you can reread answers already telling you memo fields are problematic in both directions with the simple commands. And that's not a matter of old or new Excel file types. Memo field never were supported for export or import from to XLS and TXT/CSV, so you need to take care yourself for these fields and need to forget about COPY TO, IMPORT and APPEND, these commands are no solution, forget them as long as you have memos.

Secondary automation of Excel is fine for both directions, handling xls as a table via oledb provider would be more natural.

Last not least, putting a grid control on a form displaying the dbf makes it possible for users to edit data without export to xls. You can add programmatic processing of data. I don't see excel as easier in itself, users might just be used to using excel.

Bye, Olaf.

 

[Jerim65]

Olaf,
Thank you once again.

To answer your points in reverse.

<grid control on a form displaying the dbf makes>

I have that facility with a number of options on buttons on the form and it works well.

< handling xls as a table via oledb provider >

I did ask how to implement this - is there a guide for not so experienced persons?

<I simply don't understand, why you overlook what's already been said.>

I don't overlook - I try to understand everything that has been said - I do understand the problem about Memo fields- and it may be that I can't do what I want because of them.

I try to implement every suggestion to try and find one that works for me.

Regards

Coldan

 

[TamarGranor]

Just want to add a couple of points for anyone who might come across this thread.

1) It's very rare to want to APPEND FROM or COPY TO ... TYPE XLS. That works with Excel 2.0 format. Today, you're more likely to want TYPE XL5 or, for APPEND, TYPE XL8.

2) On the file format parameter for SaveAs, 43 is the Excel 97-2003 format. Unfortunately, when you create that format in Excel 2007 or 2010, the file it creates can't be read by APPEND FROM. The same is true for a file converted from the XLSX format in Excel 2003 with the compatibility pack.

3) The file format parameter that gives you Excel 95 format is 39. Files created that way can be read by APPEND FROM.

Tamar

 

[Olaf Doschke]

Coldan,

< handling xls as a table via oledb provider >

I did ask how to implement this - is there a guide for not so experienced persons?

i already gave you pointers to an article at Foxite in my posting from 11 Dec 10 6:30:

http://www.foxite.com/archives/vista-excel-compatibility-w-worksheets-0000153776.htm

Bye, Olaf.

 

[Mike Lewis]

Can I add a couple of points about using OLE DB to access Excel data from VFP.

First, there seems to a bug in the driver that causes it always to assume that the data in the first row of the worksheet contains field names.

Normally, this is a user-configurable option, but it appears that the options doesn't work. (See

http://support.microsoft.com/kb/288343.)

One result of this that you will lose the first row of data, unless you specifically code for this situation (by creating an extra record and inserting the field names into it, keeping in mind that the names will always be characters, regardless of the data type of the field).

Another implication is that the driver will create field names that might not be valid in VFP. When this happens, VFP creates the cursor, but any attempt to access its data results in an error message.

To solve that problem, you have to loop through the field names (using FIELD(), for example), and change them to a valid VFP name.

These problems are not insurmountable, but you do need to keep them in mind if you are using OLE DB to import from Excel into VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[dbMark]

With the introduction of Excel 2007, the older VFP routines used to APPEND FROM, COPY TO, IMPORT and EXPORT all became obsoleted or unreliable with the new XML-based code starting with Excel 2007. The suggestions above are correct to use an OLEDB provider or open/process Excel using CREATEOBJECT("Excel.Application"). The old one-line methodology won't work everywhere any more.
thread184-1338029

 

[Mike Lewis]

There is one other method of moving data between VFP and Excel that might be appropriate in some cases: use HTML as an intermediate format.

HTML is just text, which VFP can easily handle. And Excel can both load and save HTML files, albeit with some limitations.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business