Automator and Excel help

Discussion in 'Mac Apps and Mac App Store' started by Ja Di ksw, Jun 11, 2014.

  1. Ja Di ksw macrumors 65816

    Ja Di ksw

    Apr 9, 2003
    Hi everyone. I'm making an automator workflow, and everything works fine except I'm missing one aspect. I will be importing files that will have different ranges (A1:A5000 in sheet 1, A1:A2000 in sheet 2, etc) and I need to copy some of these columns to other sheets. I can't find out how to use Automator to paste something in a new sheet (e.g., make column K in a new sheet the same as column A in sheet 1).

    Thanks a ton!
  2. kryten2, Jun 17, 2014
    Last edited: Jun 17, 2014

    kryten2 macrumors 6502a

    Mar 17, 2012
    It's possible in Automator if you use Range names. In Applescript you can do something like this :

    tell application "Microsoft Excel"
    	tell active sheet of active workbook
    		set lastRowCellA to string value of (get end range ("A" & (count rows)) direction toward the top)
    		set rangeToCopy to range ("A1:A" & lastRowCellA)
    	end tell
    	tell active workbook
    		make new worksheet at end with properties {name:"Testsheet"}
    		activate object worksheet 1
    		copy range rangeToCopy destination range "F1" of sheet "Testsheet"
    	end tell
    end tell
    Automator example workflow :

    1. Ask for Finder Items or similar action
    2. Open Excel Workbooks
    3. Select Cells in Excel Workbooks --> Range name SomeRange
    4. Get Selected Content from Excel Workbooks
    5. Copy Excel Workbook Content to the Clipboard
    6. Add New Sheet to Workbooks
    7. Paste Clipboard Content into Excel Workbooks --> Paste in cell F1

Share This Page