### Export To Excel File - With User Define Field Header

[irfanhabib]

Dear All Expert -

When i export file in xls ( excel file format ) , Control Generate File As Per My Define Field Haeader Names.

Presently I Try Following Procedure. ( VFP6 - )

Sele A
Use Customer
Copy To CSNAME.XLS Fiel Custname:h='Customer Name',Add1:H='Address-1',Busad:H='Business Address',Stno:h='Street Number' Type XL5

My Requirement
==============

I want csname.xls file generate according to my define field header names wise. And when i open above
mention file in excel , all field headers names properly present without any special quotes ( ' , " " )
or space.

Best Regards
Irfan Habib

 

[Olaf Doschke]

The syntax with :h does not work out in VFP, it works with BROWSE FIELDS, still in VFP9, but neither with COPY TO nor EXPORT TO.
What you can do is export as is and afterwards change captions:

oXL = CreateObject("Excel.Application")
oWB = oXL.Workbooks.Add("C:\excelfiles\your.xls")
_cliptext = "Customer Name"+Chr(9)+"Address-1"+...
oXL.ActiveSheet.Paste()
or
oXL.ActiveSheet.Cells(1,1)="Customer Name"
oXL.ActiveSheet.Cells(1,2)="Address-1"
...

Bye, Olaf.

 

[Olaf Doschke]

You can also get the captions as defined for a DBF of a DBC via DBGetProp, for example DBGETPROP("customers.companyname","FIELD","Caption") results in "Company Name" for the customers.dbf of the northwind.dbc

Bye, Olaf.

 

[Mike Lewis]

I suggest a small modification to Olaf's code:

oXL = CreateObject("Excel.Application")
oWB = oXL.Workbooks.Add("C:\excelfiles\your.xls")
[b]lcSaveClip = _cliptext[/b]
_cliptext = "Customer Name"+Chr(9)+"Address-1"+...
oXL.ActiveSheet.Paste()
[b]_cliptext = lcSaveClip[/b]

This is just a courtesy to the user - not to interfere with whatever they have copied to the clipboard.

But that's not a problem if you use the alternative:

oXL.ActiveSheet.Cells(1,1)="Customer Name"
oXL.ActiveSheet.Cells(1,2)="Address-1"
...

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Good point,

but indeed restoring _clipboard only works with textual clipboard content. If you have a picture in the clipboard, eg from the snipping tool, then _clipboard is the empty string, still setting the _clipboard removes the picture. From that perspective you shouldn't use the _clipboard at all to write something.

I was just suggesting this as the code line would be quite similar to the field lists h: parts, so it's easy to change the existing code to new code putting together the captions+chr(9) (TAB) for moving from excel cell to excel cell and then just add it to row 1 in one paste. So I thought in the amount of refactoring work to save, when doing it this way.

Bye, Olaf.

 

[JRB-Bldr]

While this will not get you EXACTLY what you want, it will get you close.

Use Customer
SELECT Custname AS Cust_Name,;
   Add1 AS Address_1,;
   BusAdd AS Bus_Addr,;
   StNo AS Street_No;
FROM Customer;
INTO CURSOR ExportFile

SELECT ExportFile
COPY TO CSNAME.XLS XL5

If you want the Row 1 - Column 'names' in Excel to be more EXACT, then you will need to do one of the recommendations as advised above.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Yes, I didn't thought of underline, but while it's just a bit off, it's not really the job of field names to be captions, too.

You could also make use of my faq184-7770 to output a dbf as CSV including multiline memos (though that aspect is not needed here) and you could modify the code to export a first line of captions instead of filed names I put together between Fcreate and the first Fputs. Excel can open csv as if it was a simple XLS (without formatting), and that's what you get with COPY TO and EXPORT TO, too.

I didn't check, if there is anything VFP9 specific, you'll see that and either fix it or come here to ask for a backward compatible way.

And though it may be over the top to reprogram what copy to is made for, this FAQ code is there to be used, it rather solves the main issue of MEMO fields not being exported in the normal case, but in details it can of course also be modified in the captions row or any other aspect, as it acts very low level, while you can't force the :h convention into COPY TO, as this is just this single command.

Last not least, maybe you could try with SET COMPATIBLE, if there is a setting allowing the :h in COPY TO. I just got this idea and didn't try any option.

Bye, Olaf.

 

[Mike Lewis]

maybe you could try with SET COMPATIBILE, if there is a setting allowing the :h in COPY TO.

Afraid not. In FoxBase and dBASE, the COPY TO command took a FIELDS clause, but it didn't support the :H format.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[irfanhabib]

Respected All Experts -

Thanks Again - Problem Salved

@@@ JRB-Bldr , Thankyou Sir - Sucessfully Follow Your Advice And Finally Done.

Best Regards
Irfan Habib