Excel Import from secondary Worksheet

lloydsmods

I have a client who is doing data-migrations from their customer's non-NAV system and they want to provide the customer with a template excel workbook, with different tabs for different data: customers, contacts, ship-to addresses, etc.

I have created an import report using the excel buffer, where the user selects the workbook, and the worksheet:

FileName := CommonDialogMgt.OpenFile(Text006, DefFileName, 2, '', 0);
SheetName := ExcelBuf.SelectSheetsName(FileName);
ExcelBuf.OpenBook( FileName, SheetName);
ExcelBuf.ReadSheet;
AnalyzeData();

Then, based on the name of the sheet, a function is called to insert the data in the correct table.

CASE SheetName OF
    'Customers': InsertCustomers();  // << this works
    'Customer Contacts': InsertContacts();  // this does not
    'Customer Ship Tos': InsertShipTos();  // neither does this
  END;

The odd thing is that it only works if you're importing the first worksheet in the workbook. I found that the info from the second and third sheets DO get imported into the buffer, but then, no matter what I do, the function to insert the records doesn't get triggered. It's not that the function doesn't write the records, it doesn't get called at all.

I even tried using a radio button to basically hard-code the sheet names, but that did not work, either.

Excel Buffer - OnPreDataItem()

RecNo := 0;

IF IncHeader THEN
  FirstRow := 2
ELSE
  FirstRow := 1;

CLEAR(x);
TempExcelBuf.RESET;
TempExcelBuf.SETRANGE(TempExcelBuf."Row No.");
TempExcelBuf.FINDLAST;

LastRow := TempExcelBuf."Row No.";

FOR x := FirstRow TO LastRow DO
BEGIN

  CLEAR(Col1);
  CLEAR(Col2);
  CLEAR(Col3);
  CLEAR(Col4);
  CLEAR(Col5);
  CLEAR(Col6);
  CLEAR(Col7);
  CLEAR(Col8);
  CLEAR(Col9);
  CLEAR(Col10);  // contacts
  CLEAR(Col11);
  CLEAR(Col12);
  CLEAR(Col13);  // customer ship tos
  CLEAR(Col14);
  CLEAR(Col15);  // customers

  TempExcelBuf.RESET;
  TempExcelBuf.SETRANGE(TempExcelBuf."Row No.", x);
  TempExcelBuf.FINDFIRST;
  REPEAT

    CASE TempExcelBuf."Column No." OF
      1 : Col1 := TempExcelBuf."Cell Value as Text";
      2 : Col2 := TempExcelBuf."Cell Value as Text";
      3 : Col3 := TempExcelBuf."Cell Value as Text";
      4 : Col4 := TempExcelBuf."Cell Value as Text";
      5 : Col5 := TempExcelBuf."Cell Value as Text";
      6 : Col6 := TempExcelBuf."Cell Value as Text";
      7 : Col7 := TempExcelBuf."Cell Value as Text";
      8 : Col8 := TempExcelBuf."Cell Value as Text";
      9 : Col9 := TempExcelBuf."Cell Value as Text";
      10 : Col10 := TempExcelBuf."Cell Value as Text";
      11 : Col11 := TempExcelBuf."Cell Value as Text";
      12 : Col12 := TempExcelBuf."Cell Value as Text";
      13 : Col13 := TempExcelBuf."Cell Value as Text";
      14 : Col14 := TempExcelBuf."Cell Value as Text";
      15 : Col15 := TempExcelBuf."Cell Value as Text";
    END;

  UNTIL TempExcelBuf.NEXT = 0;

  CASE SheetName OF
    'Customers': InsertCustomers();
    'CustomerContacts': InsertContacts();
    'Customer Ship Tos': InsertShipTos();
  END;

END;

Even more perplexing is that when I debugged this, the variable SheetName was set to "CustomerContacts" at the time the CASE statement was called, so I have no clue why it would skip the function call entirely.

Can anybody see what I've done wrong here? It's probably something obvious and I've been looking at it so long I can't see it.

](*,)