Hi Russell,

I'm working on the exactly same problem at the moment. I've modified my colleague's code and here's what I have right now.

The problems, that I need to fix, may be you or someone can help:
1) Date columns were not converted correctly
2) Long info was truncated to ~10 chars.

************************************************************
*  FUNCTION fSplitIntoDBF()
************************************************************
*  Author............: VCS Developers Team
*  Project...........: Visual Collections System
*  Created...........: 01/01/2000  17:00:00
*  Copyright.........: (c) Jzanus, 2006
*) Description.......: Takes an Excel file and makes a DBF out of it
*  Calling Samples...: fSplitIntoDBF()
*  Parameter List....: tcInputFile, tnStartFromRow
*  Major change list.:
function fSplitIntoDBF
lparameters tcInputFile, tnStartFromRow

#include Excel.h

local OleApp, lcMsg, lcCellValue, lcStr, lcRange, ;
	lnRow, lnCol, lnNumSheets, lnDoneSheets, lnI, loSheet, ;
	lnRows, lnCols, llValFound, loErr, lnSameField, lnK, lnSheet

lcMsg = ""
lcCellValue = ""
lnDoneSheets = 0
if empty(tnStartFromRow)
	tnStartFromRow = 1
endif

try

	OleApp = createobject("Excel.Application")
*-- Open XLS File
	OleApp.application.WorkBooks.open(tcInputFile)

	lnNumSheets = OleApp.application.application.WorkBooks(1).Sheets.count

	for lnSheet = 1 to m.lnNumSheets

*-- Select individual sheet from opened XLS File
		loSheet = OleApp.application.application.WorkBooks(1).Sheets(m.lnSheet)
		loSheet.select

		lcStr = OleApp.application.ActiveSheet.name

** Do this only for non-SUMMARY sheets in the spreadsheet
		if !("SUMMARY" $ upper(m.lcStr))

			lnDoneSheets = m.lnDoneSheets + 1
				
			** Delete first N unused rows (help by Sergey Berezniker)
			loSheet.Range("A1" , "A" + TRANSFORM(m.tnStartFromRow)).EntireRow.Delete()

*-- Find total # of columns
			lnCols = loSheet.UsedRange.columns.count
			lnRows = loSheet.UsedRange.rows.count

*-- Find total # of used rows
			for lnRow = 1 to m.lnRows
				llValFound = .f.
				for lnCol = 1 to m.lnCols
					lcCellValue = loSheet.Cells(m.lnRow,m.lnCol).value
					if not (empty(m.lcCellValue) or isnull(m.lcCellValue))
						llValFound = .t.
						exit
					endif
				next
				if !m.llValFound
					loSheet.range("A" + transform(m.lnRow)+ ":" + "A" + transform(m.lnRow)).EntireRow.Delete()
				endif
			next

*-- Find total # of columns
			lnCols = loSheet.UsedRange.columns.count
			lnRows = loSheet.UsedRange.rows.count

*-- Find total # of used columns
			for lnCol = 1 to m.lnCols
				llValFound = .f.
				for lnRow = 1 to m.lnRows
					lcCellValue = loSheet.Cells(m.lnRow,m.lnCol).value
					if not (empty(m.lcCellValue) or isnull(m.lcCellValue))
						llValFound = .t.
						exit
					endif
				next
				if !llValFound
					loSheet.Range(chr(64 + m.lnCol), chr(64 + m.lnCol)).EntireColumn.Delete()
				endif
			next

			lnRows = loSheet.UsedRange.rows.count
			lnCols = loSheet.UsedRange.columns.count

*-- Create column name array
			local array laFieldNames[m.lnCols,3]

			for lnI = 1 to m.lnCols
				lcCellValue = loSheet.Cells(1,m.lnI).value
				if vartype(m.lcCellValue) <> "C"
					if vartype(m.lcCellValue) = "N"
						lcCellValue = "_"+ alltrim(str(m.lcCellValue))
					else
						lcCellValue = "Not Valid"
					endif
				endif
				laFieldNames[m.lnI,1] = fGetFieldName(m.lcCellValue)
				laFieldNames[m.lnI,2] = alltrim(m.lcCellValue)
				laFieldNames[m.lnI,3] = m.lnI
			next

