VFP6 and EXCEL export 1

[LALLORA]

Hi all.
Firstly sorry for my English.

I read all those I found about exporting from VFP 6 to Excel and I learned a lot but in the end I still have two problems.

I need to export from VFP 6 to Excel a table. I did it using COPY TO and, since that I need to modify some cells, I tried using this code (just to test) :

oExcel=CreateObject('Excel.Application')
oExcel.Application.Visible = .f.
oExcel.Workbooks.Open('C:\TEMP\F.xls')
oExcel.Range("A1:C185&quot.Select
oExcel.Selection.NumberFormat = "mm:ss"
oExcel.Workbooks.Close()
RELEASE oExcel

All seems work fine but :

1. Excel asks a confirmation for the changes. How can avoid it ?

2. Probably this is an Excel question, but after having applied the new cell format the cells are not recognised as "hh:mm" really till I go into the cell (manually) and simply press 'enter'. After this operation the content is aligned to the right and, i.e., I can sum the cells.

Any tip ?
Thanks in advance.

Marco (Italy)

 

[Mike Gagnon]

Try:

oExcel=CreateObject('Excel.Application')
oExcel.Application.Visible = .f.
oExcel.DisplayAlerts = .f && This will stop the confirmation popups
oExcel.Workbooks.Open('C:\TEMP\F.xls')
oExcel.Range("A1:C185&quot.Select
oExcel.Selection.NumberFormat = "mm:ss,@"
oExcel.Workbooks.Close()
RELEASE oExcel


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[LALLORA]

Thanks Mike but I still have problems. I use Excel 97.

I'd tried with DisplayAlerts = .f. before sending the message and I tried again after your answer but the modifications are not saved. It looks like the default answer for the question "Do you want to save..?" is "No".

The format mm:ss,@ is not allowed in Excel 97... I tried manually as well. The behaviour is a bit strange because the same operation applied to a cell with a number (i.e. with "#.##0,00&quot works fine, I mean, if I have a number in a cell with format General and I change the cell format the number is immediatly recognised as A NUMBER, a DATE or TIME not.

Further, do you know where I can find a list of all the properties I can use for Excel worksheet modifications ?

Thanks
Marco.

 

[monikai]

Hi Marco !
Look at CLOSE in Help VBA. I have Excel 97 and:

expression.Close(SaveChanges, FileName, RouteWorkbook)

expression - Required. An expression that returns an object in the Applies To list.

SaveChanges - Optional Variant. If there are no changes to the workbook, this argument is ignored. If there are changes to the workbook and the workbook appears in other open windows, this argument is ignored. If there are changes to the workbook but the workbook doesn't appear in any other open windows, this argument specifies whether changes should be saved, as shown in the following table.

True - Saves the changes to the workbook. If there is not yet a file name associated with the workbook, then FileName is used. If FileName is omitted, the user is asked to supply a file name.
False - Does not save the changes to this file.
Omitted - Displays a dialog box asking the user whether or not to save changes.

In VFP: oExcel.Workbooks.Close(.T.)

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[LALLORA]

Ciao Monika !
How about the weather there ? Here is terrible hot !

Thanks for your reply but, I do not know why, the Close with parameters returns 'OLE error code 0x8002000e Invalid number of parameters'... I use VFP 6.0... do you think I could have problems in the Excel library installation or something like this ?

Ciao.

 

[monikai]

In Poland is hot too (i had previous two weeks vacation, i were at Mazury - Polish lake country - and there was beatiful weather). Where are you ?
About VFP: try close from VBA with .T.
I use this OLE close in VFP 3 with Excel 5 and in VFP 6 with Excel 97

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[Mike Gagnon]

LALLORA

Although I'm using VFP7.0, Windows XP, and Excel 2002, the following should work for you.

oExcel=CreateObject('Excel.Application')
oExcel.Application.Visible = .f.
oExcel.DisplayAlerts = .f.
oWB=oExcel.Workbooks.Open('C:\TEMP\F.xls')
oExcel.Range("A1:C185").Select
oExcel.Selection.NumberFormat = "hh:mm:ss"
oExcel.Range("A1").value = "12:00"
oWb.SaveAs('c:\Temp\F.xls')
oWb.Close()
RELEASE oExcel
oExcel = .null.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[LALLORA]

Ok, now it works fine.

I still have the problem in applying the cell format date/time, but I know it's not a really VFP related issue... if someone knows something about or someone knows where I can submit this issue free (an expert excel site...)

Monika, I'm 60 km north Milan - Varese -


Marco (Italy)
marco.dicesare@elmec.it

 

[Mike Gagnon]

LALLORA

You may want to try to be a little consistant in your request for answers:

In your first post I see this line:
oExcel.Selection.NumberFormat = "mm:ss"

And I also see this in the same post:
are not recognised as "hh:mm"

And now in your last post I see :
in applying the cell format date/time,

It is unclear if you want minutes/seconds, hours and minutes, or datetime.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Gagnon]

For a datetime format try:

oExcel=CreateObject('Excel.Application')
oExcel.Application.Visible = .f.
oExcel.DisplayAlerts = .f.
oWB=oExcel.Workbooks.open('c:\temp\F.xls')
oExcel.Range("A1:C185").Select
oExcel.Selection.NumberFormat ="[$-409]m/d/yy h:mm AM/PM;@"
oExcel.Range("A3").value = TTOC(DATETIME())
oWb.SaveAs('c:\Temp\F.xls')
oExcel.Workbooks.Close()
RELEASE oExcel

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[monikai]

MIKE
But why don't work:
oExcel.Workbooks.Close(.T.) ?

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[LALLORA]

I'll try to be clearer. It's not a problem of hh:mm or mm:ss... it's exactly the same.

Try to run form VFP

SELECT "01:00" FROM your_table INTO CURSOR Query
SELECT QUERY
COPY TO C:\TEMP\F.XLS XL5

Replacing your_table.. so as to create a CURSOR which contains rows with columns with "01:00"

Open the XLS file; you'll get all the rows with 01:00 but the cells format is GENERAL. Apply with Excel the format hh:mm or mm:ss or... confirm and you should see that the content of the cell is 01:00 LEFT ALIGNED. Go into the cell, edit it and simply confirm the same value (you do not need to modify anything)... what happen ? The value NOW (only NOW) gets the correct cell format, infact it's RIGHT ALIGNED and you can use it for SUM, for example.

Now, from my application I extract a certain number of rows with columns containing 03:56, 05:29 and so on and the user should make an average or sum them or I do not know... he is forced to edit and confirm ALL THE CELLS to do so... I hope I'm clear now.

I do not have the same problem if I create a file with NUMBERS in the cells and then modify from GENERAL to any "number format" the cell format. Excel recognises and applies immediatly the format.

In your example you firstly apply the cell format and then insert the value. I'm sure it works.

Thanks Mike.



Marco (Italy)
marco.dicesare@elmec.it

 

[monikai]

I found in my old programs:
NOT oExcel.Workbooks.Close(.T.)
BUT oExcel.ActiveWorkbook.Close(.T.)
Sorry

Monika from Warszawa (Poland)
(monikai@yahoo.com)

 

[Mike Gagnon]

Monikai

oExcel.Workbooks.Close(.T.)

I have a feeling it has to do with the fact that you (and LALLORA) are using the Close function to close all workbooks and not just the one that is opened.

oExcel.Workbooks.Close()

This will try to close all opened workbooks, which also gives me an error, but:

oWb.Close(.t.,'c:\temp\F.xls')

In this example, I use an "opened" workbook reference, which seems to work without an error.

oExcel=CreateObject('Excel.Application')
oExcel.Application.Visible = .f.
oExcel.DisplayAlerts = .f.
oWB=oExcel.Workbooks.open('c:\temp\F.xls')
oExcel.Range("A1:C185").Select
oExcel.Selection.NumberFormat ="[$-409]m/d/yy h:mm AM/PM;@"
oExcel.Range("A3").value = TTOC(DATETIME())
oWb.Close(.t.,'c:\temp\F.xls')
RELEASE oExcel

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[monikai]

Thanks, Mike. You are right (always)

Monika from Warszawa (Poland)
(monikai@yahoo.com)