Can Macro save CTRL-V keystroke instead of pasting the value pasted during recording?



  • Hi. I am trying to record a macro that can be run on many files of the same structure. I am trying to copy a value from the file and paste it at the end of each line as the value of a new column. But when I run it on other files, it pastes the value from when the macro was being recorded instead of the value found in the current file. Is there a way to record the paste command in the macro without the value being pasted?



  • Hello @Felix-A,

    I think the only save way to do it with macro
    would be using regular expressions

    Let’s say we have the following text

    value=1     text=rt1    old=12     new=
    value=2     text=rt2    old=13     new=
    value=3     text=rt3    old=14     new=
    value=4     text=rt4    old=15     new=
    value=5     text=rt5    old=16     new=
    value=6     text=rt6    old=17     new=
    value=7     text=rt7    old=18     new=
    value=8     text=rt8    old=19     new=
    value=9     text=rt9    old=20     new=
    value=10    text=rt10   old=21     new=
    

    and we want to get the value of each line and add it to the end,
    to get the result like

    value=1     text=rt1    old=12     new=1
    value=2     text=rt2    old=13     new=2
    value=3     text=rt3    old=14     new=3
    value=4     text=rt4    old=15     new=4
    value=5     text=rt5    old=16     new=5
    value=6     text=rt6    old=17     new=6
    value=7     text=rt7    old=18     new=7
    value=8     text=rt8    old=19     new=8
    value=9     text=rt9    old=20     new=9
    value=10    text=rt10   old=21     new=10
    

    we would do

    • start recording macro
    • press CTRL+h
    • find what : (.*?=)(.+?)(\s+.+)$
    • replace with: \1\2\3\2
    • press replace all
    • press close
    • save macro with meaningful name

    The regex, of course, needs to be modified to match your data structure.

    Cheers
    Claudia



  • Claudia, thanks so much for your input. Hopefully this will clarify my question and then you can tell me if part of your method still applies.

    Say that I have created a csv file from a regular text file successfully, using a bunch of macros with regular expressions. At the end I have a file with a header like this with data as follows:

    ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED
    Green Car,0145,8,
    Blue Car,0245,4,
    Yellow Bus,0345,2,

    I need to run another macro to accomplish the following on multiple files:
    (the SHIPPED column remains blank)

    ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
    Green Car,0145,8,94608342
    Blue Car,0245,4,94608342
    Yellow Bus,0345,2,94608342

    If I record a macro to do this, it doesn’t work on other files b/c I don’t know how to tell it to record just the paste command needed at the end instead of pasting 94608342. Of course, the other files will not have the same value after PO so the file will be incorrect if it ends up with the wrong PO number in that new column.

    The first part of the macro grabs the value 94608342 which is living inside the header that begins with QTY. Then it adds “,PO” and arrows down
    Then CTRL-H does: find \r and replace with “\r,94608342”

    But it’s there that I wonder if I can tell it find \r and replace with “\r,[Paste whatever is in the clipboard]”

    By the way, I will be learning something pretty nifty from method regardless!



  • For some reason the “blank” SHIPPING column did not appear in the post.

    The end result would look something like this: (adding a space b/w commas to force it to show).

    ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
    Green Car,0145,8, ,94608342
    Blue Car,0245,4, ,94608342
    Yellow Bus,0345,2, ,94608342



  • Hello Felix,

    I don’t think that this can be achieved by using macros.
    A simple python script could do it.

    Assuming the following lines as input

    ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED
    Green Car,0145,8,
    Blue Car,0245,4,
    Yellow Bus,0345,2,
    

    would result in

    ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
    Green Car,0145,8,,94608342
    Blue Car,0245,4,,94608342
    Yellow Bus,0345,2,,94608342
    

    The script it self looks like this

    editor.beginUndoAction()                                    # set undo start mark
    
    header_array = editor.getLine(0).rstrip('\r\n').split(',')  # get the header elements
    qty_array = header_array[2].split(' ')                      # get the QTY elements
    pos = editor.getLineEndPosition(0)                          # get the end position of first line
    editor.insertText(pos, ','+ qty_array[2])                   # insert a comma and the PO at the end of the line
    
    
    max_lines = editor.getLineCount()                           # get max. lines
    for i in range(1, max_lines):                               # for each line
        _text = editor.getLine(i)                               # get the text
        if _text.count(',') == 3:                               # and check if it has 3 commas (to test if this is a valid text)
            pos = editor.getLineEndPosition(i)                  # if valid, get the end position of that line
            editor.insertText(pos, ','+ qty_array[3])           # and insert 3rd element of what we got from header
            
    editor.endUndoAction()                                      # set undo end mark
    

    Cheers
    Claudia



  • Wow, I was not expecting to branch out like this but willing to learn, especially if it can stay inside notepad(?) since the macros/scripts are run by clerical personnel on about 30 files per day. They process the files really fast actually, but adding columns of data like this will allow me to do more with them. Thank you very much. I will experiment.



  • if it can stay inside notepad(?)

    you need to have the python script plugin installed to make this work.
    As the plugin manager sometimes has some problems to correctly
    install it I would propose to use msi installer instead.

    Cheers
    Claudia



  • Hello Felix A and Claudia,

    Of course, the Claudia’s Python script is the ideal solution :-)

    However, in the particular case ( and ONLY in that case ! ) where the number of lines, under the header ( ITEM…SHIPPED ), is fixed , throughout all your files ( 3 in your example ), you can, simply, use the regex Search/Replacement, below :

    Find what    :  (?-i)(^ITEM.+PO (\d+) .+)\R(.+)\R(.+)\R(.+)\R
    
    Replace with :  \1,PO\r\n\3,\2\r\n\4,\2\r\n\5,\2\r\n
    

    NOTES :

    • There are TWO spaces, one BEFORE and one AFTER the block (\d+), in the search regex

    • If you usually work with Unix files, change the form \r\n by the simple syntax \n, instead, in the replacement part !


    For instance, with that S/R, the list below :

    ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED
    Green Car,0145,8,
    Blue Car,0245,4,
    Yellow Bus,0345,2,
    
    ITEM,SKU,QTY #891 PO 987 02-10-2016,SHIPPED
    Green Car,0145,8,
    Blue Car,0245,4,
    Yellow Bus,0345,2,
    
    ITEM,SKU,QTY #891 PO 12345 02-10-2016,SHIPPED
    Green Car,0145,8,
    Blue Car,0245,4,
    Yellow Bus,0345,2,
    

    would become :

    ITEM,SKU,QTY #891 PO 94608342 02-10-2016,SHIPPED,PO
    Green Car,0145,8,,94608342
    Blue Car,0245,4,,94608342
    Yellow Bus,0345,2,,94608342
    
    ITEM,SKU,QTY #891 PO 987 02-10-2016,SHIPPED,PO
    Green Car,0145,8,,987
    Blue Car,0245,4,,987
    Yellow Bus,0345,2,,987
    
    ITEM,SKU,QTY #891 PO 12345 02-10-2016,SHIPPED,PO
    Green Car,0145,8,,12345
    Blue Car,0245,4,,12345
    Yellow Bus,0345,2,,12345
    

    Felix, if this S/R suits you, I could give you some explanations, about it, if necessary :-)

    Best Regards,

    guy038



  • Hi Guy038: Thanks very much. This is my 2nd reply. Hopefully this one will post. I will definitely take a look. Since the number of lines will vary from file to file, it may not apply for this, but I imagine I will be able to use this for other projects as well. Would be amazing if a Macro could record a keystroke in the middle of a regex search replace operation but that is apparently not doable and thanks very much for confirming. What a great community.



  • Hi Guy, just curious if there is an easy way to make the regex flexible where it can to the insertion of the text on all lines be there 3, 6, or a 100?


Log in to reply