Using Excel tables to define points on a curve

[PotterHarry]

Hi

I gather from one of Cory's posts that it is possible to extract values for variables from an excel table. I am trying to construct a macro that will draw curves, or in fact any shape, in a paint program. Wonder if you could help me work out how to implement this, please?

I have taken a look at one of cory's links to http://bluepointdesi...ta/Default.aspx

This is what I need to figure out. The properties of my curve can be predefined by the numbers in a column in Excel, and I can maybe get the macro to pull the info in an array from excel ?

 

Thanks!

Ian

[Cory]

What part are you having problems with?

[PotterHarry]

Hi Cory

I am going to struggle with this without some serious help! I can write a little script that gets the mouse pointer to move to a certain location of the screen. Using variables rather than absolute values. I want to pull the variables from a table in excel, such that the mouse moves to incremental positions. From your article (BTW the link to the download didn't work for me) I gather it's possible to extract a column of data from excel, and then to strip out the tabs. I'm at work now, but will have a go at this later this evening. Thanks Ian

[Cory]

Thank you for pointing out that my link was broken. They keep changing my web server an de-registering the MEX extension. I stuck it in a zip archive to prevent the problem in the future.

 

I think my explanation was pretty good on the webpage. Please read it, download and try my sample and then tell me where you'er confused. I will tell you one thin and that is that the macro doesn't really 'strip out the tabs'. It uses the tabs as delimiters. A delimiter is a character used as a boundary in a string of data. It is used in a process known as splitting. A common way of parsing string data. Please take a moment to read the help section in MEP titled "Split String". You also need to understand the concept of array variables.

[PotterHarry]

Thanks Cory.

I downloaded a hex editor and in Excel I made a very small table which encompass the full range of values for my mouse coordinates (Approx range = 90 to 1600)

 

The table is:

20 300 1500 95

1000

 

If I copy/paste this into the hex editor I see 00 32 30 0d 0a 33 30 30 0d 0a 31 35 30 30 0d 0a 39 35 0d 0a 31 30 30 30 0d 0a 20..300..1500..95..1000..

So I see I will need my macro to somehow recognise and separate the numbers between the full stops (periods). These are the boundaries in your last post?

 

I downloaded your macro and when I run it I get this result ...

00 53 53 4e 09 4c 61 73 74 20 4e 61 6d 65 09 46 69 72 73 74 20 4e 61 6d 65 0d 0a 32 30 2d 2d 09 09 0d 0a 33 30 30 2d 2d 09 09 0d SSN.Last Name.First Name..20--....300--...
2a 0a 31 35 30 2d 30 2d 09 09 0d 0a 39 35 2d 2d 09 09 0d 0a 31 30 30 2d 30 2d 09 09 .150-0-....95--....100-0-..

I'm puzzled at the gaps in the large numbers?

Thanks for your help, Cory. I just took a look at the split string help file and it looks pretty straight forward (Famous last words) . Running out of time, so will try again tomorrow. Thanks!

[Cory]

Your table "20 300 1500 95" can't be a table in Excel. there are spaces in between. If those were clipped from 4 Excel tables they would have tabs between them.

 

FYI 0x20 is space and 0x09 is tab.

[Cory]

Now the second block in your post has tabs. Reading across I see 0x53 (S) twice then 0x4e (N) then 0x09 (tab). This continues two more cells for Last Name and First Name. Then there is a 0x0d and 0x0a. These are the Newline characters Carriage Return and Line Feed. So with my macro this whole line would be read in and split into 3 elements of an array. Then the next line for the next one. The next line is 0x33, 0x30 (20) and then 0x2d which is a dash. Now the odd thinga bout this is that there are two of them. This sound like the famous MS Word but where there's a special character for a long hyphen which is often converted into two. But it's very strange Don't know what to make of that. Anyway you can continue to read though like this to see what things are. Your editor should have an ASCII table reference someplace or you can look it up online.

 

If you read my article on hex editors you should understand the concept of non-printing characters like the Newline combination. In my editor when it's displaying text on the right and hex on the left it uses a period to indicate a non-printing character. Kind of a placeholder. So if you look after the 20-- there are 4 periods. That's because there are two tabs, a carriage return, and a line feed. This indicates there were two empty cells in the second line.

[PotterHarry]

The input to the hex editor, and to your macro was a vertical column of numbers in Excel. This is what I meant by a table. In the first instance I just pressed ctrl/c and then pasted into the editor. In the second I used the commands from your macro. I can't get access to my PC just now to follow your suggestions, but in the meantime can I just ask you ... does the definition of an array stipulate that the number of digits in the items in the array must be the same, such that for example 1234 2345 3456 4567 would work, but 1234 234 3456 4567 would not? Thanks.

[Cory]

You can't copy the example in the webpage. That is not Excel. My example is copying from Excel, not a web browser. One of the first things you need to learn about text data in applications is that just because it looks similar doesn't mean the data behind it is.

 

An array is exactly the same as it's base type. So in this case we will have text. If I create a text variable %Name% it has the normal stipulations for that type. No where in those limitations is a limit on the number of characters. You can read the help file on the text type. An array is just a bunch of them glued together and a common name. If it's an array they will be %Name[1]%, %Name[2]%, and so forth.

[PotterHarry]

No I did use excel. I created a new workbook, and from cell a1 to a5 I entered the five different numbers. I then selected them and pressed ctrl/c.

[Cory]

On the first 5 I see that. Since there is only one column there are no tabs. So in this case you can simply split on newline.

[Samrae]

When cells are copied from Excel CR and LF characters are added to the content of each cell. Let's examine your example:

00  32 30 0d 0a 33 30 30 0d 0a 31 35 30 30 0d 0a 39 35 0d 0a 31 30 30 30 0d 0a

I do not know where 00 (character NUL) comes from so I won't try to explain it here.

 

These are hexadecimal numbers.

 

32 is the hexadecimal value for the digit '3' 30 is the hex value for the digit '0'. 0d = CR and 0a = LF. So your sample data is:

NUL 3 0 CR LF 3 0 0 CR LF 1 5 0 0 CR LF 9 5 CR LF 1 0 0 0 CR LF

 

When viewed it might look like this:

30
300
1500
95
1000

Cory says he expected to see TAB characters between fields. A TAB or HT is hexadecimal 09. Perhaps whether cells are separated by CRLF or by a TAB depends on the setting in the spreadsheet. I will leave that for you to discover.

 

It is possible to use the Split String command to separate at the CRLF.

Variable Set to ASCII Char 13 to %CR%
Variable Set to ASCII Char 10 to %LF%
Variable Set String %CRLF% to "%CR%%LF%"
Split String "%InString%" on "%CRLF%" into %OutArray%, starting at 1

[Cory]

You will also need to convert the text array to an integer array since Mouse Move requires integer.

[Cory]

The table is:
20 300 1500 95
1000

In my defense this is what PotterHarry gave as an example of a table. That implies 2 rows with 4 columns. If it was one column of data his post should have looked like this:

The table is:
20
300
1500
95
1000

I think he pasted into his hex editor and copied the text from there where they were re-stacked.

[Samrae]

Only PotterHarry knows for sure what he did. I only wanted to add the explanation about his first example to help PotterHarry and to aid anyone else who reads this topic. I see that the second result he posted does contain TAB (09 hex) characters.

[Cory]

Well I thought my explanation on my webpage and here were pretty good but...

 

The null character is PotterHarry copying from his hex editor. The left pane is the table of hex values and the right is the text itself. The first column of hex values are the starting address 0x00. For some bizarre reason he has configured his hex editor to show 42 characters wide. Normally I'd expect a hex to display 16 and 42 is't divisible by anything close to 16. If you look at the large clip paste it starts with 0x2a = 42 decimal. Being zero based the address of the 43rd byte would be 0x2a IE the first character on the second line.

[PotterHarry]

Yes I have managed to confuse things here. Lesson learned ...be a lot more descriptive (perhaps with screenshots) in the initial question. I'm leaving this for the moment to maybe have a go with VbsEdit and take a different tack. The hex editor was a free one, and I simply imported from clipboard, and being the first time I have used one I simply copied and pasted what I saw on screen into my post. It's all a learning curve! Thanks fellas for your help !!!!!

[Cory]

Attached is a simple macro that will split 10 numbers in a column from Excel and display them. Make sure it's exactly 10 that you copy to your clipboard.

Column Split.mex

[Cory]

BTW you can draw graphs in Excel too. But I'm sure you know that.

 

IMHO MEP is not suited for what you want to do. I've made graphics in VB.NET before and it's amazingly powerful and much easier to use. Besides the primitive shapes there are tons of draw methods. Ellipse? No problem. Bezier curve? No problem, here's the method for that. Check out a couple YouTube videos. And Visual Studio Express is free.

[PotterHarry]

Cory that's brilliant, thanks. I'll take a look at the other stuff ... PH