Export dbf to xls 1

[redzombi5k]

Hello all,
I have a program that exports the data from my dbf to excel as a .xls
My question is this...
When I export everything is exported as text, is there any way to control this? i.e. make certain columns be numeric, or date ect.

Thanks,
-Steve

 

[DSummZZZ]

Take a look at the FAQ's for this forum, and also take a look over in this forum:
Microsoft: VFP - Automation, Mail & 3rd Party Svcs

There are all sorts of ways to format it via automation.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Stella740pl]

How do you export it?

If you use COPY TO ... TYPE XL5, or, alternatively, COPY TO ... TYPE FOX2X (that will copy into an older format DBF file which you can open from Excel and save as XLS), it recognizes numbers and dates just fine.

 

[redzombi5k]

Thanks Stella740pl and DSummZZZ

Stella740pl, I had no idea about the FOX2X, which solves the date field, but I am using character fields in my table and they need to be shown as numeric in the xls so Ill check out the Automation fourm that DSummZZZ listed.
Again, thanks for your feedback guys!

 

[Stella740pl]

While I like FOX2X at times, doesn't COPY TO ... TYPE XL5 (that's XLfive, not XLS) solves the date field problem also?


but I am using character fields in my table and they need to be shown as numeric in the xls

If this is the only change you need to make, you can try also another way of doing it: select your fields into a cursor while converting your character-numbers into numeric using VAL(your_field), then COPY TO from that cursor.

 

[redzombi5k]

Ahh I just assumed that was a typo, my mistake.
Your cursor idea sounds like it should work, cant believe I didnt think about that!
I will try it now and let you know.
Thanks again

 

[SnyAc]

Considering the existing problem with the VFP copy command not being able to work with Excel 2007 files, automation is definitely the way to go to avoid any problems down the line with future versions of Office...

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc. a division of AmTech Software

http://www.hyperwareinc.com

 

[Mike Lewis]

Andy,

Considering the existing problem with the VFP copy command not being able to work with Excel 2007 files, automation is definitely the way to go

That might or might not be true, but it doesn't apply in this case. Steve is trying to export to Excel. You can reliably export to Excel 2007. It's importing the new format files that causes the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[redzombi5k]

Alright, so after all these great sugesstions I have an idea. Tell me what you think..

I was thinking that it might be simplest if I were to use 'copy to - type xl5' and then use automation to set the formating of the columns that I need. i.e. column b's format would be numeric, and cloumn d's would be currency.

I would have just done the idea supplied by Stella740pl but I forgot that I have to do currency and numberic in excel.

Does this sound too convoluted or like a good idea?

I was looking at faq184-4428

http://tek-tips.com/faqs.cfm?fid=4428

but I didnt see anything on opening up an existing file, so that I could then change the formating

Any thoughts?
Thanks

-Steve

 

[Stella740pl]

Well, if you need to do a lot of formatting and other Excel-specific operations, it would definitely make sense for you to first copy it via TYPE XL5, then open it and work on it.

As for the details, even if you do a simple search in forum1251 and this one using keywords like "Excel file open", you will find tons of examples in there. Say thread1251-1398921 - see TamarGranor's and MikeLewis' posts there. You may find other great tips suitable for your task there or in other threads.

 

[redzombi5k]

ok, so I guess I am showing my ignorance with excel and automation here because I am able to change the formating (in that when you click on the cell and select format cells it shows to be what I set it to programaticly) but in the xls all the cells still have the little green triangle in the top left corner with the info box that says it is regular text, and when i use that warning box to convert to numeric it really does work.
So please forgive my ignorance here but if you have any suggestions or clues I would be very greatfull

Thanks again

 

[JRB-Bldr]

"I am showing my ignorance with excel and automation"

If you are creating your Excel output file with a COPY TO <> XL5, your VFP application is NOT using Excel Automation.

In order to use Excel Automation would have to write the cell values (and any cell formatting commands) discretely with code similar to that found in the faqs:
faq184-4266
faq184-4428
and other FAQ's on the topic

A 'mixed' approach would be for you to use the COPY TO command to write the 'raw' data and then go back and use Excel Automation to format the existing cell contents of the resultant XLS file.

Good Luck
JRB-Bldr

 

[brigmar]

I've struggled with this in the past and my solution was as follows:

* Create an Excel Template (.xlt) File for the report.
* Excel automation: Create a new file using that template
* Use ADO to transfer the table into a recordset
* Excel automation: Paste the recordset into Excel
* Excel automation: "Save As..." your new file

The ADO transfer and Paste operations are covered in foxwiki/foxite/UT references. Google "Cetin Basoz save to excel