Not clear on exporting to XLSX format frm VFP9 2

[keepingbusy]

With reference to thread184-1605291 I have tried out some of the code posted without success:

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

First off, I get an error:

THIS can only be used within a method

so I'm guessing this is used within a form.

Having removed that and replace the THIS.cWorkFile with a variable (myfile) I still get an error:

OLE IDispatch exception code 0 from Microsoft Office Excel: 'myfile.xlsx' could not be found . Check the spelling of the file name, and verify that the file location is correct.

STORE "myfile" TO cWorkFile
COPY TO (cWorkFile) XLS
loExcel= CREATEOBJECT("EXCEL.APPLICATION")
loExcel.Workbooks.OPEN(cWorkFile)

My goal here is to export about 180000 records from a table into an XLSX file. We didn't have a problem in previous versions of Excel but as you know, they were limited to around 65000 records, hence the reason we upgraded.

We are using VFP9, Excel 2007, Windows 7

Any advice / guidance would appreciated.

Thank you


Lee

 

[JRB-Bldr]

Maybe some of the others have more ideas, but my recommendation would be to forget trying to export to an Excel file format which may or may not be supported by Excel 2007 - either due to record limitations or file format.

Instead export from VFP9 into something more 'generic' such as a CSV file and then it could be opened into Excel 2007 just fine and saved however they wanted.

Good Luck,
JRB-Bldr

 

[Dan Freeman]

Excel has a limit of 65k rows per sheet. You CANNOT push 180,000 rows via COPY TO. It's an Excel limit.

There are various workarounds that all involve breaking up the number of records into multiple sheets.

 

[Olaf Doschke]

simple reason for the code not working that way:

STORE "myfile" TO cWorkFile
COPY TO (cWorkFile) XLS

Foxpro does create the file in the current directory.

loExcel= CREATEOBJECT("EXCEL.APPLICATION")
loExcel.Workbooks.OPEN(cWorkFile)

Excels current directory is different from foxpros, so it doesn't find a file of course. What do you expect?

Work with full paths always and you will never have a problem. Forget about being able to work in a current directory, absolute paths are universally working and can be shared with other processes and applications of course, as they are concretely adressing that absolute file.

OK?

Beside that problem, you will generate an excel5 or 8 file, I'm not sure what copy to does, excel2007 is capable to read that in of course, no problem. And then you can save as new format.

But the two preferred methods for excel2007 are using the excel oledb provider generating the xlsx via it in the first place or automate excel and copy in via automation or eg via _vfp.datatoclip and a paste operation in the excel worksheet.

Don't just copy and run code, especially not partly, try to understand it, if there's something you don't understand you have the help and the internet to search and find answers.

Bye, Olaf.

 

[Koen Piller]

Hi,

If your problem is "how to export > 65000 records into Excel" than I would recoomend to make use of FoxyPreviewer.
Create a report with the data you would like to export, fireup FoxyPreviewer and save the report as Excel file, the routine in FoxyPreviewer allows you to export > 65000 rows. Once you have your file in Excel you can save it in any Excelformat you like including xlsx.

Regards,

Jockey(2)

 

[Mike Lewis]

Lee,

As Olaf has said, the immediate cause of your problem is a simple pathing issue. "Myfile" is present in the VFP search path, so the COPY command will work fine.

But Excel doesn't know anything about the VFP search path. So it is telling you it can't find the file.

The simplest solution is to wrap the filename in FULLPATH():

loExcel.Workbooks.OPEN([b]FULLPATH([/b]cWorkFile[b])[/b])

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[keepingbusy]

JRB-Bldr

Instead export from VFP9 into something more 'generic' such as a CSV file and then it could be opened into Excel 2007 just fine and saved however they wanted.

Yes, I tend to agree and having had a chance to look at this, I believe this is the way forward.

danfreeman

Excel has a limit of 65k rows per sheet. You CANNOT push 180,000 rows via COPY TO. It's an Excel limit.

I was aware of this but somewhere I thought I had read that since the restriction was now lifted in newer versions of Excel then my project could have achieved this from VFP (Obviously not!)

Olaf

