Using "Copy To filename" (command to copy the result from a query to excel) type XLS 5

[titoneon]

Hi Guys,
I have this code that creates a query, then later i just issued the "COPY TO" to copy the result of the query to create an spreadsheet,
you can see in the code i am allowing the user to select the drive letter, folder and allow to type the filename and as default the filename extension is "xls" but guess what, the user using this, has microsoft office 2007 and (the file should be created as "xlsx" well can read also "xls"), the file is created no problem but for some reason the field date which in the query is (ie..08/21/13), then it appears in the spreadsheet as 21-Aug-13, so the thing is i know VFP 9.0 does have problems with excel 2007 and the "Copy To" but i am wondering if there is any away to get the date as 08/21/13 in excel ? i don't know if this problem is for compatibility reason or i am missing something else ? most likely is a combination of the two

Note: the table i use, the field "day" is date field type

Here is the code i have in a command button for running the query and this go into a Grid, for the user to review.

SELECT job_no, employee, time, day, time_type, descrip, descrip2, extra_desc FROM Nengtime WHERE job_no IN (SELECT job_no FROM jobnos) AND !EMPTY(job_no) ORDER BY 4 INTO CURSOR RESULTD READWRITE
IF _tally > 0
WITH Thisform.grid2
.Visible = .T.
.ColumnCount = -1
.RecordSource = 'resultd'
.refresh()
ENDWITH
thisform.command5.Enabled=.t.
thisform.command4.Enabled=.t.
ELSE
Messagebox('There is not result for your Query, Check again your Job No. Entry',0+64,'Ok!')
thisform.command5.Enabled=.f.
thisform.command1.Enabled=.f.
thisform.command4.Enabled=.f.
Endif

i have a command button with this code for sending the result of this query to Excel is as below:

lcd =GETDIR() && to select drive letter and folder dialogo box
gcexc = lcd+ '.xls' && variable that holds the path and the extension filename
gcxls = PUTFILE('EXCEL File:', gcexc, 'xls') && to save file name from dialogo box
IF EMPTY(gcxls) && Esc pressed
CANCEL
ENDIF
COPY TO (gcxls) xls && Create as xls file using the "copy to " command



Thanks in advance

 

[Mike Lewis]

Everything you said about XLSX and XLS is irrelevant. VFP will save the file in XLS format, regardless of the file extension. And Excel 2007 (or any other recent version) will happily read it.

Also, everything you said about creating the cursor, showing it in a grid, selecting the directory, etc, is irrelevant - as is the code you showed.

It is Excel itself that determines how the date appears to the user. If you set the date format to mm/dd/yy, it will show up the way you want. If you don't explicitlty set the date format, it will appear in a default format.

Does the end-user have control of the formatting of the worksheet? If so, they should set the date format within Excel. Alternatively, set the format programmatically. To do that, you need to open Excel as a COM object. I assume you know how to do that, but if not we can give you some guidance.

Another option is to store the date as a character string within VFP. In that case, you can make the string appear in whatever date format you choose. But it will still be a string, so the user won't be able to manipulate it as a date in Excel.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Mike,
Thanks for getting back, i know it is irrelevant but i just did it in case there is something wrong there so you can see it, as i said above i got not problem using "copy to", to excel, the file can be opened as xls, no problem with that but after we open the file, we went to change the column where the date is displayed in excel and trying there to change as m/dd/yy but it only does for one cell, if i select the whole column from top to all the the way to the bottom, i have not choice to change that.
Thanks a lot

 

[TamarGranor]

Try using TYPE XL5 in your COPY TO instead of TYPE XLS. TYPE XLS is Excel 2.0 format and the handling of dates changed after that.

Tamar

 

[TamarGranor]

Actually, while you should do what I said, Mike's right that the issue is about formatting in Excel. Nonetheless, you should never use TYPE XLS, unless you want Excel 2 format.

Tamar

 

[Olaf Doschke]

>if i select the whole column from top to all the the way to the bottom, i have not choice to change that.
Click on the header of a column, the letter. Then you can change format for the whole column.

Bye, Olaf.

 

[Olaf Doschke]

