Is there a way to export to a newer version of Excel?

[montypython1]

Greetings,

Is there a way to export to a newer version of Excel?

I realize that VFP supports XLS and XL5 formats, but I am starting to run into problems that might easily be addressed if I could export to Excel 97 or higher.

Specifically, I am exporting data to a specific Excel file, which then needs to be linked to another "master" spreadsheet. Some versions of Excel apparently do not properly support linking and embedding (with older file formats). When I link these older file formats into the "master" spreadsheet, Excel generates the error "Workbook has no bound sheets".

Many of our computers are using Excel2000, and these systems received the same "Workbook has no bound sheets" error as well. Note: there is not a problem with the spreadsheet, since the error does NOT occur if I download the data directly into the spreadsheet (for testing purposes, I can bypass VFP and grab the raw data from the server. I need VFP however, since it is used to perform other calculations and link data from other servers).

Any suggestions?

Thanks,
Dave Higgins

 

[Nigel Gomm]

Dave,

there is also this from Craig Boyd

http://www.sweetpotatosoftware.com/spsblog/2008/09/30/UsingExcel2007FileFormatsInVFP90.aspx

hth

nigel

 

[montypython1]

Thanks Nigel,

I've just opened your link to Craig's article and am reviewing it now.

Thanks,
Dave

 

[Mike Lewis]

Dave (and Nigel),

Regarding Craig Boyd's article.

Essentially, his suggestion is to use SQL pass-through with OLE DB / ODBC to access Excel 2007 (and later) files. I am using this approach on my present project to import data from Excel 2007 workbooks.

In general, the approach works well and does not require a lot of code. The big disadvantage is the overhead that it adds to the setup routine.

If the end-user does not have Office 2007 or later installed, they have to install the relevant version of the Access Database Engine. If you get them to do that themselves, it means a 25 MB download, assuming they know where to look for it and how to follow the instructions to install it.

Alternatively, you could add it to your setup routine; in my case, that tripled the size of my Setup.EXE, plus it introduced extra complexity, even though only a small minority of users will benefit from it.

I'm not saying you shouldn't use this technique. As I say, it works well for me. But be aware of the overhead.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[montypython1]

Hi Mike,

Thanks for clarifying the advantages/disadvantages of this option.

I tried to use Craig's code, but received an error upon compiling: [Unable to find Unknown "CreateExcelTemplate"].

I also received an error when running the code: [Table number is invalid].
This error occurred near the top of the function, at the line with the code:
[IF ! USED(m.tvWorkArea) AND TYPE("m.tvWorkArea") = "C" AND FILE(DEFAULTTEXT(m.tvWorkArea,"DBF"))]

Since I only need to export data in a current Excel format, I only copied the appropriate function, but maybe I am missing something obvious.
Any ideas?

Thanks,
Dave

 

[Mike Lewis]

Dave,

Sorry. I can't help you. I haven't studied Craig's code in detail. I just glanced at the article and saw that it was using the same SQL pass-through technique that I am currently using.

Essentially, all you need to do is to use SQLCONNECT() to get a connection handle, and then send normal SQL commands in the usual way.

You could try posting a message to Craig on his own website. He'll probably be glad to help. (He might also reply to this thread, as he sometimes visits this forum.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[montypython1]

Hi Mike and All,

Good suggestion. I'll post the question on Craig's website.
As always, I certainly appreciate your assistance and that of all the other Tek-Tips contributors who took the time to point me in the right direction.

Thanks,
Dave

 

[cbasoz]

What is 'current excel format'? If you mean Excel is installed then I already gave how to export in that format

Cetin Basoz
MS Foxpro MVP, MCP

 

[craigsboyd]

montypython1,

CreateExcelTemplate is used to create the default (new, empty, or whatever you want to call it) Excel file. It is included so that I don't have to automate Excel to create a new excel doc. This even allows for export Excel docs when Excel isn't even installed (such as on a server). There are templates for XLS, XLSX, XLSM, and XLSB I believe (I wanted to support all new formats).

The example code I posted on my blog should work (unless you go in there cutting out pieces of code like you did).

Depending on your needs, another thing that works fairly well is to export Excel XML...

http://blogs.msdn.com/b/brian_jones/archive/2005/06/27/433152.aspx

... you can even give the XML file an XLS extension so it will open on dbl-click. If you change the extension, Excel will complain with a dialogue when it opens, but clicking Yes on that dialog opens it just fine.

SweetPotato Software Website
My Blog

 

[montypython1]

Hi Craig and Cetin,

Thank you both for your help.

Good point about cutting out pieces of the code (I thought I only needed the export function for my purposes .... I will try it again with the entire code).

I was able to accomplish my objective using the ideas from Mike, Cetin and the other contributors, but I can see that your solution will give me quite a bit of flexibility. I'll give it another go (I'm sure it will work fine this time).

Thanks again for your assistance.

Dave Higgins

 

[baltman]

I must point out:

Export a Formatted Table to Excel using HTML
faq184-4704

Customize and needed.

Cheers

 

[Mike Lewis]

Brian,

So what you are suggesting is that you create a text file containing an HTML table, the rows and columns of which reflects the records and fields in the data. You then open the file in Excel. Since Excel can read HTML, you end up with your data in a spreadsheet.

That seems to be a very neat solution. I haven't tried it yet, but I can't see why it shouldn't work.

One question: Is it necessary to include the style sheet in the HTML file? I would have thought that Excel would be able to figure out the data types for itself. Are the styles important?

I'll have to experiment with it when I can find a moment.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[baltman]

Styles provide a default format by data type in the example, such as commas for numeric fields with 2 decimals; and can be cutomized for particular fields etc.

The code in the FAQ is based on logic developed for a custom report writer I put together and I spent a lot of time figuring out the fastest way to write/format hundreds of tables/workbooks at a time. There's another FAQ I have there somewhere about how to create an Excel workbook from a collection of VFP tables via HTML using the same principals.

Formatting via HTML is much faster than automating formatting in Excel. The only thing best done in excel is auto fitting the column widths and possibly merging cells afterwords. Even there I gave Excel starting column width assumptions based on the width of the VFP field to maximize quality of output despite Excel's bugs with regard to such tasks.

 

[Mike Lewis]

I can see how applying the formatting via styles would be faster than doing it by Automation. Besides, the HTML solution has the advantage that you don't need Excel to be installed in order to create the file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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