Creating XLSX from a .DB

[GordonSH]

Currently I have a routine that creates an XLS file from a .db

*Command7.click - Create the Receipt.xls File
Select receipt
set safe off
copy all to receipt.xls type xls *(Can I specify [b]xlsx[/b] instead or not?)
set safe on
Messagebox("Receipt XLS File Created", 0, "Receipt XLS Status")
select medspot
Set Order to WIZARD_1
Select donate
Thisform.refresh

 

[GordonSH]

I tried to create an xlsx file but the command would not like xlsx.
So how can I take an xls file and convert it to xlsx within FoxPro?
Thank you in advance,
Gordon

 

[mjcmkrsr]

Hi,

Please have a look at [link ]

http://praisachion.blogspot.com/2015/06/append-from-xlsx.html

[/url]

hth

MarK

 

[Mike Lewis]

We've had several discussions on this topic on Tip Teps over the years. A couple of examples:

https://www.tek-tips.com/viewthread.cfm?qid=1605291

See the post from alan92rttt (Programmer) dated 24 May 10 11:52, and those that follow it.

https://www.tek-tips.com/viewthread.cfm?qid=1667260

See especially the post from mm0000 (IS/IT--Management) dated 3 Dec 11 20:11.

There is another option: Export the file to XLS rather than XLSX (you already know how to do that). All versions of Excel will happily open an XLS, after which it can be saved as XLSX if necessary.

Final point: FoxPro tables are type DBF, not DB as per your question.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Just to make it clear: VFPs COPY TO and EXPORT don't automate Excel or an Excel driver or provider. So you can't use these inbuilt native VFP commands to create newer Office formats natively.

The help topics on COPY TO and EXPORT list file types and - believe it or not - the latest Excel workbook format VFP supports is Excel 5.0 workbooks.

https://www.vfphelp.com/help/html/e5cc1f14-402f-43b7-8066-af695c60e183.htm

https://www.vfphelp.com/vfp9/html/be78df32-95c1-4939-a439-65f1f0b93ca9.htm

That's also important for IMPORT and what APPEND can do.

What still works is Office automation (of the desktop versions of Office applications). Also you can use newer ODBC drivers and OleDB Proviers, as long as they are 32Bit.

I'd personally favor writing out CSV to read it into Excel. It's also perfectly sufficient for the usual business data types, integer/float/numeric/currency, dates, datetimes and char types.


Chriss

 

[Dan Olsson]

@MarK I think you meant:
[

http://praisachion.blogspot.com/2017/01/export-dbf-to-msexcel-xlsx.html/link

]

 

[GordonSH]

Thank you all for your responses. I could not find a solution.

*Command7.click - Create the Receipt.xls File
Select receipt
set safe off
copy all to receipt.xls type xls 
[COLOR=#EF2929]*Want to open the xls file and save as an xlsx file and close the xlsx file within this code if possible and no screen.
*Please help me with the code.[/color]
set safe on
Messagebox("Receipt Spreadsheet File Created", 0, "Receipt  Status")
select medspot
Set Order to WIZARD_1
Select donate
Thisform.refresh

 

[Chris Miller]

Well, from one of the threads Mike pointed to:

* Your code
Select receipt
set safe off
copy all to receipt.xls type xls

* Excel workbook version upgrade by load & save
Local loExcel
loExcel= CREATEOBJECT("EXCEL.APPLICATION")

loExcel.Workbooks.OPEN(Fullpath("Receipt.xls"))
#DEFINE xlNormal -4143
loExcel.ActiveWorkbook.SAVEAS(ForceExt(Fullpath("Receipt.xls"),"xlsx"),xlNormal)
loExcel.QUIT()

* Your code
set safe on
Messagebox("Receipt Spreadsheet File Created", 0, "Receipt  Status")
select medspot
Set Order to WIZARD_1
Select donate
Thisform.refresh

With some notes:

1. Needs Excel installed (minimum Excel 2007, I guess) so its normal (xlNormal) format is XLSX.
2. Set Safety has no effect on Excel, so you might try to set Excel visible first (loExcl.visible=.T.) to see messages like asking to overwrite an already existing file.
3. Just like Set Safety also the current/default directory isn't known by the separate Excel process, so you have to pass on the full paths to filenames. This code ensures it with the FULLPATH() function and also changes the fileextension of the final XLSX via FORCEEXT() function.


Chriss

 

[Mike Lewis]

One point to keep in mind is the maximum file size. When you COPY ... TYPE XLS or COPY ... TYPE XL5, you can export a maximum of 65,535 rows. The XLSX format supports up 1,048,576 rows. This is unlikely to be a problem, but if your DBF does have more than 65,535 records, you might need to split it into smaller parts and re-join them after the export.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

If you have more that 65,535 rows you might do better to export as CSV, open in excel and save as xlsx

As you have to use excel automation anyway, it shouldn't be too stressful

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GordonSH]

