Draw a Custom path on a map based on user input using VBA??

[willis005]

Hi,



I am working on an excel sheet to display and optimize helicopter movement between 80 different locations, # of persons transported , fuel consumption, etc.

on a userform the user will enter location 1, location 3, location 5, etc which is the locations to be visited for the date, using Dijkstra's Algorithm the program will find the shortest route and display it in different textboxs first location in first box second location in second box , etc. that part i have done successfully

Problem:
Now using the input from these text boxes , when the user hit a command button i want to draw a line connecting this locations on a custom map i got in .dwg or .dxf or.jpeg or .bmp or even i can redraw if needed. since its 80 locations , it's almost impossible to predefine all possible combination , i have no clue from where to start :dunno

[Tommy]

Hi willis50, :hi:

Welcome to the board!

I can work in .dwg or .dxf format easily. I need more information to continue.
How are the destinations entered? GPS, Longitude and Lattitude? X and Y? Name of city and we have to figure the rest out?

[willis005]

Thanks Tommy ! u r a life saver :)

Well the map i am working on in .dwg is custom made and not to scale , just a 2D illustrative map , so we will have X & Y from autocad, thou there is no name of city to use google maps , as the locations are offshore oil production platforms , for calculation of shortest route i had to manually enter the distance between different locations and got it to work "which should be used as inputs to draw the path"

Thanks !

[Tommy]

So I need the dwg file so I can get the points, I will need some sample data, and I need the forms and such that you have done. Is this in excel?

One more question are we goiing to have to deal with fuel consumption or Weight?

[willis005]

Hi Tommy,

please find the attached
thanks in advance !

[willis005]

well fuel consumption is not an issue here .. thanks thou !

[Tommy]

Hi willis005,
I looked at the drawing file and it has a lof of xreferences that I don't have, so I don't know exactly where the cords are for the platforms. I don't think this is an issue since this is just showing the route. (right?) Now I have in the past drawn on the userform to show sample shop details for beams and columns, so this should be ok to do on a userform if you want. :)

I will need a day or 2 on this so please be patient.

[willis005]

Hi Tommy,

Thanks for ur quick response ! well u r right this is just showing the route for the traffic coordinator, so missing references is not an issue..

take ur time man , i appreciate ur care to help :)

[Tommy]

I have found 2 wells not designated with a number. They are between 34 and 101, and the other one is by 34.

I have extracted the cords from the drawing. I selected the number close to the insertion point of the xref, I think I have the hard parts done!

Now for the question : How are you going to send me the plots? Could you send me an array of numbers the numbers(it should be strings since some of the platforms are named X11 ....) being the platforms?

[willis005]

i checked for towers not designated with numbers , i could only see one which is coming by a red line from 12 , that will be tower number "15" ..

i am not quit sure i fully understand ur question here , what do u mean by "array of numbers the numbers?? " sorry lots of numbers here .. so which ones u need ? can u please explain more ?

[Tommy]

Here is what I have so far. I not happy with it but before I continue too far I want you to look at it and see what you think. I thing I have in mind is to hide all of the platforms not on the route.

open the attached file, enable macro and pick the button on sheet one. The data you see there is extracted from the drawing you sent me. I dupliced 34A for some reason. Anyway let me know what you think. If you want me to code it to open acad and draw the lines instead just let me know.

[willis005]

Hi Tommy ,
Thanks for ur effort and time !

- well i noticed the drawing is mirrored along the X-axis ;)
- can we include the big platform compound next to the text USSC in the .dwg? just a single symbol for it as this is the main base platform which usually 'but not necessary' the path will start from.
- some text are hidden behind other text or platforms, will it help if i edit the .dwg based on ur drawing to accommodate all the text ?
-hiding the platforms not on the path is not recommended as traffic guys might need it as reference in case they entered a manual path based on job priority
- also coding it to open acad is difficult as acad will not be installed on aviation and traffic PC's .

i appreciate it buddy !

[Tommy]

HA Yea it was quick and dirty wanted to see if I could first and go from there. I also wanted to make sure this was ok before I sit down and get serious. The rest is somewhat easy it will take time though. I can adjust the cords until we can read it.

[willis005]

Thanks alot and sorry for the late reply .. i think that will be good ! it's better than what i pictured :)

[Tommy]

Hi willis005,
here it is. I would suggest that you play around with it some. The overwriting is pretty bad. You can play with the images on the userform to see how it works, you can also change the image I just put some color in a square and inserted it. I have a location for the ussc, but I do not draw lines to it. All of the platforms are located on the columns A being the x cord B being the y cord and C being the name of the platform. In cell 2F are the platforms to be visited. Once the form is displayed pick the show me the way button and it will draw the path. I have limited this to 98 platforms, this can be expanded if needed.

Well after you play with it for a while let me know if you need further help. All that is left to do is play with it until you get the cords arranged into and acceptable layout.

[willis005]

Thanks A million Tommy .. that's brilliant !!! I will try to do some adjustments here .. and will contact you for any further help.

I appreciate your help ! Thanks !