I'm trying to build a macro to paste copied text of city, state and zip code data for companies I'm looking up on Google for my job. On occasion, Excel does this automatically because the webpage where the data was copied from was built as a table.
I'm trying to create a macro to paste what's on my clipboard but divide it properly into 3 columns. I feel like this would have been done already, does anyone know how to build this quickly or have something close to it?
I got the macro to work but I get the error below:
Here is an image of the macro:
I even tried to accommodate the error message at the end but when it occurs KM does not continue. But it does complete the actions I need.
Hey @bocciaman,
Put up an example or two on the forum, so we can actually see the format.
Use the preformatted text button
in the editor on the pasted text, so it is NOT rendered.
If for some reason you don't want the info public you can PM me.
This is a job for AppleScript.
-Chris
Ok, 3 things:
I've figured out that it does what I need by pasting it into three different columns if I just use the plain text paste via ^⌘V.
The data I need is simply coping the City, state and zip code of about 220 companies and paste them into an Excel file. I thought I needed automation because at first Excel was pasting all the data into one cell so I was using the Text To Columns feature which would need to be automated.
@ccstone I've actually started writing the AppleScript for using the Text To Columns feature but I can't finish it cause I'm not that good with AppleScript yet. The script is below:
tell application "Microsoft Excel"
activate
end tell
tell application "System Events" to key code 9 using {control down, command down}
delay 3
tell application "System Events"
tell process "Microsoft Excel"
click menu item "Text To Columns..." of menu "Data" of menu bar 1
tell application "System Events" to key code 48
tell application "System Events" to key code 49
tell application "System Events" to key code 36
end tell
end tell
My AppleScript is stuck on:
Can you tell me how to select radio buttons, checkboxes and click the buttons with AppleScript?
That's not AppleScript per se that AppleScript UI-Scripting, and you might as well use Keyboard Maestro if you going to work the UI (unless there are objects KM cannot see but System Events can).
Post examples of what your clipboard data looks like, or provide a link to the source.
That is if you want help actually AppleScripting Excel.
-Chris
Here is a sample of what my data look like:
What I'm pasting in is the City, State, Zip code
As I said in my earlier response, ^ ⌘ + V separates the 3 items above into their proper column.
Clipboard data is:
Elkhart, Indiana 46516
The only issue arises when the name of the city is two words separated by a space. Then the paste action is thrown off.
Are the city and state always comma-delimited as in your example?
Not always.
Copied data may be copied like so: Elkhart Indiana 46516 or even Elkhart IN 46516
I'm looking into how to use Apple-Data-Detectors with address strings.
We'll see how effective they are with address data like you demonstrate.
-Chris
As @ccstone mentioned, upload several examples (as in, several row examples) to get a response that better suits your needs.
I completed 229 records manually using ⌘C and ⇧⌘V. It did fine for the most part.
