Using a command to copy DBF to EXCEL

[rdonnay]

/*
This command was contributed by Michael Rudrich.

This is from an email to me from Michael:

"I'd like to share something - maybe it's helpful for you or
one of your customers:

In FoxPro there is a very easy way to export records into MS-Excel like:
COPY FIELDS cust_name, zip, address to c:\testing TYPE XLS

A customer asked me if I could create something similar -
and so I used the preprocessor for that

Maybe you could use it.. it is a very easy way to export to excel"
*/

Code: Select all

#INCLUDE "dcdialog.CH"
#Pragma Library("ASCOM10.LIB")
#Pragma Library("DCLIPX.LIB")
#Pragma Library("DCLIP1.LIB")

#COMMAND COPY TO <(file)> TYPE XLS ;
         [FIELDS <flds,...>] ;
         [   FOR <for>] ;
         [ WHILE <whl>] ;
         [  NEXT <nxt>] ;
         [RECORD <rcd>] ;
         [ <rst: REST>] ;
         [   VIA <dbe>] ;
         [ ALL ] ;
=> _dbExport( DC_Path(AppName(.t.)) + '_TempData', { <(flds)> }, __EBCB(<for>), __EBCB(<whl>), <nxt>, <rcd>, <.rst.>, <dbe> ) ;
   ;M->_oExcel := CreateObject("Excel.Application");
   ;M->_oExcel:DisplayAlerts:=.f.;
   ;M->_oBook:= M->_oExcel:workbooks:Open(DC_Path(AppName(.t.))+'_TempData.dbf');
   ;M->_oBook:SaveAs(<(file)>+".xls", -4143 );
   ;M->_oExcel:Quit();
   ;M->_oExcel:Destroy();
   ;Ferase(DC_Path(AppName(.t.))+'_TempData.Dbf')


FUNCTION Main()

DC_LoadRdds()

USE ..\..\data\customer VIA "FOXCDX"

COPY TO Customer TYPE XLS   // This will end up in C:\Users\<login>\Documents
COPY TO (DC_CurPath() + '\Customer') TYPE XLS // This will end up in current directory

DCMSGBOX 'Done'

RETURN nil

* ----------

PROC appsys ; RETURN

[dougtanner]

Is it possible to save the spread sheet as an .xlsx for excel 2010? I have a customer who does not want an .XLS and complains that he has to load the .XLS and save it as an .xlsx.

Doug

[rdonnay]

Is it possible to save the spread sheet as an .xlsx for excel 2010?

I don't know but you could try changing this line of code:

;M->_oBook:SaveAs(<(file)>+".xls", -4143 );

to this:

;M->_oBook:SaveAs(<(file)>+".xlsx", -4143 );

[dougtanner]

I tried that but Excel recognizes it as a .xls just named as an .xlsx. I was hoping there was a replacement for the "-4143" that would change the file type?

Doug

[rdonnay]

I tried a Google search and found the following :

xlOpenXMLWorkBook = 51

Try this:

oExcel:saveAs(<filename>,51)

[dougtanner]

Thanks, that worked.
Doug