How do I get the formatting when I export or copy a table in Foxpro to Excel? 2

[german12]

My free VFP table looks like this:

when I transfer it to MS EXCEL by using this command:

copy TO f:/dividendeab200421 TYPE xls

I receive this in EXCEL:

I tried it also with "Export to..." but that leads to the same result, which is not satisfactory because you would then have to manually adjust the formatting in Excel every time.
How do you achieve a satisfactory layout using a VFP program?
Possible?

Thanks in advance
Klaus


Peace worldwide - it starts here...

 

[TamarGranor]

Don't ever use TYPE XLS; that's Excel 2.0 format. TYPE XL5 should give you what you need.

Tamar

 

[german12]

Thank you Tamar,
now the result looks better

but unfortunately the values in column C (caption = "Ergebnis") have not yet changed into a better formatting. (2 decimals each would be fine)
Do I have to live with that?

Regards
Klaus





Peace worldwide - it starts here...

 

[Mike Lewis]

Klaus,

You can use Excel Automation to change the format of the numbers. Something like this:

ox = CREATEOBJECT("excel.application")
ox.Workbooks.Open("c:\data\MyWorkbook.xls")  && change this as necessary
ox.Columns("C").Select
ox.Selection.NumberFormat = "0.00"
ox.ActiveWorkbook.Save
ox.Quit

I haven't tested this, but I think it should work.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Bingo! Mike!
That is exactly the solution I needed.
This is the result now:

With the automatic processing of Excel / WORD (and other programs probably) via VFP, a whole new great world opens up for me.
Where - or how - can I best learn more about it as a beginner?

At the beginning I would try to put the headings in columns A-C in capital letters in the table above
and in columns B + C to automatically set them right-justified (or in the middle) via VFP.
The numbers in column C should also be in "x.xxx.xx" format
- (e.g. 3,840.00 in line 4) for better readability with large numbers.


Thank you for the valuable tips that I get from you (and of course from everyone who gives advice here).
It is really a pleasure to find out new things here again and again.

Stay healthy - and as we say here at the moment:
Stay negative.

Best regards from Germany
Klaus

Peace worldwide - it starts here...

 

[Mike Lewis]

Klaus, you're right. Automating Word and Excel (and many other applications) can indeed open new worlds for you.

Tamar has written some useful articles that will help you get started. Go to

http://www.tomorrowssolutionsllc.com/conferencepapers.php

and look for articles such as "Getting Started with Office Automation" and "Driving Word and Excel from Visual FoxPro".

And come back here when you have specific questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

If you need more than the beginner stuff on automating Excel that's in my articles, you might want to check out the book Della Martin and I wrote on the subject:

http://www.hentzenwerke.com/catalog/autofox.htm.

Della wrote the Excel and PowerPoint chapters, based on her experiences on the team that created the logistics app the US Department of Defense used for moving people and materiel (which was shown at multiple VFP conferences back in the day).

Tamar

 

[german12]

thank you very much, Tamar.
Klaus

Peace worldwide - it starts here...