Copy from excel

[philranger]

Hello,

I’m copying a cell from Microsoft Excel and want to take action only if is not empty, but the clipboard contains an « Image », whether the cell is empty or not. I can’t pre-treat the excel files (there will be hundreds of them in the next year and the risk of accidentally saving the treated version is too high) and I’d prefer not having to first go through a second app (such as the notepad). Asking to convert the clipboard to an integer throws an error message.

Any hint, anyone?

Thanks in advance!

[philranger]

Here's what I found so far:


superuser.com/questions/1296965/how-to-disable-copy-as-image-in-excel

Q:
Recently it seems Excel has made copy-as-image default behaviour for the Ctrl+C hotkey (actually it seems to put the formatted text, table cell data, and a print-view image on the clipboard simultaneously).



This is bad if you have a workflow where you copy data from Excel and paste into another MS Office app, or into a web-form in Google Chrome, where the default behaviour of Ctrl+V is to paste the richest content available.

A:
Copy through a VBA macro.

I can't have my non tech-savvy users try to run 2 macros, I need a simpler way.  If MMM would have a more advanced way to convert the clipboard to a string or integer, that would work for me...

[philranger]

Found a workaround!

In the Excell cell, Hit F2 (edit cell), then ctrl-A (select all of the content), then ctrl-c (copy).  An empty cell will keep the last copied value though, but I can work with that.

1 | RUN ACTION | DEFINE STRING VARIABLE | %CLIPBOARD%::Cell Is Empty A1B2C3

2 | X | X | 0 | Keypress f2

3 | X | X | 0 | Keypress Ctrl+ | a

4 | X | X | 0 | Keypress Ctrl+ | c

5 | X | X | 0 | Keypress escape

6 | X | X | 0 | Keypress down

7 | IF | CLIPBOARD | VALUE | TEXT IS | Cell Is Empty A1B2C3 | GOTO MACRO LINE | 1


Pfeeew!

[bigjim]

Great info, I wish I'd seen this thread earlier I could have helped (or I could have benefited a few months ago! ), as I do this a lot too - use "edit mode" (hit F2 in a cell) to drop the flashing cursor inside the cell without having to emulate a human double-clicking into the cell to get at the contents, or a human mousing up to the formula bar and getting the contents from there..

In fact using MMM has got me into excel hotkey shortcuts in a big way in my macros as Excel is often involved somewhere... Old school spreadsheet pros would probably roll their eyes that we've forgotten so much of this stuff!



