Using a script to input prices from an Excel sheet into tables on InDesign

[DavidW10]

Hi there,

I'm trying to find a script that will match a product code in the first column of a table with a product code (column A) in an excel sheet and pull back the price (column b) and enter it into the third column of a table on InDesign. 

I'm worried it might be a bit too extensive for a simple script to help with. 

This might be a long shot, but I've tried for a couple of hours now to get a script I found to work on InDesign to input prices into tables from an Excel sheet. Script below:

// get the active InDesign document
var doc = app.activeDocument;

// open the Excel file and get the sheet
var excelFile = new File("/path/to/excel/file.xlsx");
var excel = new Excel(excelFile);
var sheet = excel.getSheet("Sheet1");

// loop through all rows in the sheet
for (var i = 1; i <= sheet.getLastRow(); i++) {
  // get the code and description from the Excel sheet
  var code = sheet.getValue(i, "A");
  var description = sheet.getValue(i, "B");

  // loop through all cells in the first column of the InDesign table
  for (var j = 0; j < doc.tables.length; j++) {
    var table = doc.tables[j];
    for (var k = 0; k < table.rows.length; k++) {
      var cell = table.rows[k].cells[0];

      // if the code in the InDesign table matches the code in the Excel sheet
      if (cell.contents == code) {
        // insert the description from the Excel sheet into the third column of the InDesign table
        var newCell = table.rows[k].cells.add();
        newCell.contents = description;
      }
    }
  }
}

Even after editing the file path to go my excel sheet (attached), it gives me an erorr on line 6 - Excel does not have a constructor:

var excel = new Excel(excelFile);

Any help or pointing me into the right direction would help a lot, thanks!

David

[Robert Tkaczyk]

---

@Peter Kahrel wrote:

@rob day 

Maybe try . . .cells[0].texts[0].contents

---

Looks like they've fixed the bug in the ID v. 18 - that was in the VB/VBA in previous version(s) of InDesign ?

  call msgbox(mySrcTable.cells.item(1).contents)
  call msgbox(mySrcTable.cells.item(1).texts.item(1).contents)

Before, the returned text was exactly the same - now it is working / returning texts correctly ...