How to use automator to convert excel cells to text files

Great script. I'm also running into the -1409 error but the data is still populating fine.

I do have a question:

How would I extend this script to add cell data from Column 3, Column 4 etc ?

Thanks in advance.

This is pretty easy to do. It may actually be easier in the shell, but less readable, so here's an all-AppleScript solution.

It writes the files to the desktop, but you can change that by substituting a different directory path.

tell application "Microsoft Excel"

-- define your range here:

set myRange to range "$A$1:$B$20" of worksheet 1

-- extract the first two columns of the range

set filenames to value of cells of column 1 of myRange

set file_content to value of cells of column 2 of myRange

-- iterate through them:

repeat with id from 1 to (count filenames)

-- create/open the file

set this_file to open for access (file ((path to desktop as text) & item id of filenames)) with write permission

set eofthis_fileto 0

-- write the data

writeitemid of file_contenttothis_file

-- and clean up

close accessthis_file

end repeat

end tell

If you want this as an Automator-based solution, just paste the script into a 'Run AppleScript' action.

Hmm, there is no error checking in my script to make sure that things are valid... are you sure there's content in the cells, for example? It would fail if the filename was empty, for example.

You could also try adding log statements to validate the data.

As for the file already being open, this can happen if the script fails mid-run - it can leave the file hanging open... the easiest way to fix it is to just throw away the file in the Finder.

I'm not sure why the 'path to' statement would make a difference, but in any case you can simplify your woes by using 'choose folder' once outside of the loop and storing the result in a variable:

set dest_folder to (choose folder) as text

tell application "Microsoft Excel"

set myRange to range "$A$1:$B$45" of active sheet

set filenames to value of cells of column 1 of myRange

set file_content to value of cells of column 2 of myRange

repeat with id from 1 to (count filenames)

set this_file to open for access (file (dest_folder & itemid of filenames)) with write permission

set eofthis_fileto 0

writeitemid of file_contenttothis_file

close accessthis_file

end repeat

end tell

I also note that your revised script omits the 'close access this_file' line, which is needed to avoid problems.

Hmm, it works when I test it here, and the nature (and location) of the errors doesn't make much sense. It means there must be something specific to your setup that's influencing this - either your version of Excel, or something in the data.

The best step (and something I probably should have done earlier) is to simplify the work we ask Excel to do (it's a bear), so try:

set dest_folder to (choose folder) as text

tell application "Microsoft Excel"

set myRange to range "$A$1:$B$45" of active sheet

set filenames to value of cells of column 1 of myRange

set file_content to value of cells of column 2 of myRange

end tell

repeat with id from 1 to (count filenames)

set this_file to open for access (file (dest_folder & itemid of filenames)) with write permission

set eofthis_fileto 0

writeitemid of file_contenttothis_file

close accessthis_file

end repeat

Ultimately, there's no reason to target Excel for anything more than the bare necessities - namely getting the cell data.