You may have seen all this already while looking for the solution (and I haven't found a better solution to date to your problem), but if you have a cell that uses functions and you want to use that function output in your macro without pre-treating the spreadsheet itself, pressing F9 immediately after F2 to go into edit mode converts the contents of that cell into a it's value, which Ctrl-A > Ctrl-C can copy and then use later in the macro.

If you use Escape to cancel-out of edit mode after copying your data, the conversion to value doesn't remain. If you tab-out or Alt-Enter, etc the conversion remains, and it's as if you'd pasted as value into the cell.



Navigating tabs by using Ctrl-Pgup/Dn is another favorite, combined with Ctrl-Home lets me ensure that users are starting at the same point in the spreadsheet every time I jump to the excel active window (tab-1, top-left cell) and they haven't started the macro with some random cell selected which messes up the routine.



Something to be aware of, if your users are using different versions of excel, some of those hotkey shortcuts work slightly differently.

For example, on my computer at home i use a slightly over version of excel, and F2>Ctrl-A will not select-all like it does for you. Instead on that version I need to F2>Ctrl-Shift-Home to get the same effect.



On the version at work, if I Ctrl-shift>right it will select all text upto the next "/" slash, which can be handy as quick & dirty way to grab a portion of a string, for example the bit of a URL you want, or a date.

Anyway sorry for banging on at length about spreadsheets but your problem struck a chord as I use excel in my macros so much!

[philranger]

Thanks, everything helps, and I'll definitely consider the ctrl-shift-home! EDIT: Macro doesn't work with this anymore as clipboard really comes back as empty!

I am now banging my head against another issue: I need to add line feeds inside the string variables in mmm, which isn't obvious!

[bigjim]

Mar 10, 2023 19:19:20 GMT philranger said:

Thanks, everything helps, and I'll definitely consider the ctrl-shift-home! EDIT: Macro doesn't work with this anymore as clipboard really comes back as empty!

I am now banging my head against another issue: I need to add line feeds inside the string variables in mmm, which isn't obvious!

Interesting.. You mean selecting a cell with text, hitting F2 to go into edit mode (cursor by default is positioned at the END of the text), then hitting Ctrl-Shift-Home *doesn't* select all the text and position the cursor at the start of the text for you?

That's cool to know - like i say the differences I've seen are very minor, like where if you F2 then Ctrl-Shift-arrow left/right in some versions selects all, in some it will only select up to the next slash, but I thought that Ctrl-Shift-Home/End were pretty universal across all the versions out there.

Thanks for that info! All my macros basically involve taking data out of excel and using it in another system, or vice-versa and I often test stuff at home on one version and then run at work on another, which is how I noticed the example I gave earlier so other minor differences like this can be valuable to me.




Your issue with adding CRLF to a string variable got me going down a rabbit hole last night for no good reason

I don't actually know of a good solution, and this would be a horrible dirty workaround but IIRC in excel (if you're actually using excel in the macro you're talking about) you can add a CRLF by using Char(10) in a formula.
It also doesn't address your issue of having the string variable directly contain the line break, it's only a quick & dirty way of inserting one into a text string you can copy to windows clipboard.


So if I type literally into a cell; ="test"&CHAR(10)&"test"
then it comes out as...


test
test

..and if you then convert the formula to it's value using F9 or somesuch, this string can be pasted into notepad and it retains the line break which got me very excited!
At least this could be handy if you're taking the source strings from excel, or inputting them into excel.

But when I tried to test this theory in a test macro I ran into issues, because when I used RUN ACTION | KEYPRESS the () characters don't seem to output, which breaks the use of CHAR(10).




1 | RUN ACTION | DEFINE STRING VARIABLE | %STRING1%::"Test1"
2 | RUN ACTION | DEFINE STRING VARIABLE | %STRING2%::"Test2"
3 | RUN ACTION | DEFINE STRING VARIABLE | %STRING3%::CHAR(10)
4 | RUN ACTION | WAIT MILLISECONDS | 500
5 | RUN ACTION | KEYPRESS | =
6 | RUN ACTION | KEYPRESS | %STRING1%
7 | RUN ACTION | KEYPRESS | &
8 | RUN ACTION | KEYPRESS | %STRING3%
9 | RUN ACTION | KEYPRESS | &
10 | RUN ACTION | KEYPRESS | %STRING2%

(I know it's long code and not necessary to have so many lines, but I used them just for an easy to read visual example)
I would expect the output of this test to be...

="Line1"&CHAR(10)&"Line2"
Which excel would then treat as a function due to the leading = and wrapping the text strings in quotation marks, but instead it comes out as...

="Line1"&CHAR10&"Line2"
[note the lack of () brackets in the output above]

Anyway you've possibly looked at this already, but if MMM can be forced into keypressing the brackets, it was at least a line of inquiry that might be worth looking into?
Sorry I couldn't have been more help, I'll be interested to see how you solve it!


*EDIT TO ADD* I notice an older thread here that discusses brackets in Keypress actions - I might have a play later today to see if this resolves the issues i had in testing..
But of course this long-winded idea still is only of use if you're taking data from or inputting to excel and is probably very cumbersome to write if you need very many strings to contain line breaks.


*EDIT-EDIT* Recording keypress shift+|9 for "(" and shift+|0 for ")" does insert brackets as-per the thread linked, and the test output into excel works, and the result then can be copy/pasted into notepad with line break present.

I wonder if the formula output of two strings separated by CHAR(10) can be copied to windows clipboard, then %clipboard% passed to a MMM string variable, and the next time you use that variable it still has the line break?

Will have a play later!

[philranger]

My macro don’t work anymore because if it’s on an empty cell it really brings back an empty clipboard, which, as far as I know, can’t be verified in MMM.

Great info, thanks!

I like your workarounds. May try them (not this week though!)