Saturday, November 18, 2017
  • Index
  •  » Automator
  •  » Need help with an Automator Action to create folders from a excel doc.

#1 2010-08-05 02:18:39 pm

coreyriggle
Member
Registered: 2010-08-05
Posts: 2

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

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.

Offline

 

#2 2010-08-05 02:28:08 pm

StefanK
Member
From:: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 11482
Website

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

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


Applescript:


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

Applescript:


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

Last edited by StefanK (2010-08-05 02:33:30 pm)


regards

Stefan

Offline

 

#3 2010-08-05 02:58:43 pm

coreyriggle
Member
Registered: 2010-08-05
Posts: 2

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

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,

Offline

 

#4 2010-08-05 03:04:24 pm

StefanK
Member
From:: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 11482
Website

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

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


regards

Stefan

Offline

 

#5 2011-06-04 01:55:45 am

sarahw97
Member
Registered: 2011-06-03
Posts: 2

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

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.

Offline

 

#6 2015-07-14 12:42:23 pm

Telenational
Member
Registered: 2015-07-14
Posts: 5

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

StefanK wrote:

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


Applescript:


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

Applescript:


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


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?

Offline

 

#7 2015-07-14 01:25:32 pm

StefanK
Member
From:: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 11482
Website

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

Telenational wrote:

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?


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

Applescript:


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


regards

Stefan

Offline

 

#8 2015-07-14 01:46:23 pm

Telenational
Member
Registered: 2015-07-14
Posts: 5

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

StefanK wrote:
Telenational wrote:

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?


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

Applescript:


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


Stefan,

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

Applescript:

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

Offline

 

#9 2015-07-14 01:57:57 pm

StefanK
Member
From:: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 11482
Website

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

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

Applescript:


…
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


regards

Stefan

Offline

 

#10 2015-07-14 01:59:26 pm

Telenational
Member
Registered: 2015-07-14
Posts: 5

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

StefanK wrote:

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

Applescript:


…
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


Ah, bingo!

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

Offline

 

#11 2015-07-14 02:25:17 pm

Telenational
Member
Registered: 2015-07-14
Posts: 5

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

StefanK wrote:

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

Applescript:


…
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


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?

Offline

 

#12 2015-07-14 02:42:40 pm

StefanK
Member
From:: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 11482
Website

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

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

Applescript:


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


regards

Stefan

Offline

 

#13 2015-07-14 03:23:58 pm

Telenational
Member
Registered: 2015-07-14
Posts: 5

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

StefanK wrote:

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

Applescript:


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


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

Applescript:

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

Offline

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

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)