Marlysa Posted December 11, 2009 Report Share Posted December 11, 2009 @Avic_weo After reading your previous messages, I'm not sure to understand exactly what you want. Is it ? 0 = General (only use this if you only have one number for that particular contact)1 = Home 2 = Work 3 = Mobile Because I consider I unse only 4 categories: Home, Work, Mobile, Other Phone. And I prefer to the phone in the correct position than place it is "0" if I only have one Number. Could you explain how you want to organize your contacts in the AVIC phonebook? Quote Link to post Share on other sites
avic_weo Posted December 14, 2009 Author Report Share Posted December 14, 2009 Marlysa, Your approach is not bad or wrong. After all, nobody said you have to do it one way or another. But what I mean is that there could be a problem with class 0. The way I wrote the macro the first thing it does is check if there is more than 1 telephone number for the same contact. If there isn't, the variable numQual becomes 0. (lines 69 through 73 on the attachment in the first post). This means that ANY class 1, 2 or 3 will be forced to 0. So I can only use 3 classes effectively. What you are trying to achieve, is to use 4 classes, with 0 being Other Phone number. I do not have any problem with that. But you left in the code to coerce the class to 0 if the contact has only 1 phone number, meaning you could end up with a contact in the AVIC where you think it is in the class OtherPhone. My point is simply you have to decide what you want: use 3 or 4 categories. If you want 4 categories, than it would be better to take out the code with the qualifiers... Perhaps like so: For Each myItem In myItems If InStr(1, myItem.Categories, "dont-export") = 0 Then 'skip' txtQual = Left(Trim(myItem.HomeTelephoneNumber), 1) _ 'skip' & Left(Trim(myItem.MobileTelephoneNumber), 1) _ 'skip' & Left(Trim(myItem.BusinessTelephoneNumber), 1) 'skip' 'skip' numQual = 0: If Len(txtQual) > 1 Then numQual = 1 Call PrintSQLElement(myItem.FullName, myItem.HomeTelephoneNumber, 1) 'skip numQual Call PrintSQLElement(myItem.FullName, myItem.BusinessTelephoneNumber, 2) 'skip numQual Call PrintSQLElement(myItem.FullName, myItem.MobileTelephoneNumber, 3) 'skip numQual Call PrintSQLElement(myItem.FullName, myItem.OtherTelephoneNumber, 0) 'skip numQual End If Next myItem But again, that is entirely up to you. I am not the one telling people how they should organize their contacts Weo Quote Link to post Share on other sites
ndy4eva Posted February 9, 2010 Report Share Posted February 9, 2010 It works great!!! Thanks!!!! Quote Link to post Share on other sites
Hockey37 Posted March 21, 2010 Report Share Posted March 21, 2010 Just wanted to say this made my life 1,000 times easier. I have well over 600 contacts in my Outlook, both business and personal. This let me simply edit out those who I didn't want to add. Thanks again. Quote Link to post Share on other sites
Chuck88 Posted May 23, 2010 Report Share Posted May 23, 2010 It works great except one thing: french letters are not converted and shown up with boxes instead. Thank you for such a great script!!! Quote Link to post Share on other sites
Marlysa Posted June 4, 2010 Report Share Posted June 4, 2010 @Chuck88: On my french version , french letter are converted. Look at it here: [TUTO] Exporter vos contacts Outlook vers L'AVIC Quote Link to post Share on other sites
Chuck88 Posted June 5, 2010 Report Share Posted June 5, 2010 @Chuck88: On my french version , french letter are converted. Look at it here: [TUTO] Exporter vos contacts Outlook vers L'AVIC Merci beaucoup Marlysa pour ta suggestion, c'est juste qu'il faut que je m'enregistre dans votre forum juste pour télécharger le script ... Y avait-il une chance que ton script se trouvait dans ce forum ou me le PM? Merci d'avance! Quote Link to post Share on other sites
Marlysa Posted June 5, 2010 Report Share Posted June 5, 2010 Here is the french tutorial which explain how to use this script: * Export contact from Outlook to AVIC * Convert french letters * Possibility to select only one categorie of contact to export it to the avic ( If you want to export the category named "Voiture" change the script with Const CatExportAnnuaire As String = "Voiture") Here is the script: (Sorry comments and dialog boxes are only in french for the moment - If it is asked I'll translate it in English) '****************** 'Cette macro Outlook permet d'exporter les contacts sélectionnés dans Outlook vers le poste Pioneer - AVIC Fxxx ' 'Elle a été inspirée à partir de la macro de avic_weo publiée sur le Forum AVIC 411 et a été améliorée pour être compatible avec les caractères Français et la sélection d'une partie des contacts ' 'Version du 03-10-2009 - Réalisé Par Marlysa de la Lance '****************** Option Explicit ' **** Cette partie est à configurer Manuellement ' Créez un répertoire sur votre disque dur : c:\AVICPhone ' Téléchargez l'application sqlite lite ici: http://www.sqlite.org/download.html - Precompiled Binaries For Windows - sqlite-3_6_18.zip [Version dispo le 3/10/2009] ' Placez la dans le répertoire que vous avez créé sur votre Ordi Const SQLITE As String = "C:\AVICPhone\sqlite3.exe" Const SQDB As String = "C:\AVICPhone\contacts.sqdb" Const SQLfile As String = "C:\AVICPhone\contacts.sql" Const CatExportAnnuaire As String = "Aucune" '******* 'Cette variable permet de ne sélectionner qu'une partie de vos contacts dans votre annuaire Outlook ' 'Vous pouvez créer votre propre catégorie dans ce cas remplacer la valeur "Aucune" par votre catégorie 'Si par exemple vous voulez sélectionner les contacts que vous voulez exportez qui sont dans la catégorie Voiture (Bien respectez la Case) 'Remplacez alors la valeur "Aucune" par votre catégorie ici "Voiture" ' 'Const CatExportAnnuaire As String = "Voiture" '******** ' ***** Fin de la zone à personnaliser Sub ContactsToSQDB() Dim sqlCmd As String Dim retval As Double Dim Msg As String retval = MsgBox("Ecraser le fichier " & SQLfile & " (si il existe)?", _ vbQuestion + vbYesNo) If retval = vbYes Then Call ContactsToSQL End If sqlCmd = SQLITE _ & " -echo " _ & SQDB _ & " "".read " _ & Replace(SQLfile, "\", "\\") _ & """" retval = MsgBox("OK pour générer votre PhonebookDB?" & vbCrLf & vbCrLf, _ vbQuestion + vbYesNo) If retval = vbYes Then 'sqlite3.exe -echo temp.db ".read c:\\temp\\contacts.txt" retval = Shell(sqlCmd, vbNormalFocus) End If Msg = "Votre fichier Contact a été généré. Veuillez renommer le fichier " & SQDB & " selon le nom associé à votre téléphone: PhoneBookxxxxxxxxxx.db" retval = MsgBox(Msg, vbOKOnly) End Sub Private Sub ContactsToSQL() Dim olApp As Outlook.Application Dim objName As Outlook.NameSpace Dim myFolder As Outlook.MAPIFolder Dim myItems As Outlook.Items Dim myItem As Object Dim txtQual As String Dim numQual As Integer Set olApp = Outlook.Application Set objName = olApp.GetNamespace("MAPI") Set myFolder = objName.PickFolder() 'Set myFolder = objName.GetDefaultFolder(olFolderContacts) 'Set myFolder = myFolder.Folders("Contactpersonen") Set myItems = myFolder.Items myItems.Sort "[FullName]", False Open SQLfile For Output As #1 On Error Resume Next 'Print #1, ".echo on" Print #1, "drop table phonebook;" Print #1, "CREATE TABLE phonebook" Print #1, " (ID INTEGER PRIMARY KEY," Print #1, " chShowName TEXT," Print #1, " chPhoneCode TEXT," Print #1, " dwType INTEGER" Print #1, " );" Print #1, "BEGIN TRANSACTION;" For Each myItem In myItems '***** 'Sélectionne les contacts dans la catégorie que vous voulez exportez 'Et lui affecxte la catégorie: '0 si un seul N° '1 Tel Maison '2 Tel Portable '3 Tel Bureau '***** If (CatExportAnnuaire = "Aucune" Or InStr(1, myItem.Categories, CatExportAnnuaire) = 1) Then txtQual = Left(Trim(myItem.HomeTelephoneNumber), 1) _ & Left(Trim(myItem.MobileTelephoneNumber), 1) _ & Left(Trim(myItem.BusinessTelephoneNumber), 1) numQual = 0: If Len(txtQual) > 1 Then numQual = 1 Call PrintSQLElement(myItem.FullName, myItem.HomeTelephoneNumber, numQual * 1) Call PrintSQLElement(myItem.FullName, myItem.MobileTelephoneNumber, numQual * 3) Call PrintSQLElement(myItem.FullName, myItem.BusinessTelephoneNumber, numQual * 2) Call PrintSQLElement(myItem.FullName, myItem.OtherTelephoneNumber, numQual * 0) End If Next myItem Print #1, "COMMIT;" Close #1 End Sub Private Sub PrintSQLElement(Name, Phone, qualifier) Phone = Replace(Phone, " ", "") Name = Replace(Name, "'", "''") Name = Replace(Name, "ç", "c") Name = Replace(Name, "ö", "o") Name = Replace(Name, "ð", "g") Name = Replace(Name, "þ", "s") Name = Replace(Name, "ý", "i") Name = Replace(Name, "ü", "u") Name = Replace(Name, "Ç", "C") Name = Replace(Name, "Ö", "O") Name = Replace(Name, "Ð", "G") Name = Replace(Name, "Þ", "S") Name = Replace(Name, "Ü", "U") Name = Replace(Name, "é", "e") Name = Replace(Name, "è", "e") Name = Replace(Name, "ê", "e") Name = Replace(Name, "ï", "i") If Len(Phone) > 0 Then Print #1, "insert into 'phonebook' " _ & "values(Null,'" & Name & "','" & Phone & "'," & qualifier & ");" End If End Sub __avicmb.xooit.fr_t21-Exporter-vos-contacts-Outlook-vers-L.pdf Quote Link to post Share on other sites
Chuck88 Posted June 5, 2010 Report Share Posted June 5, 2010 Thank you very much. I will try it out. Quote Link to post Share on other sites
irkgreen Posted July 13, 2010 Report Share Posted July 13, 2010 Hey guys, I'm gonna try my newly exported db file with the awesome VBA code. Thanks! I was curious though about the caller ID. Do the phone numbers need to be stripped of (, ), and -'s? I've never had any luck with my custom PhoneData DB's showing an ID when someone calls me. They work fine when I pull up the contact but not when called. I can make an easy mod to the code if they just need to be numbers. Also, while I'm booted up in test mode I was going to update my splash screen. It has been a while and I can't remember how I did it last time. A search for 'splash screen' didn't help. Is it an easy menu setting? Or need to be in a specific folder? Thanks Eric Quote Link to post Share on other sites
avic_weo Posted July 14, 2010 Author Report Share Posted July 14, 2010 Hey guys, I'm gonna try my newly exported db file with the awesome VBA code. Thanks! I was curious though about the caller ID. Do the phone numbers need to be stripped of (, ), and -'s? I've never had any luck with my custom PhoneData DB's showing an ID when someone calls me. They work fine when I pull up the contact but not when called. I can make an easy mod to the code if they just need to be numbers. Also, while I'm booted up in test mode I was going to update my splash screen. It has been a while and I can't remember how I did it last time. A search for 'splash screen' didn't help. Is it an easy menu setting? Or need to be in a specific folder? Thanks Eric Correct: the numbers need to be stripped of all non-numeric stuff. There is a thread in which this is explained. Outlook tries to store the phone numbers in a country-related style, e.g. for Belgium +32 (52) 19 12 14. This won't work. What I always do is to set my Outlook country setting to Generic. Then it will let you enter the phone number in the format you want. Some work, but it pays of.. It is of course possible to have the Macro do the work for you. In the last paragraph, you could write: Private Sub PrintSQLElement(Name, Phone, qualifier) Phone = Replace(Phone, " ", "") ' get rid of more unwanted characters in the phone number Phone = Replace(Phone, "(", "") Phone = Replace(Phone, ")", "") Phone = Replace(Phone, "-", "") ' ... and so on, you get the picture Name = Replace(Name, "'", "''") If Len(Phone) > 0 Then Print #1, "insert into 'phonebook' " _ & "values(Null,'" & Name & "','" & Phone & "'," & qualifier & ");" End If End Sub Weo Quote Link to post Share on other sites
irkgreen Posted July 14, 2010 Report Share Posted July 14, 2010 I did just that. My SQL looks like: insert into 'phonebook' values(Null,'Eric Green','859???????',1); (anonymized for my privacy) But it still shows as unknown if I initiate the call from my phone. There's a thread on this you say? Quote Link to post Share on other sites
Marlysa Posted July 14, 2010 Report Share Posted July 14, 2010 @irkgreen: I couldn't test for the moment because I'm travelling in Eastern Europe (Without my car and my Avic ) but you could try to store the phone number as + 32 xxx xxx xxx Quote Link to post Share on other sites
avic_weo Posted July 15, 2010 Author Report Share Posted July 15, 2010 OK, I think the easiest thing to do is to monitor how the phone number appears on your AVIC when you recieve a call. The format in which you store the phonenumbers (and the SQL) has to match just that. The thing is when you try to call '052123456', this will work. But when you recieve the call, the number that is presented to the AVIC, will be '+3252123456' (the 'national' 0 will be replaced by the international code for the country of the caller) Now if your phonebook entry does not match this exactly, it will not work. HTH, Weo Quote Link to post Share on other sites
irkgreen Posted July 15, 2010 Report Share Posted July 15, 2010 I will have to look, but I am pretty sure its like 8591234567, which is how I have it. Calling from the US I'm not sure how this would differ. Quote Link to post Share on other sites
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.