Importing text data from excel - access to VBA functions?

imurphy

Nav 5

I've seen various postings reading importing from Excel via automation but nothing which answers my problem.

While importing excel files using the standard excel buffer table and functions you will occasionaly get an error because a cell contains a long text (more than 250 chars).

I was investigation a solution but haven't gotten anywhere. What I'd like to do is to use the Excel vba functions on the cell contents before importing. Something along the lines of:

xlapp.vbafunctioncalls.len(XlWrkSht.Range(xlColID + xlRowID))

the idea being that the evaluation of the length of the cell be done in excel and the result returned, rather than nav extracting the contents of the cell and then passing it back to excel to calculate the length.

There is an evaluate function which can be passed a string to be evaluated but it involves jumping through hoops to construct a cell reference in text format.

Anyone done any work on this?

Ian

abartonicek

Just limit the string that is stored into field "Cell Value as Text" in table "Excel Buffer".

Go to:
Table 370, function "ReadSheet"
and change line

"Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');

with

"Cell Value as Text" := COPYSTR(DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' '),1,250);

Hope this is what You were trying to do.

imurphy

This unfortunatly doesn't work when the import workbook contains a cell with several Kb of text (multiline product descriptions running to pages of text - stupid, I know). Normally the field only contained a short description but when these problem workbooks turned up the import routines would just fail with a library error. I needed a way to stop navision from even trying to read these cells.

In the end I solved this by writing a small tool for excel which simply scanned each cell of a file before importing and chopped the length. I also extended slightly the size of the import buffer. The user has to click on a 'check' button in excel before importing but its simple and works - which is what the user wants in any case.

Thanks for the suggestion anyway.

Ian