Hello,

I use Excel to use MapPoint with my data.

Through this site, I am able to achieve the necessary tool for my job is to optimize rounds of my drivers.

I was able to resume many things here and adapt to my needs.

When I want to locate 430 delivery points, I encounter a bug at the end of the macro.
I do not understand why.

Has anyone's eye look at why?

This is the macro that is in the sheet ("locate addresses") and is activated by clicking on the button "Map MapPoint".

Thank you for your help.

Sorry, I'm French so the file also.

Regards,

Do you mind to also post your question in French? That would help French speakers understand your question.

Thanks!
Eric

Pour les francophones:

Bonjour,

J'utilise Excel pour utiliser MapPoint avec mes données.

Grâce à ce site, je suis en mesure de réaliser l'outil nécessaire pour mon travail consiste à optimiser les tournées de mes chauffeurs.

J'ai pu reprendre beaucoup de choses ici et d'adapter à mes besoins.

Quand je veux localiser 430 points de livraison, je rencontre un bug à la fin de la macro.
Je ne comprends pas pourquoi.

Quelqu'un at-il le regard de regarder pourquoi?

Il s'agit de la macro qui est dans la feuille ("localiser des adresses") et qui est activé en cliquant sur ​​le bouton "Carte MapPoint".

Merci pour votre aide.

Désolé, je suis français donc le fichier également.

Sorry I don't have MapPoint EU installed on this computer as the default, but I can take a look at the code.

At what line in the worksheet and/or at what line in the code does it give the error?

Also, what is the error?

Thanks!
Eric

Eric,

The code gives the error here: (red line is in yellow in fact)


'---------------------------------------------------------------------------------
'Localiser les adresses
'---------------------------------------------------------------------------------
Set objFindResults = objMap.FindAddressResults(szAddress, szCity, , szState, szZip, geoCountryFrance)
Set objLoc = objFindResults(1)


Excel open a msgbox with the text: runtime error. the desired element does not exist. Use a name or number of valid indexes. Then Mappoint try to close Mappoint.
700

I ve put the picture of the msgbox with this message. (in french so why I translate it)

And a new question, eric,

Could you tell me if its possible to import my pushpin with excel?

Thanks

Great! Can you tell what line of data in the worksheet it fails on?

If it's beyond the end of the data, then your loop criteria needs fixing.

If it's just a bad address it can't find, then you should be able to skip it. Do something like


If objFindResults.Count <> 0 Then
Set objLoc = objFindResults(1)
...
End If

Could you tell me if its possible to import my pushpin with excel?

Do you want to import pushpins programmatically or just import locations from a list in Excel? If the latter, you can just use the Data Import Wizard.

Or are you wanting to import a Pushpin Symbols?

Eric

Do you want to import pushpins programmatically or just import locations from a list in Excel? If the latter, you can just use the Data Import Wizard.

Or are you wanting to import a Pushpin Symbols?

Eric

Yes, in fact I find the symbols into mappoint not so usefull when I put 35 kind of symbol into a map.

I would like to custom my own symbol ans important them with excel.

maybe I m a liittle bit too exigent with Excel and MapPoint!

Thanks

Yes you're right

It was a bad address.

i wil test your solution to fix it.

Do you know how with excel I could put all the wrong address mappoint doesn't manage in a sheet?

Thanks

Do you know how with excel I could put all the wrong address mappoint doesn't manage in a sheet?

Yes, it's fairly simple, but you need to know your way around writing Excel VBA macros a little bit.

Use the ActiveWorkbook.Add method to create a new worksheet you wish to write out the bad addresses

Then within the loop, use a row variable to increment the rows on the "bad" worksheet each time it encounters a bad address, maybe call it "badrow".

You can use something like Range(row,1).EntireRow.Select to select the whole row and then something like BadSheet.Paste( Destination:=BadSheet.Cells(badrow, 1))

It would probably take me a half hour or so to write up and debug, if you're struggling learning Excel VBA and Basic shouldn't take more than a day?

If you get stuck, feel free to post your code and questions!

Eric

Yes, it's fairly simply, but you need to know your way around writing Excel VBA macros a little bit.

Use the ActiveWorkbook.Add method to create a new worksheet you wish to write out the bad addresses

Then within the loop, use a row variable to increment the rows on the "bad" worksheet each time it encounters a bad address, maybe call it "badrow".

You can use something like Range(row,1).EntireRow.Select to select the whole row and then something like BadSheet.Paste( Destination:=BadSheet.Cells(badrow, 1))

It would probably take me a half hour or so to write up and debug, if you're struggling learning Excel VBA and Basic shouldn't take more than a day?

If you get stuck, feel free to post your code and questions!

Eric

Eric,

thank you for using reactive as you bring me!

I will try to write the vba myself with your thinking

if necessary I will come to you to check the result.

thanks

Hi Eric,

I have done this code.

I have not error with excel but the data are not copied into the sheet determinated.

Could you have a look on?
This code is into the first one using to put the data (I don't put the code here)
Maybe you prefere the worksheet?
On Error GoTo ErrSub


Dim plage As Range
Dim Ligne1 As Integer
Dim Ligne2 As Integer
Ligne1 = 11
Ligne2 = 11

Exit Sub
ErrSub:
If Err.Number = -2147217383 Then
Set plage = Range(Cells(Ligne1, 2), Cells(Ligne1, 7))
plage.Copy Sheets("adresses fausses").Range(Cells(Ligne2, 2))
Ligne2 = Ligne2 + 1
Resume MoveOn
End If
MsgBox " Impossible de localiser des adresses. Merci de consulter la table des adresses fausses " '& Err.Description
Err.Clear

thanks

I Eric,

I have changed some lines:
ErrSub:

Sheets("Localiser des adresses").Select
Range("B" & Ligne1 & ":G" & Ligne1).Select
Selection.Copy
Sheets("Adresses fausses").Select
Range("B" & Ligne2 & ":G" & Ligne2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Localiser des adresses").Select
Ligne2 = Ligne2 + 1
Resume MoveOn

But I have an error 1004 when the line into the sheet "Adresses fausses" should de selected

Do you know why?

Thanks

Yann

Yeah I guess I would need to see the sheet.

It is trying to select a Named Range. Do you have a named range called "Adresses fausses" ?

What is the error description?

Eric,

When I translate the message in english:

Select method of range Class Failed

The next line is in Yellow:

Range("B" & Ligne2 & ":G" & Ligne2).Select


702

Thanks for your help

Yann

When you get to that line, can you go to the Immediate Windows at the bottom and type:

? "B" & Ligne2 & ":G" & Ligne2

I think it's going to report:
B11:G11

but let's see

Eric,


I found the bug:

Because the past is not in the same sheet, I need to precise the sheet before!

Sheets("Adresses fausses").Range("B" & Ligne2 & ":G" & Ligne2).

Ah yes, that makes sense. Good job!