Site icon Best Excel Tutorial

Creating VCARD (VCF) file Using VBA

In this Excel tutorial, I will show you how to create a VCARD (VCF) file using VBA. A VCARD file is a standard format for storing contact information, such as name, phone number, email address, etc. You can use a VCARD file to share your contacts with other applications or devices, such as Outlook, Gmail, or smartphones.

To create a VCARD file using VBA, you need to follow these steps:

Create a new workbook and insert a worksheet with the contact data. You can use any column names and order, but make sure the data is consistent and accurate.

Add a reference to the Microsoft Scripting Runtime library in the VBA editor. This library allows you to work with files and folders in VBA. To do this, go to Tools > References and check the box next to Microsoft Scripting Runtime.

Insert a new module and paste the following code:

Sub CreateVCARD()
Dim ws As Worksheet
Dim rng As Range
Dim fso As FileSystemObject
Dim vcfFile As TextStream
Dim vcfName As String
Dim vcfPath As String
Dim i As Long
Dim j As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("A1").CurrentRegion
Set fso = New FileSystemObject
For i = 2 To rng.Rows.Count
vcfName = rng.Cells(i, 1).Value & ".vcf"
vcfPath = ThisWorkbook.Path & "\" & vcfName
Set vcfFile = fso.CreateTextFile(vcfPath, True)
vcfFile.WriteLine "BEGIN:VCARD"
vcfFile.WriteLine "VERSION:2.1"
For j = 1 To rng.Columns.Count
Select Case rng.Cells(1, j).Value
Case "Name"
vcfFile.WriteLine "N:" & rng.Cells(i, j).Value
Case "Phone"
vcfFile.WriteLine "TEL;TYPE=CELL:" & rng.Cells(i, j).Value
Case "Email"
vcfFile.WriteLine "EMAIL;TYPE=INTERNET:" & rng.Cells(i, j).Value
Case Else
'Do nothing for other columns
End Select
Next j
vcfFile.WriteLine "END:VCARD"
Next i
Set vcfFile = Nothing
Set fso = Nothing
MsgBox "VCARD files created by", vbInformation
End Sub

Run the macro by pressing F5 or clicking the Run button. The macro will create a VCARD file for each row in the worksheet and save it in the same folder as the workbook.

You can now open the VCARD files with any compatible application or device and import or export your contacts as needed.

Exit mobile version