×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Creating XLSX from a .DB

Creating XLSX from a .DB

Creating XLSX from a .DB

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

CODE --> FoxPro

*Command7.click - Create the Receipt.xls File
Select receipt
set safe off
copy all to receipt.xls type xls *(Can I specify xlsx 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 

RE: Creating XLSX from a .DB

(OP)
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

RE: Creating XLSX from a .DB

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=160529...
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=166726...
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

RE: Creating XLSX from a .DB

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-43...
https://www.vfphelp.com/vfp9/html/be78df32-95c1-49...

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

RE: Creating XLSX from a .DB

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

CODE -->

*Command7.click - Create the Receipt.xls File
Select receipt
set safe off
copy all to receipt.xls type xls 
*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.
set safe on
Messagebox("Receipt Spreadsheet File Created", 0, "Receipt  Status")
select medspot
Set Order to WIZARD_1
Select donate
Thisform.refresh 

RE: Creating XLSX from a .DB

Well, from one of the threads Mike pointed to:

CODE

* 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

RE: Creating XLSX from a .DB

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

RE: Creating XLSX from a .DB

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 Smileing

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.

RE: Creating XLSX from a .DB

(OP)
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

RE: Creating XLSX from a .DB

Gordon,

I think what Chris was saying was as follows:

This is what you have:

CODE -->

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

and you should change it to this:

CODE -->

loExcel.Workbooks.OPEN("C:\folderName\Receipt.xls")
#DEFINE xlNormal -4143
loExcel.ActiveWorkbook.SAVEAS(ForceExt("C:\folderName\Receipt.xls","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

RE: Creating XLSX from a .DB

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

RE: Creating XLSX from a .DB

(OP)


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

Thank you in advance,

Gordon

RE: Creating XLSX from a .DB

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

Regards

Griff
Keep Smileing

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.

RE: Creating XLSX from a .DB

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

RE: Creating XLSX from a .DB

(OP)
Office 16

RE: Creating XLSX from a .DB

(OP)
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.

CODE -->

*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 

RE: Creating XLSX from a .DB

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

Regards

Griff
Keep Smileing

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.

RE: Creating XLSX from a .DB

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:

CODE -->

*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

RE: Creating XLSX from a .DB

Oddly enough, I'll be discussing this exact topic in my session at Virtual Fox Fest on Thursday.

Before I summarize options, one comment. NEVER NEVER NEVER use TYPE XLS. That's Excel 2.0 format and it can't handle dates properly.

So, natively, from VFP without any other tools, you can COPY TO XL5 (that's Excel 5 format) or CSV. That doesn't give you an XLSX. As people here have shown you, you can use automation to open the file in Excel and SaveAs to XLSX.

If you don't have Excel available on the machine where you're doing the saving, there are two tools built by community members.

The first is Vilhelm-Ion Praisach's program that someone already pointed you to.

The second is Greg Green's XLSX Workbook, which is part of VFPX: https://github.com/ggreen86/XLSX-Workbook-Class.

My paper that includes this material (as part of a broader look at crosstabs and pivot tables) is at http://tomorrowssolutionsllc.com/ConferenceSession....

Tamar

RE: Creating XLSX from a .DB

(OP)
Thank you Chris, Mike, Griff, Dan & Tamar!!!!!
You all rock and are awesome!
I have it working now.
Blessings to you all.
Gordon

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close