gagangoomer Posted February 19, 2013 Report Share Posted February 19, 2013 HI All, I've two questions to ask fromthe forum. 1) Firstly i've put some values in Column A of an Excel sheet. I want the macro to read each value till the time it finds a blank cell. The macro should pop-up an error message "There are no more values available". I've tried doing this but the macro is reading all the values including blank values. I've tried Repeat until and Repeat Start both options but failed to get the desired results. 2) Secondly Can i store an image in a variable in ME pro. I've a project in which i need to draft a template as per user selection and there should a particular logo depending upon the option selected on the top of the word document. Is it really possible ? Thanks in advance, Gagan Quote Link to comment Share on other sites More sharing options...
Cory Posted February 19, 2013 Report Share Posted February 19, 2013 When you copy from Excel you will at least get an invisible tab character. I recommend you add a trim command. No. BTW when you coy from Excel and read it into a variable it's in a tab delimited format. IE like a CSV except a tab instead of a comma. I often parse those into arrays with the split command and build a TSV output that can be pasted back in at one go. EG I have a macro that takes a list of SSNs and 'fixes' them if they are missing the dashes. I have the user copy the entire range or column to their clipboard and execute the macro. The macro splits each into a string array and loops thru the array fixing as necessary. Each time it appends the result to an output string followed by a CR-FL combo. when the loop finishes I put the results on their clipboard to paste back in. Of course this can be automated as well. Another trick is to copy a range of cells and save that to a text file in MEP. It is now a TSV file and you can use the ASCII File Process command to loop thru it. This is conceptually simpler for most I think but I don't like writing to the disk unless I have to. If you have any interest in these techniques please just let me know. Quote Link to comment Share on other sites More sharing options...
gagangoomer Posted February 19, 2013 Author Report Share Posted February 19, 2013 Thanks Cory for a quick respone I've not used Array's or copying a range of values in a MEP. Can you please share an example of how to use copy a range nd store it into a variable or in a an array ? Quote Link to comment Share on other sites More sharing options...
Cory Posted February 19, 2013 Report Share Posted February 19, 2013 I have some tings on my website that would be useful but I'm migrating and they're not available. Allow me a moment to move them over now but while i work on that how about we start you on arrays. An example of a string variable might be %CarModel% but this can only hold one. What's more you might not know how many or what type of values you will have. Arrays are simply a collection of like variables with the same name but an pointer. So how about you create a variable right now called %CarModel% but in the definition check the box to make it an array of 10. Now you can reference the each by it's pointer. EG set %CarModel[1]% to Corvette, %CarModel[2]% to "Elan", and so forth. See how that works? You can loop thru these easily too using a variable for the index. Create a loop for 10 times and use a counter named %C% starting at 1 and stepping 1. In the loop display a message box showing %CarModel[%C%]%. See how the integer counter variable is used for the pointer? End of lesson 1. Are you with me so far? Quote Link to comment Share on other sites More sharing options...
gagangoomer Posted February 19, 2013 Author Report Share Posted February 19, 2013 Yes, I'm with you. Cory can you explain it along with an example.I'll import it into my ME and then see what it exactly does. I know i'm expecting a lot but as i've not used array's before so its bit confusing for me Quote Link to comment Share on other sites More sharing options...
Cory Posted February 19, 2013 Report Share Posted February 19, 2013 I prefer people read and learn. Examples 'teach' in such a way as not to stick as long in memory as well. For instance if you read some material then re-write it i your own words it will stick with you far longer than just reading. Also it takes the author a lot more time to create and I'm way behind on billable hours ATM. But I'll incorporate this into the next example for the split function. Now in this example I show you you take a string with a delimiter and split it into an array. Does this make sense to you? SplitDemo.mex Quote Link to comment Share on other sites More sharing options...
Cory Posted February 20, 2013 Report Share Posted February 20, 2013 I took a break from work and resurrected one useful page for you. Click here for a snippet that will create the special characters needed to split a copy from Excel and to create TSV that can be pushed back in. Quote Link to comment Share on other sites More sharing options...
gagangoomer Posted February 20, 2013 Author Report Share Posted February 20, 2013 I check the above snippett but but couldn't get much idea about it. What are ASCII codes and where are they used ? Quote Link to comment Share on other sites More sharing options...
Cory Posted February 20, 2013 Report Share Posted February 20, 2013 You should be able to copy and paste the macro text directly into your scripting editor and see the commands. Most text is ASCII. Click the link and read. That is each letter is one byte of data. Tab for instance is 9 decimal, 0x09 hexadecimal, which are just different ways of representing the binary 00001001. 8 bits = 1 byte. BTW the "0x" in the hex is just a way of indicating that the following digits are hexadecimal, not decimal. Hexadecimal is a base 16 representation. Click here to understand hexadecimal. Another example would be the letter "a" which is 0x61 (97 decimal, 1100001 binary). I have another page I'll convert to explain this more. You don't see them but they are there. But in MEP it's nearly impossible to use the dialog boxes to reference these. EG if you try to enter the tab key in the dialog it doesn't enter a tab but rather moves to the next field. So we have to specifically set it to an ASCII value. So just like I can set a string variable to a comma "," I can also set one to the tab character. Then we can use the tab like i used the comma in my example. Chew on this for a bit while I convert my other relevant article. Is this making sense to you? Quote Link to comment Share on other sites More sharing options...
Cory Posted February 20, 2013 Report Share Posted February 20, 2013 Click here for my article on Hex and it's application to MEP. Once you're caught up and understand all this let me know and I'll show how you can apply it to tabular data from Excel or any Office application. Quote Link to comment Share on other sites More sharing options...
gagangoomer Posted February 21, 2013 Author Report Share Posted February 21, 2013 Thanks Cory..I know lil bit about this. ASCII is something the complier is converting the commands which we use in ME for its own reference. Just like we have binary language which is easy for the complier to understand. I must say you are a knowledge warehouse . I just had an idea about the binary language but the knowledge i really got to know more about how the complier works. Though i've used Hex editor only couple of times to break the protection in excel file but didn't realised how it interpreats things. Do you have an a/c on FB where i can join or follow you or may i know your website from where i can learn more. I want to learn many more things from you. Thanks for your valueable time. Gagan Quote Link to comment Share on other sites More sharing options...
Cory Posted February 21, 2013 Report Share Posted February 21, 2013 Nothing formal. Too much work to do and not enough time. I do a 'Tip of the day' for one of my clients with Office worker stuff but even that I've been neglecting. For MEP stuff it's best to request it and I can answer here or make an example on my website. I'm also available for hire if you ever need something quickly. I also like to create and teach so it's not a black box for my client. Today if I get time I'll make a simple example of how to grab a range of cells in Excel and make something useful. Quote Link to comment Share on other sites More sharing options...
Cory Posted February 21, 2013 Report Share Posted February 21, 2013 Check out this page for your final instructions. Does this make sense to you? Quote Link to comment Share on other sites More sharing options...
gagangoomer Posted February 25, 2013 Author Report Share Posted February 25, 2013 Yes, it does...Thanks for your time Cory Quote Link to comment Share on other sites More sharing options...
Cory Posted February 25, 2013 Report Share Posted February 25, 2013 My pleasure. Quote Link to comment Share on other sites More sharing options...
gagangoomer Posted February 26, 2013 Author Report Share Posted February 26, 2013 Hi Cory, SOrry for bothering you again. I'm working on a project in which i need to draft a letter. The macro will prompt to select from Option A & option B. As per the option selected it will open word document and paste a logo depending on option (A or . As per our previous discussion as we cann't store an image file in MEP, so the only option which comes in my mind is to create two different templates and save it on your system or on sharepoint (whatever is the requirment) and as per the user's respone open that blank template and paste the data which we'll fetch from the other application stored in vairables. Now the problem is while i paste the data in template(A or it takes extra spaces and change the alignment of the text entered in the document. I tried to use Trim function to remove the extra spaces but i'm still facing for both spaces and alignment. Is there any way through which i can overcome this problem or will you suggest some other way to draft a letter other than the one i mentioned above. Regards, Gagan Quote Link to comment Share on other sites More sharing options...
Cory Posted February 26, 2013 Report Share Posted February 26, 2013 I have made several macros to do the exact same thing. I created a Mail Merge document linked to a delimited text file then had the macro change the delimited file contents before opening the merge document. Voila! As soon as it opens the custom text is already there. Quote Link to comment Share on other sites More sharing options...
gagangoomer Posted February 27, 2013 Author Report Share Posted February 27, 2013 Can you share the code or the macro with me, also send the supporting docs (if any)..leme see how it works Quote Link to comment Share on other sites More sharing options...
Cory Posted February 27, 2013 Report Share Posted February 27, 2013 It's a client's property so I can't. I'd have to recreate it and I don't have time to give away ATM. Besides, it's simple to do. Just create a mail merge document linked to a TSV or CSV file and create a macro that overwrites this file and launches your document. You don't need sample code for that, just do it. 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.