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

 

[JRB-Bldr]

Actually just because VFP's COPY TO MyExcelFile XL5 will export into an older version of Excel, does not mean that the new versions of Excel cannot open and work with those files.

Another way to send data from VFP to Excel which will continue to work even with the newer versions of Excel is as follows:

1. From VFP execute a COPY TO MyCSVFile CSV
2a. Now either manually launch Excel and with it Open the CSV file.
2b. Or use VFP Automation of Excel (yes it works with the new versions of Excel) and have it Open the CSV file.
3. Then do whatever you want in Excel

Once the file is in Excel, you can always to a Save As.. to convert the file to the current file version.

If your problem is an Excel problem (not a VFP problem) like you mention with "Workbook has no bound sheets" you might post your question in an Excel forum and ask what is going on.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Dave,

Are you sure you've understood the problem correctly?

My point is that VFP will export to "version 5.0" Excel, which is the format supported by all versions since Office 4.0. That goes way back to Windows 95. That version, and all later versions, support "linking and embedding".

If Excel can open the workbook, it should be able to link it. If it can't, that's an Excel problem, and can't be cured within VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[cbasoz]

Use OLEDB export and do not use

copy to ... type xl*

Copy to ... type xl* command creates old Excel formats which current version of Excel even doesn't attempt to open and do not have a file converter by default. IOW "copy to ... type xl*" is a dead command at this point in time (unless you don't also have an old version of Excel installed).

Here is what I use with a sample:

Local oExcel
oExcel = Createobject("Excel.Application")
With oExcel
  .WorkBooks.Add
  .Visible = .T.
  VFP2Excel(_samples+'data\testdata.dbc',;
    'select * from customer',;
    .ActiveWorkBook.ActiveSheet.Range('A1'))
  .ActiveWorkBook.ActiveSheet.UsedRange.Columns.AutoFit()
Endwith

Function VFP2Excel
  Lparameters tcDataSource, tcSQL, toRange
  Local loConn As AdoDB.Connection, ;
    loRS As AdoDB.Recordset,;
    ix
  loConn = Createobject("Adodb.connection")
  loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.tcDataSource
  loConn.Open()
  loRS = loConn.Execute(m.tcSQL)

  For ix=1 To loRS.Fields.Count
    toRange.Offset(0,m.ix-1).Value = Proper(loRS.Fields(m.ix-1).Name)
    toRange.Offset(0,m.ix-1).Font.Bold = .T.
  Endfor
  toRange.Offset(1,0).CopyFromRecordSet( loRS )
  loRS.Close
  loConn.Close
Endfunc

Cetin Basoz
MS Foxpro MVP, MCP

 

[craigber]

That's not correct, Cetin. I have Excel 2010 and it will open .xls files just fine.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[alan92rttt]

I've been playing a lot with vfp to excel recently. WHile "copy to" get a basic output to excel the users expect a bit more formatting than "copy to" supports.


You can use a portion of what I've done to get a file into the current version.

1st I copy the data to excel using "copy to" and then open that sheet via ole.

COPY TO (THIS.cWorkFile) XLS
loExcel= CREATEOBJECT("EXCEL.APPLICATION")

** loExcel.VISIBLE=.T.  && uncomment for testing

loExcel.Workbooks.OPEN(THIS.cWorkFile)

At this point I'll normally add whatever formatting is required.

Now I save it in the current format.

DELETE FILE (THIS.cfilename) && make sure the name does not exist.
#DEFINE xlNormal -4143

loExcel.ActiveWorkbook.SAVEAS(THIS.cfilename,xlNormal)
loExcel.QUIT()

DELETE FILE (THIS.cWorkFile) && cleanup

You can determine most excel commands by making an macro in excel and seeing what it does.


Alan

 

[Mike Lewis]

Copy to ... type xl* command creates old Excel formats which current version of Excel even doesn't attempt to open and do not have a file converter by default. IOW "copy to ... type xl*" is a dead command

I hesitate to disagree, Cetin, but that's not my own experience. I have users with a range of Excel versions, and I'm not aware of any problems opening files that I've created with COPY TO.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[cbasoz]

