Get data directly from Excel

The function was written and tested in InDesign CC 2017 and Excel 2016 (Version 16) on Windows 10. Theoretically it should work in all versions from CS (3) to CC 2017 (12).

Function for Windows

Quite often I have to write scripts that use data originated from an Excel worksheet. I (and most scripters) believed that the easiest approach was to use a CSV-file exported from Excel. However, this takes an extra step for the user so I decided to write a function that opens an Excel book in background, reads the data from its spreadsheet and returns array.

The function has three arguments:

  1. excelFilePath — The platform-specific full path name for the xlsx-file — fsName. If you pass it as a string, make sure to double the backslashes in the path like in the line below:
    var excelFilePath = "D:\\My Test Folder\\SampleBook.xlsx";
  2. splitChar — [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t)
    If it isn't set, semicolon will be used by default.
  3. sheetNumber — [Optional] the worksheet number: either String or Number. If it isn't set, the first worksheet will be used by default

The data in Excel

The same data transfered to InDesign as Array

Click here to download the function.

Here’s a practical example: a script that uses this function.

Function for Mac

It works in the same way as the abovementioned counterpart for Windows and was tested in InDesign CC 2015 and Excel 2011 (Version 11) on Mac OS X 10.10 (Yoshemite).

The function has four arguments:

  1. excelFilePath — The Mac-specific – colon separated – full path name for the xlsx-file like in the line below:
    var excelFilePath = "Test:My Folder:SampleBook.xlsx";
  2. splitCharRows — [Optional] the character to use for splitting the rows in the spreadsheed. If it isn't set, pipe (|) will be used by default
  3. splitCharColumn — [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t) If it isn't set, semicolon will be used by default
  4. sheetNumber — [Optional] the worksheet number: either string or number. If it isn't set, the first worksheet will be used by default The function returns an array in case of success; null -- if something went wrong: e.g. called on PC, too old version of InDesign.

Here's the function for Mac.