*-- SORT Field Names
			=asort(laFieldNames,1)
			lnK = 1
** Now make sure we don't have duplicates
			for lnI = 1 to m.lnCols - 1
				lnSameField = ascan(laFieldNames, laFieldNames[m.lnI,1], m.lnI + 1,-1,1,8+2)
				if m.lnSameField > 0
					laFieldNames[m.lnSameField,1] = ;
						laFieldNames[m.lnSameField,1] + transform(m.lnK)
					lnK = m.lnK + 1
				endif
			endfor
			
			for lnI = 1 to m.lnCols
				loSheet.Cells(1,laFieldNames[m.lnI,3]).value = laFieldNames[m.lnI,1]
			next

			lcRange = "A1:"
			lcRange = m.lcRange + chr(64 + m.lnCols) + alltrim(str(m.lnRows))
			loSheet.range(m.lcRange).select

			lcStr = strtran(m.lcStr,space(1),"_")
			lcStr = strtran(m.lcStr,".","_")
			lcStr = upper(strtran(upper(justfname(m.tcInputFile)),".XLS","") + ;
				"(" + m.lcStr + ")")

			OleApp.DisplayAlerts = .f.

*#DEFINE xlDBF3	8

			OleApp.ActiveWorkbook.saveas(sys(5)-curdir()- m.lcStr, xlDBF3)

		endif

	next

	if (m.lnDoneSheets = 0)
		lcMsg = "There are no non-summary sheets in the file - cannot process."
	else
		if (m.lnDoneSheets > 1)
			lcMsg = "There are too many sheets in the file - cannot determine which to use."
		endif
	endif

	OleApp.Quit

catch to loErr
	lcMsg = Log_Error(m.loErr)

endtry

OleApp = null

return lcMsg

endfunc

************************************************************
*  FUNCTION fGetFieldName()
************************************************************
*  Author............: VCS Developers Team
*  Project...........: Visual Collections System
*  Created...........: 01/01/2000  17:00:00
*  Copyright.........: (c) Jzanus, 2006
*) Description.......: Attempts to make a field name string
*  Calling Samples...: fGetFieldName()
*  Parameter List....: tcName
*  Major change list.:
function fGetFieldName
lparameters tcName

lcStr = alltrim(tcName)

lcAllowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'+lower('ABCDEFGHIJKLMNOPQRSTUVWXYZ') + "_"+"0123456789"

lcFind = chrtran(lcStr,lcAllowedChars,space(7))

if len(lcFind) > 0
	lcStr =chrtran(lcStr,lcFind,replicate("_",len(lcFind)))
endif
lcStr = left(alltrim(lcStr),8)
if asc(left(lcStr,1)) >= asc("0") and asc(left(lcStr,1)) <= asc("9")
	lcStr = "_"+ lcStr
endif

return lcStr

endfunc

>I have a situation where I need to import data from an Excel spreadsheet. Normally, this is fairly straightforward, however this is a spreadsheet that's being used like a database to generate quotes. There is a "header" section consisting of 16 rows that has information such as customer name, date, a logo, a button for customer lookup, etc. The line items on the quote are on lines 17 through whatever. Using a "standard" import (choosing Import off the File menu and letting VFP do it's thing), the data does get imported, however some fields have a nonprintable character in front of them and the last character of text is missing. Also, when you put the cursor in that field, the contents disappear. Then there's an issue with a date field. It comes in as a number. I've also tried importing by creating a table (making the 28 fields C(80)) and issuing an APPEND FROM ExcelFileName TYPE XL8. This didn't help. I'm thinking of two possible solutions: 1) I try multiple imports to
>tables with different structures to extract out what I need or 2) I use automation to open the file and pull the data out of the spreadsheet in a more "manual" fashion. Any suggestions would be appreciated.
>
>Russell Campbell