Automating Excel

Scripting Forums Automator
, ,
#1

Hello,

I’m trying to create a workflow in Automator to use in Excel. So far I have compiled the following actions:

  1. Get Specified Finder Items
  2. Open Finder Items
  3. Get contents of TextEdit Document
  4. Copy to Clipboard

Everything works fine so far. Here is where the problem occurs.

  1. Select Excel Worksheet

When the workflow gets to this point I receive the error message:

This action requires that “Microsoft Excel.app” be running and a document be open.

I have Excel running and a document open. Can anyone offer advice as to what the problem may be?

Thanks!

#2

Lots of views and no replies. :frowning: Is this too much of a newbie question?

#3

Hi,

it’s probably easier to use a simple AppleScript for this task.
I guess, the more information you give (what the task should do) the more replies you get :wink:

#4

Thanks Stefan! As I’ve begun researching scripts and automator in more detail I think I need a folder action. Whenever a file is placed in a specific folder I want my mac to open the file using the text editor, copy the data to the clipboard, activate the active excel workbook, activate a specific worksheet and then prompt me to enter a cell location where I want the data pasted into.

Here is what I have so far:

tell application “Microsoft Excel”
tell active workbook
activate object worksheet “sheet1”
end tell
end tell

#5

easiest version


set theText to read (choose file)
set {text returned:theCell} to display dialog "Enter a cell (format A2)" default answer "A1"
tell application "Microsoft Excel"
	tell active workbook
		set value of cell theCell of sheet "sheet1" to theText
	end tell
end tell
#6

Thanks! That is very cool. I’d like the file to be opened in TextEdit first and then copied to the clipboard before being pasted into the cell. The file is a spreadsheet file and if I open it in TextEdit and then select all and copy it will paste to excel in the proper locations. Wow did that make sense?

#7

This prompts for a file, opens it in TextEdit and copies the text to the clipboard


set theTextFile to (choose file)
tell application "TextEdit"
	open theTextFile
	set theText to text of document 1
	close document 1 saving no
end tell
set the clipboard to theText

Note: the document closes immediately after the copy process

#8

Thanks again Stefan! I’m learning more from you and my Applescript book than I could from my book alone. When I manually copy and paste my data into the selected cell the rest of the data from the file falls right into the rest of the appropriate cells in Excel, which is what I’m shooting for. When I run the script the whole contents of the file gets put in the selected cell. Is that because I’m telling the computer to put it all there rather than using the cell as a starting point for the data?

#9

Try this, TextEdit is not needed


set theText to read (choose file)
set {text returned:theCell} to display dialog "Enter a cell (format A2)" default answer "A1"
set the clipboard to theText
tell application "Microsoft Excel"
	tell active sheet
		paste worksheet destination range theCell
	end tell
end tell