Good afternoon, All!

Last week one of my users reported a problem importing an Excel spreadsheet into one of our applications. At first glance I thought this would be a no-brainer, but it turned out that no matter what I did, I could not import the spreadsheet either. The error message was: Microsoft Excel file format is invalid. I had her send me an older spreadsheet that had been successfully imported, and on the first attempt it worked, but on subsequent tries it didn’t.

This had me scratching my head, so I tried importing the spreadsheets directly into FoxPro using the import wizard. All attempts failed. Today, my boss encountered the same problem in another system. Our collaborative efforts to fix the problem were to create a new spreadsheet from scratch in Excel, manually input the data from the defective spreadsheet, save it in an Excel 97 – 2003 format, and try again. It failed.

We have ruled out the possibility that Windows 7 had anything to do with it, because the problem is consistent on both platforms. What makes this problem so baffling is that I randomly chose files on my computer and successfully imported some, but others could not be imported.

Just to rule out VFP as the culprit, I installed Access on my machine and imported the problem spreadsheets into brand new databases and brand new tables. It worked like a charm. I have three computers on my desk, and I am getting ready to throw each one of them through a wall.

Please help save poor innocent computers from a deranged lunatic.


All best,

James M. Weil

Local loExcel, lnSheets, i, lcName, laSheetNames[1], liBond, lcFile

lcFile = This.txtExcel.Value 

IF Empty(lcFile)
	RETURN .f.
ENDIF 

This.oFile.Open('bond')
CREATE CURSOR curRedemptions (;
	redate d, ;
	amount n(13,2), ;
	project c(16), ;
	bondky i)

loExcel = Createobject("excel.application")
loExcel.Workbooks.Open(lcFile)
lnSheets = loExcel.ActiveWorkbook.Sheets.Count

Dimension laSheetNames[lnSheets,1]

For i = 1 To lnSheets
	lcName = loExcel.ActiveWorkbook.Sheets[i].Name
	laSheetNames[i] = lcName
Endfor

loExcel.Workbooks.Close()
loExcel.Quit()
Release loExcel

For i = 1 To Alen(laSheetNames)
	lcName = laSheetNames[i]
	Append From (lcFile) Type Xl5 Sheet (lcName)
Endfor

Delete All For Empty(amount)

Scan
	lcProject = Alltrim(Project)
	If Seek(lcProject, "bond", "proj")
		liBond = bond.ky
		Replace bondky With liBond In curRedemptions
	Endif
Endscan

USE IN bond

Thisform.oFile.Open('bond_redemptions')

SELECT ky ;
	FROM bond_redemptions ;
	INNER JOIN curRedemptions ON ;
	curRedemptions.amount = bond_redemptions.amount ;
	AND curRedemptions.redate = bond_redemptions.redate ;
	AND curRedemptions.bondky = bond_redemptions.bondky ;
	INTO ARRAY laDupe
	
USE IN bond_redemptions

IF _tally >0
	=MessageBox("Duplicate file.", 16, "Import")
	RETURN .f.
ENDIF 

SCAN
	ldRedate = Redate
	lnAmount = Amount
	lcProject = Project
	liBondky = Bondky
	Thisform.oRedemptions.GetRec(0)
	Thisform.oRedemptions.oFields.Redate = ldRedate
	Thisform.oRedemptions.oFields.Amount = lnAmount 
	Thisform.oRedemptions.oFields.Project = lcProject
	Thisform.oRedemptions.oFields.Bondky = liBondky 
	Thisform.oRedemptions.Save()
ENDSCAN 

=MessageBox("Import Complete.",48,"Import")