Paste Special in Excel

I have an excel Sheet that I need to send as an email attachment on a regular basis. I want to be able to send it with just the values and numbe values and number formatsr formats. I am trying to write Applescript to select the desired range, copy, open a new workbook, paste special ( values and number formats), send to (mail recipient), close without saving New Workbook.

Here is my code.

tell application "Microsoft Excel"
     tell worksheet "active" of active workbook
           select range "A1:N15"
      end tell
      tell application "System Events"
            keystroke "c" using command down
      end tell
      tell application "System Events"
            keystroke "n" using command down
      end tell
      tell application "system Events"
            click menu item "paste special" of menu "edit"
      end tell
end tell

When I run it copies what I want and opens a new workbook and then nothing. If I manually click th paste special it does just what I want.

What is wrong?

Hello.

Hopefully this works for you, maybe you’ll have to change the numbers, for the menu items, I have used numbers, in order to not use the localized names, as you can see, you need to specify the menu bar as well as the menu, and menu item. I have also changed the call hierarchy in your script, so Excel can be adressed correctly from System Events. :slight_smile:

tell application "Microsoft Excel"
	activate
	tell worksheet "active" of active workbook
		select range "A1:N15"
	end tell
end tell
tell application "System Events"
	tell application process "Microsoft Excel"
		
		keystroke "c" using command down
		keystroke "n" using command down
		tell menu 4 of menu bar 1
			tell menu item 9 to click
		end tell
		tell button "OK" of window 1 to click
	end tell
end tell

Hi,

Excel has a huge AppleScript dictionary, GUI scripting is not needed


tell application "Microsoft Excel"
	tell worksheet "active" of active workbook
		copy range range "A1:N15"
	end tell
	set newWorkbook to make new workbook
	tell active sheet of newWorkbook
		paste special range "A1:N15"
	end tell
	
	-- do Mail task
	
	close window 1 saving no
end tell

Hello Stefan.

You are of course right, with regards to Excels huge object model, which is why I liked the OP’s approach by using UI Scripting, in stead of reading up on the object model. Sometimes I feel it counter productive to learn a lot just to get “something working” too.

Because of that, I felt it right to correct the script, because in many cases, when you have UI Element inspector on your disk, you will actually be able to create a solution in Excel faster with it, than reading up, and experimenting with the dictionary. :slight_smile:

GUI scripting is always the worst choice, it’s worth any effort to avoid it

Hello.

I agree with you in that gui is not a sustainable solution, but I feel that under certain circumstances; (general) you know GUI scripting, and you are writing something that is just doing a job for you once, or you are trying out an idea. Then you may not interested in investing time into something that is out of your boundary, with respect to learning an object model the size of Excels, for something you want to do just once, or just an initial experiment.

Then sometimes GUI scripting is the cheapest alternative with respect to programming time, delivering a quality to the solution that is feasible in the moment, and for the life span of the product. Then I see GUI scripting as the component delivering most “bang for the buck”. :slight_smile:

But the OP was quite clear: “on a regular basis”.

I think Stefan summed up the situation nicely.

Personally, I hate Excel. I used to like Excel when I had to use it. Now I hate it. It’s a love hate relationship.

I’m not disputing that Shane. :slight_smile:

I’ve played with this for a bit and I’m stuck. Both solutions given stop after opening a new workbook and do not paste.

McUsrII, Am i correct that Menu 4= Edit and that menu item 9 = paste special? When does it select values and number formats?

D

I ran my solution a few times (Excel 2011) and it worked fine.
Of course without the code to handle the Mail part the close line at the end closes the new workbook immediately,

StefanK,

This does nothing in my Excel 2008.

McUsrII’s solution works up to the point of paste.

Struggling with the paste special (values and number formatting.

Does anyone know how to Paste Special values and number format only? Nothing is working. The script works fine up to that point. Excel 2008.

I don’t have Excel 2008 so I’m not able to test anything

Hello.

When I wrote the snippet, I tested that I didn’t have to select anything anything. And yes, you are corect in your assertions regarding the “Edit” meny, and the “Paste Special” command.

It worked for me, anyhow (Excel 2008) I wonder why it doesn’t for you. :confused: Can you see any error messages when you run the script from AppleScript Editor?

yes, "error “System Events got an error: AppleScript Editor is not allowed assistive access.” number -1719 from menu bar 1 of application process “Microsoft Excel” and highlighted click.

This does not show up when I run the script in excel.

Thanks for your help, this stuff always makes me feel dumb.

Hello.

No reason for feeling dumb, just be patient, and try to run this tinkered script from Excel, maybe an error message pops up. The thing is is that I have neithe allowed Excel, nor System Events to control my computer via Accessibility. fro the security pane in System preferecences. You can allow Applescript editor this, in order to be able to run scripts from AppleScript editor that uses such settings.

tell application "Microsoft Excel"
	activate
	tell worksheet "active" of active workbook
		select range "A1:N15"
	end tell
end tell
try
	tell application "System Events"
		tell application process "Microsoft Excel"
			
			keystroke "c" using command down
			keystroke "n" using command down
			tell menu 4 of menu bar 1
				tell menu item 9 to click
			end tell
			tell button "OK" of window 1 to click
		end tell
	end tell
on error e
	tell application "Microsoft Excel"
		activate
		display alert e
	end tell
end try


ok, i think we are getting some where. error,

Hello.

I’ll come back to you with a propery written script.

Edit

I have tinkered Stefans script a little bit, I have omitted the deletion of the new workbook, so you can see that you end up with a new workbook. Hopefully this works as it should.

tell application "Microsoft Excel"
	activate
	if (count of workbooks) > 0 then
		try
			tell worksheet "active" of active workbook
				copy range range "A1:N15"
			end tell
			set newWorkbook to make new workbook
			tell active sheet of newWorkbook
				paste special range "A1:N15"
			end tell
		on error e number n
			tell "Microsoft Excel" to display alert "An error occured: " & e & " : number " & n
		end try
	end if
	-- do Mail task
	
	-- close window 1 saving no
	-- We are done with the workbook we attached to the mail, and can delete it.
end tell