How to Create a vCard (.vcf File) Reader with Excel VBA

Have you ever wanted to import contact details from VCF data in to an Excel sheet? Follow this article and create your own VCF reader that would import data from your VCF files into Excel worksheets

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

Let’s prepare the GUI for our VCF reader application. On Sheet1 we have to create a field that will hold the full path of our VCF file. Rather than expanding the cell width, merge cells to create this field. To allow users to select the VCF file, let’s add a “Browse” button to the sheet. We need an additional button to allow users to import the VCF data. Let’s name this button as “Import”.

The Sheet1 will now look like thisPrepare The GUI For Our VCF Reader Application

Let’s prepare the database

We will be using Sheet2 as database. All you have to do is create appropriate column headers. For this example, I am going to import Name and Organization from VCF. So I have added Column headers “Name” on Column A and “Organization” on Column B.

Sheet2 will now look like thisUsing Sheet2 As Database

Let’s make it functional

Now we have developed the Front-End for our tool and our next step is to make it functional by adding scripts. Open the VBA editor and in the Module1 paste the below script.

Now go back to Sheet1 and add the Sub “pickvcf” to the “Browse” button. Right click “Import” button and assign the macro “importvcf” to it. Save the file as macro enabled file and that is it. You now have developed a VCF reader.

Let’s test it

Click on the “Browse” button to select your VCF file. As soon as you press Ok on the File Browse window, you could see the full path of selected file appearing on the Sheet1. Now Click “Import” button and you will immediately see Name and Organization info of the Contact appearing on Sheet2.

How it works?

This script holds two Subs. Sub “pickvcf” is to allow users to browse and select their VCF File and the Sub “importvcf” is to read and import data from VCF into Excel file.

Modify it

As of now the script imports Name and Organization info from the VCF file. You can easily modify the script to import other info like Address, Telephone, Fax and Email. You can also tweak the script to read multiple VCF files in a folder and import them in a batch process.

This script works well if your Excel is not corrupted. However if you have a corrupted Excel file and want to repair it, you can use Excel recovery tools such as DataNumen Excel Repair.

Script

Sub pickvcf()
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Please select the file."
        .Filters.Clear
        .Filters.Add "VCF", "*.vcf"
        If .Show = True Then
            txtFileName = .SelectedItems(1)
            Sheets("Sheet1").Range("F8").Value = txtFileName
        End If
    End With
End Sub

Sub Importvcf()
    Dim fileName As String, textData As String, textRow As String, fileNo As Integer
    fileName = Sheets("Sheet1").Range("F8").Value
    fileNo = FreeFile
    r = 2
    Open fileName For Input As #fileNo
    Do While Not EOF(fileNo)
        Line Input #fileNo, textRow
        If Left(textRow, 3) = "FN:" Then
            Sheets("Sheet2").Range("A" & r).Value = Replace(textRow, "FN:", "")
        End If
        If Left(textRow, 4) = "ORG:" Then
            Sheets("Sheet2").Range("B" & r).Value = Replace(textRow, "ORG:", "")
        End If
    Loop
    Close #fileNo
End Sub

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc and outlook recovery software products. For more information visit www.datanumen.com

Comments are closed.