njan1982 Posted September 9, 2016 Report Share Posted September 9, 2016 i need a macro to copy the selected excel cell into a dynamic array when i press a hot key then when i press another hotkey paste values of array one by one followed by a down key into another highlighted window please help it save a ton of my work Quote Link to comment Share on other sites More sharing options...
Cory Posted September 9, 2016 Report Share Posted September 9, 2016 http://bluepointdesign.com/macros/CRLFandTAB.aspx http://bluepointdesign.com/macros/TabularData/Default.aspx You might want to select them all and process the table. If so check out my white papers above. There are no dynamic arrays in MEP. And since each hotkey would be a macro and variables would not persist you don't need them. It sounds like you want the user to select several cells and accumulate them then paste all of them back in with another macro. I would have the capture macro take the clipboard contents and save it by appending a text file. Then when you paste it back use the Text File Process to loop though each line. Quote Link to comment Share on other sites More sharing options...
rberq Posted September 9, 2016 Report Share Posted September 9, 2016 Yes, this is something ME can do very well. I recommend exactly what Cory said: capture cells and append to a text file with one macro, process and paste from the text file with a second macro. And a third macro, to delete the text file when all pasting has been completed successfully. You could delete the text file in the second macro, but it's better to keep it around in case something goes wrong during the pasting and you want to start that phase over again. Quote Link to comment Share on other sites More sharing options...
Cartwheels Posted September 13, 2016 Report Share Posted September 13, 2016 Not sure if this would work for you but I use the ASCII file processing in ME for something similar. It is a file processing loop. You can copy a row or even a whole table and save it to a text file file. Then begin the process loop where it will take each row and can spit them out in any order you wish %t2%, %t4%, %t5%, %t1% ect. Then on to the next row and the process starts over again. I use the method quite a bit and have grown fond of it. You could even use a hot key, lf "x" is pressed, ME spits out %t4% and %t1%. It takes a little bit of playing with, but once you get it you can save yourself a ton of work!! Quote Link to comment Share on other sites More sharing options...
Cory Posted September 13, 2016 Report Share Posted September 13, 2016 Cartwheels I didn't recommend ASCII File Process in this case because he is only taking one value at a time. It's better to use The Text File Process command in this case. Quote Link to comment Share on other sites More sharing options...
njan1982 Posted September 13, 2016 Author Report Share Posted September 13, 2016 http://bluepointdesign.com/macros/CRLFandTAB.aspx http://bluepointdesign.com/macros/TabularData/Default.aspx You might want to select them all and process the table. If so check out my white papers above. There are no dynamic arrays in MEP. And since each hotkey would be a macro and variables would not persist you don't need them. It sounds like you want the user to select several cells and accumulate them then paste all of them back in with another macro. I would have the capture macro take the clipboard contents and save it by appending a text file. Then when you paste it back use the Text File Process to loop though each line. i want to reply to all there is a standard item code list of 200 items in two a4 page , the format of it is i had in excel i will receive the physical written codes in a4 pages all i need to do is select all codes in excel sometimes codes are continuous some time not i need to copy all excel cell codes into the macro code , then when i press it should enter each codes one by one below each line into software erp which is same like excel page. i need to define 200 variable i think then there is a space in the end of each excel cell so i need to trim the variable and load each cell value into a variable when i press a hotkey each variable which is not null need to be pasted one by one separated by a down arrow key-press where i am stuck is how to define to paste all non blank variable only or which approach is fail proof since there may be a maximum of 200 item and sometime i can select a long continuous excel array some time individually cell copy ( i want to select all codes by pressing down control key then all copied cells need to be loaded into macro as a trimmed list ready to be pasted continuously into the erp window one by one followed by down arrow as the erp column will not accept space) (i want to speed up this pasting process via a com connection if possible but that seems complicated) i will be happy to get a simple code only the part that able to copy a continuous excel column into separate cell values , and the part only paste the copied trimmed cells values one by one Quote Link to comment Share on other sites More sharing options...
njan1982 Posted September 13, 2016 Author Report Share Posted September 13, 2016 Not sure if this would work for you but I use the ASCII file processing in ME for something similar. It is a file processing loop. You can copy a row or even a whole table and save it to a text file file. Then begin the process loop where it will take each row and can spit them out in any order you wish %t2%, %t4%, %t5%, %t1% ect. Then on to the next row and the process starts over again. I use the method quite a bit and have grown fond of it. You could even use a hot key, lf "x" is pressed, ME spits out %t4% and %t1%. It takes a little bit of playing with, but once you get it you can save yourself a ton of work!! i prefer a simple code which populate each variable % t1% to %t200% with trimmed text but how to paste variable that is not null seperated by a down arrow i am new to this loop thing Quote Link to comment Share on other sites More sharing options...
Cartwheels Posted September 14, 2016 Report Share Posted September 14, 2016 You would have to use logic that is something like, if %t1% > 0 then text type %t1%, end if. I'm using an iPad right now so I can give you better directions tomorrow. %t1% would need to change to %t2% also, for the next code. Quote Link to comment Share on other sites More sharing options...
Cory Posted September 14, 2016 Report Share Posted September 14, 2016 I would approach it completely differently. I generally use RegEx for this kind of thing but it's a more advanced programming concept and not MEP. So if I was just using MEP I would export the Excel files to a CSV file and use some logic and ASCII File Process to identify the values and enter them into the ERP system. I always avoid interfacing with Excel when possible. Quote Link to comment Share on other sites More sharing options...
Cartwheels Posted September 16, 2016 Report Share Posted September 16, 2016 I threw together a macro using ASCII File Process. Hope this helps. BTW, i'm using ME3 not MEP but there shouldn't be any differences here. This file process seems to ignore blanks. Hope this helps! How this works is that each loop it moves down a line. So data from A4 = %t1%, B4 = %t2%, ect. When it loops A5 = %t1%, B5 = %t2%, ect. That is why you would only need %T1% // Save a text file called asc processing file.txt // Have a spread sheet called Microsoft Excel - Book 1 Temp open Activate Window: "Microsoft Excel - Book 1 Temp" Text Type: <F5> Delay 0.5 Seconds Text Type: Sheet1!A4:A204 Text Type: <ENTER> Delay 0.5 Seconds Clipboard Copy // // Settings: "Save Clipboard to a Text File" // // Settings: You will need to browse to the .txt file and choose it Clipboard Save Text: "asc processing file.txt" Delay 0.5 Seconds Text Type: <F5> Delay 0.5 Seconds // Below puts it back into excel but you would need activate what ever program when you run the real deal. //Putting it back to excel was just for testing Text Type: Sheet1!B3 Text Type: <ENTER> Delay 0.5 Seconds ASCII File Begin Process: "asc processing file.txt" (Tab Delimited Text ) Text Type: %T1% Text Type: <ARROW DOWN> ASCII File End Process 1 Quote Link to comment Share on other sites More sharing options...
rberq Posted September 16, 2016 Report Share Posted September 16, 2016 This file process seems to ignore blanks. Hope this helps! How this works is that each loop it moves down a line. Yes, I was caught by that once or twice. If a line contains only blanks, the file process returns it to the macro. But if it contains nothing but a carriage-return-line-feed, then the macro logic never sees that line. I got around that by putting an extra character, like a period, at the beginning or end of every line, then stripping it off as I processed each line. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.