Need help with an Automator Action to create folders from a excel doc.

Scripting Forums Automator
#1

Hello,

I’m working on an automator action to copy a range of cells in excel and then create folders based on those cells.
Here’s what i’ve got so far.

Select Cells in Excel Workbooks
copy excel workbook content to the clipboard
get contents of clipboard.

I’m not sure what do do next, I’ve got the list in the clipboard but now i need to make 25 or so individual folders.
I can get it to make one folder with all 25 names but thats about it.

Thanks in advance for any advice.

#2

Hi,

the clipboard is not needed at all.
The benefit of AppleScript is you can retrieve data from one application and pass it to others
This script take the values form cell A1 to A9 and creates new folders from them at the destination path


set destinationFolder to "MacHD:path:to:folder:"

tell application "Microsoft Excel"
	set theCells to value of range "A1:A9" of active sheet
end tell
repeat with oneCell in theCells
	tell application "Finder" to make new folder at folder destinationFolder with properties {name:item 1 of oneCell}
end repeat

Edit: Or if you want the value of the selected cells


tell application "Microsoft Excel"
	set theCells to value of (get selection)
end tell
#3

Thanks for the reply, I’d like to keep this within Automator. It’s my first project and I need something a little easier.

I took your advice about the clipboard. And that helped trim down some unneeded steps, but i’m still getting one folder with all 25 names.

thanks,

#4

you need a repeat loop to process each item, but I don’t know how to manage this in Automator

#5

Hi, this script is very helpful for what I’m trying to do; can anyone help me with how to tweak this so that if there are duplicate values in my excel range (A1:A9), to ignore / skip duplicates?

Or perhaps there a cleaner way to do this at the step where I am specifying the Excel range? My list is very long and it has many instances of the same account number.

Also, is it possible to modify this further to enable creation of subfolders / hierarchy structure?

Thanks; help much appreciated I am totally new to this.

#6

Hi, I’m curious.

It will not return the selected cells value if they aren’t in a range A3:A9 for example. Instead I’m selecting multiple random cells in the same column. Is there something similar that’ll make that work?

#7

After a few hours (actually minutes) of investigation I found this


tell application "Microsoft Excel"
	set theAreas to areas of selection
	set theCells to {}
	repeat with anArea in theAreas
		set end of theCells to value of anArea
	end repeat
end tell
#8

Stefan,

Thank you for this. Unfortunately I’m lost on why this still returns 0

set destinationFolder to choose folder with prompt "Where do you want the folder?"


-- tell application "Microsoft Excel"
--	set theCells to value of (get selection)
-- end tell
tell application "Microsoft Excel"
	set theAreas to areas of selection
	set theCells to {}
	repeat with anArea in theAreas
		set end of theCells to value of anArea
	end repeat
end tell
repeat with oneCell in theCells
	tell application "Finder"
		set newFo to (make new folder at folder destinationFolder with properties {name:item 1 of oneCell})
		make new folder at newFo with properties {name:item 1 of oneCell & "_Selects"}
		make new folder at newFo with properties {name:item 1 of oneCell}
		make new folder at newFo with properties {name:"Processed"}
		
		
	end tell
end repeat
#9

the array contains the flattened values of the cells, so it’s


.
repeat with aValue in theCells
	tell application "Finder"
		set newFo to (make new folder at folder destinationFolder with properties {name:aValue})
		make new folder at newFo with properties {name:aValue & "_Selects"}
		make new folder at newFo with properties {name:aValue}
		make new folder at newFo with properties {name:"Processed"}
	end tell
end repeat
#10

Ah, bingo!

Stellar work, Stefan. You’ve saved me hours and hours in mere minutes. Kudos!

#11

There’s one particular bug I can’t work out.

If you select all of them in a row vs a select few (should the document have everything correct and I do want A1:A6) I’ll click-drag a1:a6 and then run the script with no return?

#12

It’s not a bug, it’s different behavior for consecutive and non-consecutive selection.
You need to treat both cases differently


tell application "Microsoft Excel"
	set sel to (get selection)
	set numberOfAreas to count areas of sel
	if numberOfAreas = 0 then
		-- consecuctive selection, use  "value of selection"
	else
		-- non-consecuctive selection, use the "areas" solution
	end if
end tell
#13

For the sake of debugging I tried this whilst making non-consecutive selects and it doesn’t run/process.

tell application "Microsoft Excel"
	set sel to (get selection)
	set numberOfAreas to count areas of sel
	if numberOfAreas = 0 then
   set theCells to value of (get selection)
	else
		set theAreas to areas of selection
		set theCells to {}
		repeat with anArea in theAreas
			set end of theCells to value of anArea
		end repeat
	end if
end tell
repeat with aValue in theCells
	tell application "Finder"
		set newFo to (make new folder at folder destinationFolder with properties {name:aValue})
		make new folder at newFo with properties {name:aValue & "_Selects"}
		make new folder at newFo with properties {name:"Capture"}
		make new folder at newFo with properties {name:"Processed"}
		
		
	end tell
end repeat

Apologies! Can’t see what I’m doing wrong