Jump to content
Macro Express Forums

Recommended Posts

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

Link to comment
Share on other sites

  1. When you copy from Excel you will at least get an invisible tab character. I recommend you add a trim command.
  2. 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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 B).

 

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 B) 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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...