Hello guys. I want to copy the content by typing a cell number (such as C34) on the Excel page. How can I do it. Thank you for your help
You should read this page. I think the answers will be there.
But I'm not sure what you are envisioning. Do you want a KM macro that is triggered by a hotkey that asks the user for a cell address and then copies it? Or do you want a KM macro that determines what the currently selected cell is, and copies that cell, whichever cell is selected.
I don't use Excel, but I read it has a menu item called CTRL-G that lets you jump to a specific cell by giving its number. Are you aware of that feature? Doesn't it do exactly what you are asking for? Or are you asking for something different?
What keys do you want to actually press to trigger this action?
Thank you for your interest, I reviewed the relevant page but I could not find exactly what I was looking for, and my English is not very good.
Actually, what I am trying to do is very simple, I want to copy the value in cell "C3" on the active sheet of the active workbook in Excel. I think the best way to do this is to use apple script.
I'm sure it is very simple to write this script for an experienced person, but I am not good at apple script.
Never a good strategy to make statements like that
- First, you have no way of knowing whether it is simple or not, and
- second, it discourages any potential help because you are announcing in advance that you will take any help for granted, blithely imagining it to be effortless.
Is it always cell C3
? If so, and if you want "the value of the cell, represented as text" (so that if C3
's formula is =TODAY()
you would get the text "26/07/2024"), then:
tell application "Microsoft Excel"
set the clipboard to (get string value of cell "C3" of active sheet of active workbook)
end tell
I wrote a subroutine a while back to get data from Excel. It may be a good starting point for you.
See it here:
This is exactly what I was looking for, thank you very much
you are right, thanks for the warning
Just to be complete, the counterpart (writing the clipboard's content to cell C3) would be:
tell application "Microsoft Excel"
tell active sheet of active workbook
set value of cell "C3" to the clipboard
end tell
end tell
Thank you for providing the GET script, which inspired me to google for the WRITE script.
I'm sure these two commands will come in handy when using Keyboard Maestro to exchange data with Ms Excel ;).
Footnotes to all the above:
- We don't really need the reference to the
active workbook
– we can go straight to testing whether anactive sheet
exists - If we refer to a range object in lieu of a cell, we can get CSV for several values from range labels like
C3:C10
- We can bypass the clipboard and directly set values for cells (or initialise a range to some value, if that's ever useful)
- We can also do all this in JavaScript (in place of AppleScript), and this gives much simpler access to Keyboard Maestro variables.
Excel GET Value(s) at Range
Excel GET Value at Range.kmmacros (2.2 KB)
Expand disclosure triangle to view JS source
const sheet = Application("Microsoft Excel").activeSheet;
return sheet.exists()
? sheet.ranges.byName(kmvar.local_Range_Label).value()
: "No active sheet in Excel.";
Excel SET Value at Range
Excel SET Value at Range.kmmacros (2.6 KB)
Expand disclosure triangle to view JS source
const sheet = Application("Microsoft Excel").activeSheet;
return sheet.exists()
? sheet.ranges.byName(kmvar.local_Range_Label)
.value=kmvar.local_NewCellValue
: "No active sheet in Excel.";
and, FWIW, AppleScript versions.
(Where again, you can either get a single cell value, or retrieve multiple comma-separated values by specifying something like C3:C10
rather than just C3
)
Excel GET Value at Range (AppleScript version)
Excel GET Value at Range (AppleScript version).kmmacros (3.6 KB)
set kmInst to system attribute "KMINSTANCE"
tell application "Keyboard Maestro Engine"
set rangeLabel to getvariable "local_Range_Label" instance kmInst
end tell
tell application "Microsoft Excel"
tell active sheet
if it exists then
value of range rangeLabel
else
"No active sheet in Excel."
end if
end tell
end tell
Excel SET Value at (AppleScript version)
Excel SET Value at (AppleScript version).kmmacros (2.8 KB)
set kmInst to system attribute "KMINSTANCE"
tell application "Keyboard Maestro Engine"
set rangeLabel to getvariable "local_Range_Label" instance kmInst
set newValue to getvariable "local_NewValue" instance kmInst
end tell
tell application "Microsoft Excel"
tell active sheet
if it exists then
set value of range rangeLabel to newValue
return value of range rangeLabel
else
"No active sheet in Excel."
end if
end tell
end tell
So, what should be the script to do this with Apple Numbers?
If you always want to target the same sheet, table, and cell of the frontmost document:
tell application "Numbers"
set the clipboard to (get value of cell "B3" of table 1 of sheet 2 of document 1) as text
end tell
...for cell B3
of the first table on sheet 2.
If you want the value of the currently selected cell it's a little trickier -- Numbers doesn't have the concept of an "active cell", but there is an active sheet
and only the table with the selection has a selection range
property that is a range
rather than missing value
:
tell application "Numbers"
tell active sheet of front document
tell (first table whose selection range's class is range)
try
if (count of cells in selection range) = 1 then
set the clipboard to (get value of first cell in selection range) as text
else
display dialog "Error -- multiple cells selected"
end if
on error
display dialog "Error -- no cell selected"
end try
end tell
end tell
end tell
thanks a lot
Or, of course, for the (possibly empty) list of all selected cell values, something like:
on run
set xs to selectedValues()
if 0 < length of xs then
xs
else
"No cells selected in Numbers."
end if
end run
-- selectedValues :: IO [a]
on selectedValues()
tell application "Numbers"
tell front document
if it exists then
tell active sheet
if it exists then
tell (first table whose selection range's class is range)
if it exists then
value of cells in selection range
else
{}
end if
end tell
else
{}
end if
end tell
else
{}
end if
end tell
end tell
end selectedValues