Don't just copy and run code, especially not partly, try to understand it, if there's something you don't understand you have the help and the internet to search and find answers.

I'm not actually into the habit of this doing this but if you search this site people like me are very willing to learn and sometimes when experienced users post coding on this forum it actually assists others to pick up tips for next time and in any case in this example:

But the two preferred methods for excel2007 are using the excel oledb provider generating the xlsx via it in the first place or automate excel and copy in via automation or eg via _vfp.datatoclip and a paste operation in the excel worksheet.

I have no idea what you are talking about - OK?

I very much appreciate your many years of experience but sometimes you need to come down to the level of those who have less.

I did mention:

Any advice / guidance would appreciated.

Jockey2

Thank you for the tip. I will certainly check this out.

MikeLewis

Constructive posting as always Mike and thank you. I take on board your point about FULLPATH()

Thanks again to those who posted helpful and constructive feedback. When I find a sollution I'll post back.


Lee

 

[Dan Freeman]

I was aware of this but somewhere I thought I had read that since the restriction was now lifted in newer versions of Excel then my project could have achieved this from VFP (Obviously not!)

There are two different issues here.

FoxPro's COPY TO command does not create XSLX files. That file format came about after FoxPro was created. (Possibly even after FoxPro was discontinued! I'm feeling lazy about looking up dates.) FoxPro creates, at the latest, XL8 files with the limits that were in force on the XL8 file format.

There is a well-known bug in Excel file formats in the last version that FoxPro "understands" that prevents FoxPro from recognizing the file unless it is saved as a previous version.

On the Excel side, as of Excel 2007 the theoretical limit on the number of rows is over a million but that isn't anything FoxPro knows about.

 

[keepingbusy]

Thanks Dan, appreciate your post.

Lee

Visual FoxPro Version 9
Windows 7

http://www.customsoftware.co.uk

 

[Olaf Doschke]

keepingbusy,

Take ma apology, I was a bit harsh to say the least.

But I know you and you know me, we are in contact here all time, aren't we. And I'm afraid I have to say you're living up your user name here.

But because you are here often and you follow the threads (hopefully) you ust have come across the Excel 2007 problem and solutions to it.

You could simply follow the two pointers I gave you. If you don't know _Vfp.datatoclip(), then look it up in the help, I was really more talking about the help in this case, as the internet in general, the foxpro help has all the reference and details you need to know about something.

And in regard to adressing excel via oledb provider, googling "excel2007 oledb" leads me to:

http://www.connectionstrings.com/excel-2007

Then as a secondary search here on tek-tips I find:

http://tek-tips.com/viewthread.cfm?qid=1633357

and

http://tek-tips.com/viewthread.cfm?qid=1631018

In the second thread you also find a reference to

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

And there you have many further hints and code on how to go about Excel2007 with foxpro.

Working with oledb (or also odbc) has the advantage of being able to read and write the same way. While it's much more code than just EXPORT and IMPORT or APPEND, it works and Cetin provides VFP2Excel for easy usage. Now google VFP2Excel and you also find further results.

Bye, Olaf.

 

[keepingbusy]

Olaf

Take ma apology, I was a bit harsh to say the least.

Water under the bridge my friend.

Thank you sincerely for the links and the information for which I will take on board.

Lee

 

[keepingbusy]

I have made some progress so far and now able to open a file in Excel using the following:

COPY TO myfile.tab DELIMITED WITH "" with CHARACTER tab
DECLARE INTEGER ShellExecute IN shell32.dll ; 
  INTEGER hndWin, ; 
  STRING cAction, ; 
  STRING cFileName, ; 
  STRING cParams, ;   
  STRING cDir, ; 
  INTEGER nShowWin 
cFileName = "excel" 
cAction = "open" 
ShellExecute(0,cAction,cFileName,"\myfolder\myfile.tab","",1) 
CLEAR DLLS ALIAS 'SHELLEXECUTE'

That opens Excel 2007 and shows the 173000 records no problem.

