Use Case
- You need to copy cells on a Web Page in a HTML Table, and process the data
- However, the data that is negative is shown in a "red" color, but without the negative sign
- So these data show up in Excel as positive numbers.
- You need to make all data in Excel that is "red" be a negative number.
- While "red" is used here, it could be any color.
- The macro/script will check for the expected RGB of the Red data, and prompt the user if not found.
This macro was written in response to Why can't If_Then_Else: Image Condition detect the image correctly?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Example Output
Full Demo of How To Use Macro
MACRO: Copy Cells in HTML Table to Excel & Set Sign of Red Numbers [Example]
-~~~ VER: 2.0 2020-08-31 ~~~
Requires: KM 8.2.4+ macOS 10.11 (El Capitan)+
(Macro was written & tested using KM 9.0+ on macOS 10.14.5 (Mojave))
DOWNLOAD Macro File:
Copy Cells in HTML Table to Excel & Set Sign of Red Numbers [Example].kmmacros
Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.
ReleaseNotes
Author.@JMichaelTX
PURPOSE:
- Copy Cells in HTML Table to Excel & Set Sign of Red Numbers
HOW TO USE
1. First, make sure you have followed instructions in the _Macro Setup_ below.
2. Select Cell in Excel that will be the Upper Left Corner of the Data to Paste from the Web page.
3. Select ALL cells on the web page, both black and red, that are of interest, that you want to copy.
4. Trigger this Macro
5. The maro/script will set all "red" numbers to a negative value.
MACRO SETUP
- Carefully review the Release Notes and the Macro Actions
- Make sure you understand what the Macro will do.
- You are responsible for running the Macro, not me. ??
.
Make These Changes to this Macro
- Assign a Trigger to this macro.
- Move this macro to a Macro Group that is only Active when you need this Macro.
- ENABLE this Macro, and the Macro Group it is in.
.
- REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:
(all shown in the magenta color)- Set Variable “DND_CCH__RedRGB”
- This is the RGB value of the "Red" cells on the Web page as they appear in Excel
- Set Variable “DND_CCH__RedRGB”
REQUIRES:
- KM 9.0+ (may work in KM 8.2+ in some cases)
- macOS 10.11.6 (El Capitan)+
TAGS: @Excel @WebScrape @AppleScript
USER SETTINGS:
- Any Action in magenta color is designed to be changed by end-user
==USE AT YOUR OWN RISK==
- While I have given this a modest amount of testing, and to the best of my knowledge will do no harm, I cannot guarantee it.
- If you have any doubts or questions:
- Ask first
- Turn on the KM Debugger from the KM Status Menu, and step through the macro, making sure you understand what it is doing with each Action.