OK I have Office 2010 64 on this computer (windows 7) and it simply denies to open xls files. You need to explicitly change settings in "Trust center" and it still says something like there isn't a file converter for Excel 2.0 ... xls is 2.0 fromat. Excel 2010 doesn't even like Excel 5 files. I don't think it is particular to my computer. I would be happy to be wrong since I have been doing tons of Excel automation.

Cetin Basoz
MS Foxpro MVP, MCP

 

[craigber]

Cetin,

I wonder if it's a 64-bit issue. I just tried 32-bit Excel 2010 and it opened an XLS file just fine.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[cbasoz]

Not an 64 bits issue as I can see but I still hope to be wrong testing such things late at night. I can automate and create workbook, open workbook etc provided it is not an old version. I was even surprised to find that the errors I got was already documented for Excel 2007 which doesn't have any 64 bits version. It has been only a few weeks I have installed it to see what may broke in VFP. One was that. Other one is with Word. Office team once again broke mailMerge OpenDataSource() - they do it with each version I am afraid to find more.

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

Mike,
BTW Office 2010 is quite new I suspect that you might have clients using it yet. I have hundreds of clients too and didn't yet get any complaints (but I think that is because none of them have installed Office2010 yet). I am actually scared to be right.

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Cetin,

Yes, that might well be correct. Even so, I'd be surprised if Excel 2010 couldn't read 97/2003 format, which I would have thought was still the most common. But who knows ....

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[cbasoz]

OK now that I am at home just tried it:

USE (_samples+'data\customer')
COPY TO "c:\temp\CustomerCopy1.xls" TYPE xls
COPY TO "c:\temp\CustomerCopy2.xls" TYPE xl5

Then open from Excel (manually by doubleclicking in explorer). First one opens in protected mode and second in compatibility mode. Before I tried opening it via automation and it were failing. Now that it at least opens in readonly mode manually I would need to revise my codes to handle it.

Cetin Basoz
MS Foxpro MVP, MCP

 

[alan92rttt]

I would think that making a CSV or dBase 3(can excel 2010 open that?)file rather than a XLS or XL5 file would solve the problem.

It would require a small change to the open command but that should be minor.



Alan

 

[cbasoz]

Alan,
I don't see CSV as a good alternative. It has problems especially with date/datetime values. Also neither CSV nor XLS/XL5 can export memo.

Cetin Basoz
MS Foxpro MVP, MCP

 

[craigber]

dBase3 is no longer a supported file type.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[alan92rttt]

My thought on CSV was a XLS replacement so the lack of memo does not rule it out.

I always had date "issues" even exporting to XLS.

When I do VFP to Excel I always am running from a cursor and a preprocess the dates to some out right and if required add the memos via OLE.

IF these are the only issues with CSV files I Can start converting my code to use CSV's now and be ready for office 2010 when we get there.

Alan

 

[Mike Lewis]

Alan,

I've often found that CSVs are a better choice for Excel import / export than XLS files. I can't say if that would be true for you as well, but it would definitely be worth your while to consider it.

I've never had a problem with dates, especially on export. On import, you need to make sure that the VFP date (and time) settings match those in the data. Apart from that, it should work fine.

In fact, I'm working on an application right now that needs to import spreadsheet data including dates and times, and we find it more reliable to have Excel save the data as a CSV and to import that into VFP, rather than to import the XLS.

Of course, you're right about memos, but, as you say, that's true of XLSs as well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[alan92rttt]

The excel date issue I normally have are on export. I could probably fix them by checking the date format and century settings.

I found that its easier to just wrap the dates in a dtoc() when I generate the cursor and they end up as dates correctly in excel. I'd bet that the same would be true for exporting to CSV.

Alan

 

[montypython1]

My Humble Apologies,

I should have written to thank you all much earlier (after posting my question I took a week off, and did not check Tech Tips until I returned). I am now working through all of your suggestions (and will probably have further questions or clarifications).

Again, thank you all for your suggestions. I will let you know how they worked for me.

Dave Higgins