How to Store Contact of Excel to mobile phone?

Some time we have contact number in excel file and we want to save them in our contact details for mobile. We can’t afford time to save them all, if contact more than 30 or 20. Then I finalize then problem with following trick.

I have created VBMacro Code which read the excel file and generate one output.vcf file. Here VCard (vcf ) is vibrational call format which Contain useful information like First Name, Last Name Contact Details, Email etc. we are aware about such file, while we taking backup of any mobile contact then it will generate VCFfile for contact details we can easily share or modify as our requirement.

Specification:

Single Vcard can have more then 2 contact. The content covered between within BEGIN:VCARD and END:VCARD. All vCards must contain the VERSION property, directly after the BEGIN tag.

The content of a vCard contains PROPERTIES and their ATTRIBUTES in the following syntax: Ref[1]

PROPERTY[;PARAMETER]:attribute[;attribute]

Where PROPERTY defines the type of attribute(s) and PARAMETER can give a more specified description of the PROPERTY.

An example vCard 3.0 may look like:

BEGIN:VCARD
VERSION:3.0
N:Lastname;Surname
FN:Displayname
ORG:EVenX
URL:http://www.drjslab.org/
EMAIL:info@drjslab.org
TEL;TYPE=voice,work,pref:+91 1234 56788
ADR;TYPE=intl,work,postal,parcel:;;Wallstr. 1;Gujarat;;361440;India
END:VCARD

PROPERTIES

I have received this table form ref [1] for vCard version 3.0. They have to be separated through line breaks.

vCard 3.0 Property

Description

Example

VERSION*: the specification used to format this vCard.The value MUST be “3.0″ according to the specifications described here.
(*required)

VERSION:3.0

N*: name (semicolon separated: LASTNAME; FIRSTNAME; ADDITIONAL NAME; NAME PREFIX(Mr.,Mrs.); NAME SUFFIX) (*required)
FN*: formatted name (The way that the name is to be displayed. It can contain desired honorific prefixes, suffixes, titles.)
(*required)
ADR[;TYPE= type*[“,”type]]: (delivery) address (type = dom(domestic), intl(international)(default), postal(default), parcel(default), home, work(default))
(content fields are in order: Post Office Address; Extended Address; Street; Locality; Region; Postal Code; Country)

ADR;TYPE=dom,home,postal,parcel: ;;123 Main Street;Any Town;CA;91921;

BDAY: birthday (YYYY-MM-DD)

BDAY:1991-05-15

EMAIL[;TYPE= type*[“,”type]]: eMail address (type = aol, applelink, attmail, cis, eworld, internet(default), ibmmail, mcimail, powershare, prodigy, tlx, x400)

EMAIL:info@evenx.com
EMAIL;TYPE=internet:info@evenx.com

GEO: information related to the global position

GEO:37.144534;-122.264581

KEY[;TYPE=type]: Public key (type = X509; PGP)

KEY;ENCODING=b:SBBIEhvd2VzMS
EwHwYJKoZIhvcNAQ
kBFhJob3dlc0BuZX

LABEL[;TYPE= type*[“,”type]]: To specify formatted text corresponding to delivery address. It allows to use =0D=0A (\n) as line break for individual lines within the vCard.
(type (can be concatenated with “,”) = dom(domestic), intl(international)(default), postal(default), parcel(default), home, work(default))

LABEL;TYPE=work,dom,postal: Wallstr.1\n Berlin\nGermany

LANG: adding a language string as defined in RFC 1766

LANG:en-US

NOTE: supplemental information

NOTE:This number is operational 0800 to 1730 EST\, Mon-Fri.

ORG: name and optionally the unit(s) of the organization.
First is Organization Name, remainder are Organization Units.
org-value = *[text-value “;”] text-value

ORG: Company LLC.; Development

REV: last revision , date of last update

REV:2012-12-12T22:11:10Z
REV:2012-12-12

ROLE: role, occupation or business category within an organization
TEL[;TYPE= type *[“,”type]]: telephone number and type (type = PREF, WORK,
HOME, VOICE(default), FAX, MSG, CELL, PAGER, BBS, MODEM, CAR, ISDN, VIDEO)
TITLE: Job title, functional position or function

TITLE:Area Administrator

TZ: Time zone

TZ:-05:00

UID: represents a persistent, globally unique identifier associated with the object

UID:20120515-082345- 40000F197843-0352

URL: represents Uniform Resource Locator

URL:http://www.evenx.com

====================The VBA Code============================================

Sub converted_to_vcf()

Dim FileNum As Integer

‘FileNum is point to hold the file

Dim iRow As Double

iRow = 2

‘initially we set iRow =2 which indicate the row in excel file.

FileNum = FreeFile

OutFilePath = ThisWorkbook.Path & “\mycontact.vcf”

‘File will be crated with name mycontact.vdf

Open OutFilePath For Output As FileNum

‘Loop through Excel Sheet each row and write it to VCF File

While VBA.Trim(Sheets(“Sheet1”).Cells(iRow, 1)) <> “”

FName = VBA.Trim(Sheets(“Sheet1”).Cells(iRow, 1))

‘Get the Column 1 for Row 2nd,3rd,4th…till empty row found.

LName = VBA.Trim(Sheets(“Sheet1”).Cells(iRow, 2))

‘Get the Column 2 for Row 2nd,3rd,4th…till empty row found.

PhNum = VBA.Trim(Sheets(“Sheet1”).Cells(iRow, 3))

‘Get the Column 3 for Row 2nd,3rd,4th…till empty row found.

Email = VBA.Trim(Sheets(“Sheet1”).Cells(iRow, 4))

‘Start printing to our vcf file

Print #FileNum, “BEGIN:VCARD”

Print #FileNum, “VERSION:3.0”

Print #FileNum, “N:” & FName & “;” & LName & “;;;”

Print #FileNum, “FN:” & FName & ” ” & LName

Print #FileNum, “TEL;TYPE=CELL;TYPE=PREF:”&PhNum

Print #FileNum, “Email:” & Email

Print #FileNum, “END:VCARD”

iRow = iRow + 1

Wend

‘Close the File

Close #FileNum

MsgBox “Contact Saved to ” & OutFilePath

End Sub

===============End of Code=========================

Ok Then, Limitation for our file is we must follow the excel file as given format.

se

Image 1

Now to execute this macro we use in build Editor for Excel Macro, Open View as image 2, and follow the instruction.


Image 2


Image 3

Go to the Macro >> View Macro

1

 

Image 4

Provide name >> Click to Create.


Image 5

Copy-Paste code and click run button will generate vcf file in current folder. Now copy this vcf file to your hand set and start backup.

Citation

[1] http://www.evenx.com/vcard-3-0-format-specification

Share Post