Excel 2007 Data Impports As Memo Fields

[JICGreg]

I am using the code shown below to import from Excel 2007. This code was supplied by Microsoft in article id 949529.

The import seems to work fine. However, the data that comes in has the field format of memo. I do not want memo data. Is there a way to have the data come in as character or numeric?

In trying to work around the memo data issue, I have tried to use scatter and gather (into a different table).
Scatter apparently does not work with memo data. Store will work but if there are many fields that will be time consuming. Any ideas would be appreciated.



LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
lcSQLCmd AS STRING, lnSuccess AS INTEGER, ;
lcConnstr AS STRING
CLEAR

lcXLBook = &filename

lcConnstr = [Driver=] + ;
[{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
[DBQ=] + lcXLBook

IF !FILE( lcXLBook )
? [Excel file not found]
RETURN .F.
ENDIF

lnSQLHand = SQLSTRINGCONNECT( lcConnstr )

lcSQLCmd = [Select * FROM &sheetname]
lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
*lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [file2] )
? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )

IF lnSuccess < 0
LOCAL ARRAY laErr[1]
AERROR( laErr )
? laErr(3)
SQLDISCONNECT( lnSQLHand )
RETURN .F.
ENDIF

SQLDISCONNECT( lnSQLHand )

 

[Imaginecorp]

Scatter apparently does not work with memo data

use scatter memo to get memo fields

lcSQLCmd = [Select * FROM &sheetname]

This may be the culprit, its bring in all of the sheet as a single field...You may have to break up the import or export data to a table in excel than access it via vfp