...even if row 1 has a caption instead of a date.

 

[titoneon]

Hi Tamar,
Thanks a lot, i will do it as indicated
Regards
Ernesto

 

[titoneon]

Thanks Olaf, yes i am sure that is what it is, i just did that wrongly, but i have to wait until tomorrow
Regards

 

[JRB-Bldr]

What everyone else above has said is correct.
The issue about how the Date column values appears is an Excel issue, not anything that involves VFP.

Having said that, you can 'fix' it by running VFP Automation of Excel after the COPY TO code.

By using VFP Automation of Excel you can 'tell' Excel how to format your Date column values.

To find out what the code should look like, go into Excel (leave VFP out of it for a while), begin recording a Macro and Select the desired Date Column.
Next manually format that Column to appear as you want.
Then Stop recording the Macro.

Go into the Tools - Macro - Edit and examine the code.
That will help you understand what your VFP Automation code needs to do.

Good Luck,
JRB-Bldr

 

[titoneon]

Hi JRB-Bldr,
Thanks for that advise, i will do that and copy and paste that into my vfp code
Regards

 

[JRB-Bldr]

i will do that and copy and paste that into my vfp code

Before you do that remember that the Excel Macro VBA code is NOT a line-for-line paste-in to be used directly for VFP Automation code. Rather the VBA code is to be used as a guideline.

Sometimes it is VERY close, other times the VFP code needs to approach things slightly differently.

2 other resources you might want to look at are:
* Microsoft Office Automation with Visual FoxPro (

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

)
* faq184-4428

Good Luck,
JRB-Bldr

 

[titoneon]

Hi JRB and the rest,
if i do this on excel 2003 or 2007 as this
"Click on the header of a column, the letter. Then you can change format for the whole column"

well i did that by just selecting on the header then "format cell", select "DATE" and the type of format as 03/14/12 and it did not change at all, now if i select the whole column from top to bottom then on the first row for the cell if i right click in the left side there is small yellow diamond icon and right click on it and select from there the option "convert xx to 20xx" then
this is the only way that i can get that whole column and show the date as i need it. so maybe anyone can tell me what i am doing wrong or if it is something wrong in excel.
Thanks a lot and sorry for the inconvenience Guys.

 

[Olaf Doschke]

SET CENTURY ON then.

You have only put half dates into Excel. The small yellow diamond ('It's rahter a triangle as yellow traffic warning signs are), is indicating the field type is not clear.

Bye, Olaf.

 

[titoneon]

Hi everyone,
Problem solved, i just change the "COPY TO... TYPE XLS" to "COPY TO... TYPE XL5 and issue resolved as Tamar indicated earlier, thanks to each and everyone of you for your help always very appreciated and Guys Gals always excuse my ignorance but here you are, i am learning from you the whole time even if i get in your nerves, sorry about bothering with simples things.
Again always very pleased with all of you.

 

[Olaf Doschke]

Titoneon,

If developers not know something, that's sometimes an annoyance, but not here, not this time. I can't remember you getting in my nerves here, though I had some cases here. Like we all are foreigners in most countries in the world, we all know little in almost any subject. Therefore sharing this is leveraging this inequality.

In detail I learned here, that my guess to SET CENTURY ON should be unimportant, exporting to the newer XL5 format is more important for newest Excel versions to be able to read and interpret VFPs output correctly. And you teached me the excel format option is useless, if the source was XLS = Excel 2.

As a side story on the same command, a customer was eager to show off he "discovered" an option to even EXPORT TO XL8, but that's only valid for APPEND or IMPORT, see

http://computer-programming-forum.com/2-vfp/f43fb049eab0a6e8.htm

(see the last post from Cindy Winegarden).

You (wouldn't) want to know how many things I have a hard time progressing with, aside of, but even in VFP.

Bye, Olaf.

 

[Olaf Doschke]

Also, it's vice versa, isn't it? I was recently picky about ZAP with you. It's just a safety thought, also because ZAP deletes without checking any referential integrity constraints. Neither delete restrict nor delete cascades, it simply truncates the DBF file to the headr + EOF mark.

Bye, Olaf.