Automator and Excel help

[Ja Di ksw]

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!

 

[kryten2]

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!

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

tell application "Microsoft Excel"
	--activate
	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