MS Excel Apply Formatting

Questions & Suggestions
1

Continuing the discussion from Clipboard Formatting - MS Excel:

This seems pretty related, everything I have tried to get the formatting to paste for a cell is not working. I have tried inserting text with already formatted text, pasting text and then applying formatting to the text, pasting text and coping it to a saved clipboard switching to the Finder and then applying the formatting to the clipboard, doing the same thing but using the system clipboard and nothing seems to work.

Ideally what I would like to do is just select a cell and apply font color, size & type and if possible the background cell color but the background cell color is not crucial in most situations.

WIP Excel - Arial, Size 12, Color Red Formatting (background white).kmmacros (26.1 KB)

This macro below should just simply paste in the values but all it ends up doing is pasting in the last thing in the clipboard.
Excel - Planning - Church (c).kmmacros (22.8 KB)

2

Have you tried calling an embedded AppleScript (or Javascript if Yosemite or later) from KM?

This may not be exactly what you need, but it should get you started:

AppleScript Tricks: Finding and formatting cells in Excel

3

Haven’t tested in KM, but this worked for me just running it from the AS Editor:

tell application "Microsoft Excel"
	
	set oActiveCell to active cell		-- Returns the currentlly selected cell
		
	tell oActiveCell
	
		set value to "TEST Using Tell Cell"
		set oACFont to font object
		
		tell oACFont
		
			set bold to true
			set font size to 18
			
		end tell -- oACFont
		
	end tell -- oActiveCell
	
end tell

See the Excel Applescript dictionary for other cell/font properties you can set.

4

Thank you Michael that was very helpful.

tell application "Microsoft Excel"

	set oActiveCell to active cell -- Returns the currentlly selected cell
	tell oActiveCell
		set value to "TEST Using Tell Cell"
		set oACFont to font object
	
		tell oACFont
		
			set bold to false
			set font size to 12
			get font style
			set strikethrough to false
		
			--get underline
			--set font style to "underline"
			set font style to "Bold"
		
			--get color
			set color to {255, 0, 0}
			set name to "Times New Roman"
		
			(*
				set {name, font style, font size, strikethrough, superscript, subscript, ¬
					outline font, shadow, font color index} to {"Arial Narrow", "Italic", ¬
					10, false, false, false, false, false, color index automatic}
			*)
		end tell
	end tell -- oACFont
end tell -- oActiveCell

I found all the other properties I was looking for thanks to you. Now I just need to figure out how to apply it if more than one cell is selected.

5

Excel operates on Ranges. Even one cell is a Range.
There should be a method to create a Range from the current Selection.
Then you can set the font object of the Range.

6

Thanks again Michael and CCStone, after searching for the answer for quite a while I found your post on macscripter which embarrassingly pointed me back to a post ccstone had already given me embedded in another question.

tell application "Microsoft Excel"
 tell active window
	set color of font object of selection to {255, 0, 0}
 end tell
end tell

With the wuzzle from you both.

tell application "Microsoft Excel"
	set oActiveCell to active cell -- Returns the currentlly selected cell
	tell oActiveCell
		set value to "TEST Using Tell Cell"
	end tell
	
	tell active window
		set name of font object of selection to "Arial"
		set color of font object of selection to {255, 0, 0}
		set font size of font object of selection to 12
		set bold of font object of selection to false
		set strikethrough of font object of selection to false
		set underline of font object of selection to underline style none
   	 end tell
    end tell
7

I've never done a wuzzle before, at least not consciously, on purpose. :laughing: