AutoHotkey Community

[ealerner]

Okay, here's a kludgy little script I wrote to create and run another script which will cut or copy a cell in Excel, paste or paste special value that cell to a new cell, and then move to another cell afterwards so you can run the script again on another cell if desired.

As I'm no good at PERL, I use Excel as my primary text processing tool, and I'm constantly performing cut, copy, and paste operations to, for example, convert a table of data from row to comumnar format. i.e.:

From This:

abc
def


ghi
jkl

To This:

abc def
ghi jkl

That said, this utility is really only a start, as it only works on one cell at a time and not a range of cells, and would be more effiicent transposing rows to columns if it implemented the copy & paste-special-transpose commands.

Nevertheless, I thought it would be an interesting exercise to write an AutoHotkey script that generates another Autohotkey script and then runs it.

I'm a often forget to set the relative reference button in Excel Macros (very poorly documented) or assign Excel macros to a hotkey, so this helps with those issues and allows the script to easily be run on any spreadsheet.

Unfortunately, the script is limited due to Excel's incomprehensible "feature" of not allowing a "Paste Special" command following a "Cut" command. Paste Special in Excel only works following a "Copy" command, at least in Office 2000 and earlier.

This script is likely inefficient in the extreme. My motive was to get it working and learn, so it likely can be optimized a great deal both by abbreviating commands, changing process flows, and using different parsing or string commands. Nevertheless, it works for what it does.

If nothing else, maybe it will serve as an example of bad coding. At best, it may give someone a new idea on how to use Autohotkey for their application.

Ok, enough banter.

To use the script, edit the "Working Directory" line to a folder where you want the new generated script to be placed.

After running the script, it will automatically create and run a new script to perform the desired operations. The new script maps the commands to the hotkey "<Control><Alt>7" (that's ^!7 in the script). Obviously, you can change this to whatever hotkey you want. For that matter, feel free to change anything you want in the script, redistribute, whatever.

---------------
;Generate Excel Macro Script

SetWorkingDir, C:\Documents and Settings\USERNAME\Desktop\Scripting\Scripts.Uncompiled\New Excel Macros

StringCaseSense, Off


;Introduction & Description or Macro


MsgBox, 1, Introduction, This Utility Will Generate a Macro to "Cut" or "Copy" the Current Excel Cell and then "Paste" or "Paste Value" the Data to a New Location on the Same Sheet.`nNote that the Cut / Paste Special Combination is Not Supported By Excel`n`nPlease Click "OK" to Begin.`n`nYou May Also click "Cancel" at Any Time to Quit.

IfMsgBox, Cancel, Exit


; Get Input from User on Whether the First Operation is a "Cut" or a "Copy"


Loop
{
InputBox, OP_1, Enter First Operation to Perform, Please Enter CT for "CUT" or CP for "COPY"
If ErrorLevel=1, Exit
If OP_1 = CT
{
OP_1 = {CTRLDOWN}x{CTRLUP}
break
}

If OP_1 = CP
{
OP_1 = {CTRLDOWN}c{CTRLUP}
break
}
}


; Get Input from the User on The Direction and Distance to Move from the Cell after "Cutting" or "Copying" It


Loop
{
InputBox, INPUT_STRING, Enter Movement After "Cut" or "Copy", Please Enter the Movement to Occur After the Cell is Cut or Copied.`n`nLetters Indicate Direction. Numbers Indicate How Far`n`nPlease Use the Format: "R7`,D9"
If ErrorLevel=1, Exit

IfInString, INPUT_STRING, `,, break
MsgBox, Error In Format - Please Re-Enter Movement in the Format:`n`n"L7`,U3"`n`nPut a Comma in Between Each Pair`, and Remove Spaces & Quotes.
}


; Convert User Distance Input String into Macro-Standard Key-Name Form (i.e. "{Right 7}{Down 4}")


MOVEMENT_STRING = {%INPUT_STRING%}

StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, `,, }{

StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, L, LEFT%A_Space%
StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, R, RIGHT%A_Space%
StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, U, UP%A_Space%
StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, D, DOWN%A_Space%

MOVEMENT_STRING_1 = %MOVEMENT_STRING%


; Get Input from User on Whether the Second Operation is a "Paste" or a "Paste Value"


Loop
{
InputBox, OP_2, Enter Second Operation to Perform, Please Enter P for "PASTE" or V for "PASTE VALUE"
If ErrorLevel=1, Exit
If OP_2 = P
{
OP_2 = {CTRLDOWN}v{CTRLUP}
break
}
If OP_2 = V
If OP_1 = {CTRLDOWN}x{CTRLUP}
{
MsgBox, Error - You Cannot "Paste Special" a Cell that Has Been "Cut."`n`nEither Choose "Paste" Now`, or Cancel And Choose to "Copy" Instead of "Cut" the Cell.
Continue
}
{
OP_2 = {ALTDOWN}esv{ALTUP}{TAB 4}{ENTER}
break
}
}


; Get Input from the User on The Direction and Distance to Move from the Cell after "Pasting" It


Loop
{
InputBox, INPUT_STRING, Enter Movement After "Paste" or "Paste Value", Please Enter the Movement to Occur After the Cell is Pasted.`n`nLetters Indicate Direction. Numbers Indicate How Far`n`nPlease Use the Format: "R7`,D9"

If ErrorLevel=1, Exit

IfInString, INPUT_STRING, `,, break
MsgBox, Error In Format - Please Re-Enter Movement in the Format:`n`n"L7`,U3"`n`nPut a Comma in Between Each Pair`, and Remove Spaces & Quotes.
}


; Convert User Distance Input String into Macro-Standard Key-Name Form (i.e. "{Right 7}{Down 4}")


MOVEMENT_STRING = {%INPUT_STRING%}

StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, `,, }{

StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, L, LEFT%A_Space%
StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, R, RIGHT%A_Space%
StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, U, UP%A_Space%
StringReplace, MOVEMENT_STRING, MOVEMENT_STRING, D, DOWN%A_Space%

MOVEMENT_STRING_2 = %MOVEMENT_STRING%


; Create Macro Script and Append to File


;Generate Fairly Random Macro Name By Appending on Current Hour and Minute to End

File_Name = New Excel Macro.%A_Hour%.%A_Min%.ahk

Generated_Macro = ^!7`::`n{`nSend`, %OP_1%`nSend`, %MOVEMENT_STRING_1%`nSend`, %OP_2%`nSend`, %MOVEMENT_STRING_2%`nReturn`n}

FileAppend, %Generated_Macro%, %File_Name%

Run, %File_Name%

Exit

[Chris]

This is the first script I recall seeing that generates other scripts. It looks interesting; thanks for sharing it and your comments.

[Guest]

Thanks Chris,

Anytime

[BoBo]

Hi ealerner/Guest,
thx for sharing this. A nice forum feature (especially if > 2 lines of code) to publish code as code, using the code tag/button.

This way it will separate the introducing text/description from the code and (IMHO the main benefit) you'll keep any line indents, which makes it easier to read your code eg.