VFP-generated Excel 2007 workbook won't save

[mkrausnick]

Using VFP9 running on XP SP2 and Office 2003, I open an existing Excel 2003 file, add data to it, and save it. It worked fine. When I upgraded Office to 2007, it stopped working. I can still open and populate the workbook, but when I execute the SaveAs method, I get:

This action cannot be completed because the Micrsoft Excel - c:\temp\myfile.xls (Compatibility mode) program is busy. Choose Switch To" and correct the problem.

with a "Switch To... / Retry / Cancel" dialog. Switch To and Retry don't do anything when clicked.

I tried the SaveAs method 3 ways:

oWorkbook.SaveAs("c:\temp\myfile.xls")
oWorkbook.SaveAs("c:\temp\myfile.xls")
oWorkbook.SaveAs("c:\temp\myfile.xls", 43)

All 3 of these statements work when running Office 2003. An article at

http://www.rondebruin.nl/saveas.htm

says that the file extension and file type must match. But is also says the default for saving is the format of the opened file, so I don't think that's the problem.

Any ideas how to solve this would be greatly appreciated.









Mike Krausnick
Dublin, California

 

[JRB-Bldr]

When you do a similar task manually in Excel 2007 (no VFP involvement at all) while recording the operations as a Macro, what does the Macro VBA code look like?

That is the best way to determine what needs to happen in your VFP Office/Excel automation code.

Good Luck,
JRB-Bldr

 

[DSummZZZ]

You may have to delete the file first before trying to save it. Then try using something like:

STORE "c:\temp\myfile.xls" TO cFileName
IF FILE(cFileName)
   DELETE FILE (cFileName)
ENDIF 
oExcel.Workbooks(1).SaveAs(cFileName, 56)  &&... 2003

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[dbMark]

Dave Summers has the right idea. This is what I've used for Excel 2007, though I'm not sure whether additional parameters are needed to remain compatiible with earlier Excel versions.

cFile = "C:\Windows\temp\MyFile.xlsx"  && or your file path & name
DIMENSION aXlsxFile[1,5]
lFileExists = ADIR(aXlsxFile, cFile)=1
IF (!lFileExists OR ;
	MESSAGEBOX("Overwrite existing file created on "+DTOC(aXlsxFile[1,3])+" at "+aXlsxFile[1,4]+"?", ;
			4+32,"Excel document already exists")=6)
	IF lXlsxFileExists
		* erase file in VFP first because if user cancels save in Excel Overwrite dialog then an error loop occurs
		ERASE (cFile)
	ENDIF
	oExcel.ActiveWorkbook.SaveAs(cFile)
ENDIF
oExcel.ActiveWorkBook.Close(.F.)  && or oWbook.Close(.F.) or oExcel.DisplayAlerts=.F. - avoid "Save Changes?" query

An excellent way to browse the VBA code that Excel uses is to do this:
In Excel, Alt-F11 for VB/VBA, then F1 for Help, search offline Workbook.{method} or Worksheet.{methods}, etc.

 

[TamarGranor]

Try letting Excel choose the extension, instead of specifying it.

Tamar