Thank you Chris,

So I just neeed to replace FullPath with something like "C:\folderName\" ?

Or could I declare a variable Named: "FullPath = "C:\folderName\"

You have helped me a lot!

Thank you so much!

Gordon

 

[Mike Lewis]

Gordon,

I think what Chris was saying was as follows:

This is what you have:

loExcel.Workbooks.OPEN([highlight #EDD400]Fullpath("Receipt.xls")[/highlight])
#DEFINE xlNormal -4143
loExcel.ActiveWorkbook.SAVEAS(ForceExt([highlight #EDD400]Fullpath("Receipt.xls")[/highlight],"xlsx"),xlNormal)

and you should change it to this:

loExcel.Workbooks.OPEN("[highlight #EDD400]C:\folderName\Receipt.xls[/highlight]")
#DEFINE xlNormal -4143
loExcel.ActiveWorkbook.SAVEAS(ForceExt("[highlight #EDD400]C:\folderName\Receipt.xls[/highlight]","xlsx"),xlNormal)

Alternatively, you could leave the path out altogether, in which case Excel will open and save the file in its own default directory (which is not the same as VFP's).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GordonSH]

Hi Mike,
Thank you so much!!!
All of you on this group are so wonderfully helpful!!!
Gordon

 

[GordonSH]

When attempting to open the XLSX file I get this error.

Thank you in advance,

Gordon

 

[GriffMG]

I might be wrong, but your saveas is using a xlNormal, it could be you need xlOpenXMLWorkbook (which is 51)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

First your older question:

Fullpath() is a VFP function, you can use it as is.
If you only have a filename like receipt.xls, VFP stores that in its default folder, but Excel does not know it, so you have to pass over the full path.
You can also use a path as Mike suggested.

Now about your error screenshot:
This is the file that Excel saved?
Which Excel version do you have?






Chriss

 

[GordonSH]

Office 16

 

[GordonSH]

This code creates the xls file and opens it and saves as xlsx file but the above error happens when I go to open it.

*Command7.click - Create the Receipt.xls File
Select receipt
set safe off
copy all to c:\Spotlite\receipt.xls type xls
**************************
* Excel workbook version upgrade by load & save
Local loExcel
loExcel= CREATEOBJECT("EXCEL.APPLICATION")

loExcel.Workbooks.OPEN("C:\Spotlite\Receipt.xls")
#DEFINE xlNormal -4143
loExcel.ActiveWorkbook.SAVEAS(ForceExt("C:\Spotlite\Receipt.xls","xlsx"),xlNormal)
loExcel.QUIT()
**************************
set safe on
Messagebox("Receipt Spreadsheet XLSX File Created", 0, "Receipt XLSX Status")
select medspot
Set Order to WIZARD_1
Select donate
Thisform.refresh

 

[GriffMG]

I might be wrong, but your saveas is using a xlNormal, it could be you need xlOpenXMLWorkbook (which is 51)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

Griff could have hit the problem. One thing is sure: The type of the created file format is not mainly determined by the file extension but by the file format constant.
I can confirm 51 means xlsx.

So make it:

*Command7.click - Create the Receipt.xls File
Select receipt
set safe off
copy all to c:\Spotlite\receipt.xls type xls
**************************
* Excel workbook version upgrade by load & save
Local loExcel
loExcel= CREATEOBJECT("EXCEL.APPLICATION")

loExcel.Workbooks.OPEN("C:\Spotlite\Receipt.xls")
#DEFINE xlWorkbookDefault 51
loExcel.ActiveWorkbook.SAVEAS("C:\Spotlite\Receipt.xlsx"),xlWorkbookDefault)
loExcel.QUIT()
**************************
set safe on
Messagebox("Receipt Spreadsheet XLSX File Created", 0, "Receipt XLSX Status")
select medspot
Set Order to WIZARD_1
Select donate
Thisform.refresh

Chriss