Wiki Home

MSExcel


Namespace: Wiki
Microsoft Excel

Object model documentation:

http://msdn.microsoft.com/library/en-us/modcore/html/deovrMicrosoftExcel2000.asp

http://msdn.microsoft.com/library/en-us/odeomg/html/deovrmicrosoftexcel2000.asp

Excel 2003 Object Model Graphic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xltocOMMap_HV01049651.asp?frame=true

See also Excel Constants and Excel Automation
FoxPro Export command only export upto Excel v5. Is there a way to export it to later version of Excel? The reason is when the user open an Excel spreadsheet exported by FoxPro, when they save/close it, Excel will ask if they want to save it to the latest format, which is annoying to the user. -- David F

What I find even more annoying is when it hits the 16000 odd row limit and discards the rest of the data!! I've since switched to always exporting via Automation Tim Hustler

What you can do is open the saved file in Excel and save it from Excel itself, in the current version. Something like this:
#define CRLF Chr(13)+Chr(10)

Set Safety off
Local loExcel as Excel.Application
Local loWorkBook as Excel.Workbook
Local loActiveSheet as Excel.Sheets
Local loRange as Excel.Range
Local lError, lcFileName
lError = .F.
lcFileName = "c:\temp\customer.xls"
USE Home(2) + "data\customer.dbf"
Copy To (lcFileName) type xl5
Use
* let's open and fix in Excel

Try
  loExcel = CreateObject("Excel.Application")
  loWorkBook = loExcel.Workbooks.Open(lcFileName)
  loActiveSheet = loExcel.ActiveSheet
  ** debug **********
  *loExcel.visible = .T.
  ** debug **********
Catch  to oErr
  lcMessage = "Error #" + ;
    Transform(oErr.ErrorNo) + ")" + CRLF + ;
    "Message:" + Transform(oErr.Message) + CRLF + ;
    Transform(oErr.LineContents) + CRLF + ;
    "Details:" + Transform(oErr.Details) + CRLF + ;
    "LineNo:" + Transform(oErr.LineNo) + CRLF + ;
    "Program:" + Sys(16)
  MessageBox(lcMessage,48,"Error")
EndTry
If lError
  Return .F.
EndIf

* do whatever in Excel

* auto fit sheet
loExcel.Cells.Select()
loExcel.Cells.EntireColumn.AutoFit()

* Save sheet to make Excel save in current format
loExcel.DisplayAlerts = .F.	&& avoid "Y" confirmation dialog
loExcel.Range("A1").Select
loWorkBook.Save()
-- Alex Feldstein

In my installation, just saving it via automation does not save it in current format,
have to save to a temp file then save it back to make it work, something like the following:
loExcel.DisplayAlerts = .f.
loExcel.Range("A1").Select()
tempfile = originalfile + '.tmp'
loWorkBook.saveas(tempfile ,-4143)
loWorkBook.saveas(originalfile)
delete file (tempfile )

where -4143 = xlWorkbookNormal
-- David Fung

Another approach is to save the database from within VFP in Fox V 2.x format and then open the resulting dbf file in Excel. This gets you past the 16K row limit that was in Excel V5. This takes advantage of the fact that excel does recognize and can read dbf files but it canít read a dbf created in native mode by version VFP 6 and above.

Sample code (stripped of the extraneous error checking) demonstrating this approach follows:

close databases
public loexcel
use workcu
fname1 = sys(5)+sys(2003)+"\tempfile.xls"
fname2 = sys(5)+sys(2003)+"\tempfile.dbf"
erase 'tempfile.dbf'
erase 'tempfile.xls'
copy to tempfile fox2x
loExcel = Create Object( "Excel.Application" )
loexcel.visible = .t.
loExcel.Workbooks.Open(fname2)
loexcel.activeworkbook.saveas(fname1,-4143)
loexcel.quit
erase 'tempfile.dbf'

-- Mike Clare

Category Automation Category Code Samples
( Topic last updated: 2006.10.29 12:52:27 AM )