However, in the second column (Let's call it column B) was a character field in my table containing SKU/UPC numbers some of which have between 1 and 5 leading zeros.

Prior to file being opened automatically in Excel, the leading zeros are present. When the file opens in Excel, the leading zeros are removed.

I have tried a few Google searches but cant seem to find an answer to this.

Any pointers or direction guys?

Thanks as always

Lee

 

[Olaf Doschke]

That shellexecute is clever, it opens excel including tab file as the command line parameter for excel to open.

Unfortunately that gives you no chance to set the column B to text type. Initially all cells of an excel sheet are set to "Standard", meaning an autodetection of value types. And the autodetection of types interprets your SKU/UPC numbers as numbers and so removes the leading zeroes.

Real Excel Automation is not automating it via Shellexecute/Commandline call, but via oExcel=CreateObject("Excel.Application")

So let's go back to square one once more.

Create Cursor curTest (sku C(10), dDate D, cText C(10))
Insert into curTest Values ("0000054321",Date(),"Hello")

oExcel=CreateObject("Excel.Application")
oExcel.Workbooks.Add()
oSheet = oExcel.ActiveSheet
oSheet.Columns("A:A").NumberFormat = "@"
_vfp.DataToClip("curTest",,3)
oSheet.Paste(oSheet.Cells(1,1))
oExcel.Visible =.t.

In your case you would need to set the numberformat for Columns("B:B").

If you would take the challenge to go with OLEDB the field types would not be guessed by values and not going through a text file or the cliptext your sku number would arrive as strings as they are. That would work best in that respect and that's why I prefer it. Going through some texte stripes off the difference between several field types, everything in principle is text then, detecting the correct type is guessing, and that's what you expereince, don't be surprised that the right way is not the most esiest here.

So I still recommend going with Cetin Basoz's VFP2Excel(), even if it's much more code. Put that in a prg, SET PROCEDURE TO it and the usage is a single line call. It is the silver bullet.

Bye, Olaf.

 

[keepingbusy]

Hi Olaf

Many thanks for posting the code. Before I start working on this can you confirm that there will not be the 65000 record restriction after the process has run?

Many thanks

Lee

 

[JRB-Bldr]

can you confirm that there will not be the 65000 record restriction after the process has run?

Since the code that Olaf shows above is not lengthy, I'd be willing to guess that you could take the time to test this yourself quite easily and then let us know yourself.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Simply generate more than 65000 records or take you large table instead of the curTest cursor, and you'll see. What are you waiting for?

Bye, Olaf.

 

[keepingbusy]

So this is where I am:

CREATE Cursor curTest (A C(21), B C(20), C C(18), ;
  D C(16), E C(15), F C(16), G C(40), H C(40), ;
  I C(20), J C(10), K C(254), L C(26), M C(12))

* Above matches that of MYTABLE.DBF where 173000 records are

SELECT * FROM MYTABLE INTO ARRAY curTest WHERE RECNO()<31

* I'm using WHERE RECNO()<31 to speed up the process for testing

oExcel=CreateObject("Excel.Application")
oExcel.Workbooks.Add()
oSheet = oExcel.ActiveSheet
oSheet.Columns("B:B").NumberFormat = "@"
_vfp.DataToClip("curTest",,3)
oSheet.Paste(oSheet.Cells(1,1))
oExcel.Visible =.t.

* oSheet.Columns("B:B").NumberFormat = "@"

* The above column (B) is where I'm having the problem of the missing leading zeros.

So I ran Olaf's code (thanks again Olaf) and got the following error message:

OLE IDispatch exception code 0 from Visual Foxpro for Windows: 13 : Alias is not found

Error 13 suggests:

You have attempted to specify an alias that has not been defined. There is no such alias currently in use.

The highlighted line is:

_vfp.DataToClip("curTest",,13)

Sorry to ask....

Lee

 

[keepingbusy]

Sorry Olaf, seems we posted at the same time

 

[keepingbusy]

One line should have read:

_vfp.DataToClip("curTest",,3)  && ClipFormat - Fields delimited with tabs

 

[Olaf Doschke]

Third parameter must stay 3. Commond, do you really not spot it? I have created a cursor curTest, you have an array, that doesn't work on an array, what you need is a cursor or a table and you enter it's name as the first parameter of _vfp.datatoclip().

Read the help, the parameterisation is described there.

Bye, Olaf.