I know you asked for Excel X but the new AppleScript dictionary in Excel 2004 is based on the VB object model. You should be able to translate this AppleScript that does work for Excel 2004 to VB code for Excel X
tell application "Microsoft Excel"
set myRange to range "A1:D5"
set numCells to count of cells of myRange
repeat with idx from 1 to numCells
set curCell to cell idx of myRange
if value of curCell is equal to """ then
set cellAbove to get offset curCell row offset -1
set newVal to the value of cellAbove
set the value of curCell to newVal
end if
end repeat
end tell
Think I finally got it to work with the LookAt property. This seems like it should work:
tell application “Microsoft Excel”
Activate
tell Workbook 1
tell Worksheet 1
set data_range to UsedRange
set quote_cell to LastCell data_range Direction xlUp
repeat
try
set quote_cell to (Find data_range What “”" after quote_cell LookAt xlWhole)
on error – not found?
beep 1
exit repeat
end try
set prev_cell to (Offset quote_cell RowOffset -1)
set Value of quote_cell to prev_cell
end repeat
end tell
end tell
end tell
It looks similar to jimmer’s script. Wonder why they changed the wording so much. It returned your data as:
Thanks for the tips/snippets. Neither worked correctly for me. I was under the gun on this, so I managed to fake it using system events to just do the correct keystrokes. Took some time, but saved me from Carpal Tunnel.
First I did a “find” for a DQ in Excel, searching by row. Then ran this:
tell application "Microsoft Excel"
Activate
repeat
tell Application "System Events"
keystroke "g" using {command down} -- < find next Double Quote
keystroke (ASCII character 30) -- < arrow up 1 cell
end tell
tell Application "Microsoft Excel"
CopyObject Selection -- < copy value in cell
end tell
tell Application "System Events"
keystroke (ASCII character 31) -- < arrow down 1 cell
keystroke (ASCII character 3) -- < press enter key to paste/deselect
end tell
end repeat
end tell
I just had to watch it so when it got to the end of the second column, I could stop it.
I am shocked that my script didn’t work for you. You are using Office X? ui scripting is a last resort and it doesn’t seem like you really need to go to that. There is a way to do this without ui scripting.
Actually, it did work, but I screwed it up somehow. It was late, my eyes were fuzzy, all that. Your script did work perfectly when I tried it with a fresh brain.
Yeah, but the UI thing got me out of the woods. Saved me from having to learn an entire new library for something I won’t use again for probably 2 years. :rolleyes:
That’s just weird - I keyed that copy straight into the BBS as an example. Funny stuff.
Thanks for the feedback. Otherwise, I may have been working on this an extra week maybe.
Ui scripting may be a quick fix, but in the long run the advantages are minute.
You can explore the dictionary of an app and the more you do this, you gain steps towards doing this in many apps. Who knows? Maybe, in the future, the hard way may be the easy way.
BTW, I’m still not sure if the script will work in all cases of finding lone double quotes. So if you run upon any bugs, keep posting. I’m interested in this stuff also, as I’m pretty green at scripting Excel.