Export excel data to individual vcf files

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
I have an address list with the following fields:
Name (includes: First Name, Last Name)
Home Address (includes: address, street name)
Home City
Home State
Home County
Home Postal Code
Home Phone
Cell
Work Address
Work City
Work State
Work County
Work Postal Code
Work Phone

I need to export the list to vcf files (one file per line) with the following format:

BEGIN:VCARD
VERSION:3.0
FN:Panera Kingston
N:Panera Kingston;;;
TEL;TYPE=WORK:613-536-1655
ADR;TYPE=WORK:;;88 Dalton Ave;Kingston;ON;K7K6C3;CA
END:VCARD

Requirements:
The text format must be ASCII.
Only fields that are filled should be exported to the vcf file.
The individual vcf files can take their name from the "Name" field.

I appreciate your help.

hip
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,160
what are these two lines?

FN:Panera Kingston
N:Panera Kingston;;;
 

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
what are these two lines?

FN:Panera Kingston
N:Panera Kingston;;;


FN: is the "Full Name" field; in this case the name is Panera (the restaurant)
N: is the "Name" field; this would typically be populated Kingston Panera (or Smith Bob), But I think I would prefer it to be Bob Smith.

Thanks

hip
 

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,160
try this

Rich (BB code):
Sub Creat_Txt_File()

mypath = "c:\results\"

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
myfile = Cells(r, "A")

Data = "BEGIN:VCARD" & vbCr & "VERSION:3.0" & vbCr
Data = Data & "FN:" & Cells(r, "A") & vbCr
Data = Data & "N:" & Cells(r, "A") & ";;;" & vbCr
Data = Data & "TEL;TYPE=WORK:" & Cells(r, "N") & vbCr
Data = Data & "ADR;TYPE=WORK:;;" & Cells(r, "I") & ";" & Cells(r, "K") & ";" & Cells(r, "M") & ";" & Cells(r, "L") & vbCr
Data = Data & "END:VCARD"

Open mypath & myfile & ".vcf" For Append As #1 
Print #1 , Data
Close #1 
Data = ""
Next r

End Sub

hth,
Ross
 

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117

ADVERTISEMENT

Ross,

I made changes where I understood (or thought I understood the code), i.e., added line feeds at the end of each line

I tried a couple of ways to add the missing fields ("Address", "City", "State", etc) but couldn't figure it out.


Thanks,

hip


Rich (BB code):
Sub Creat_Txt_File()

mypath = "G:\"

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
myfile = Cells(r, "A")

Data = "BEGIN:VCARD" & vbCr & "VERSION:3.0" & vbCr & vbLf
Data = Data & "FN:" & Cells(r, "A") & vbCr & vbLf
Data = Data & "N:" & Cells(r, "A") & ";;;" & vbCr & vbLf
Data = Data & "TEL;TYPE=WORK:" & Cells(r, "N") & vbCr & vbLf
Data = Data & "ADR;TYPE=WORK:;;" & Cells(r, "I") & ";" & Cells(r, "K") & ";" & Cells(r, "M") & ";" & Cells(r, "L") & vbCr & vbLf
Data = Data & "END:VCARD"

Open mypath & myfile & ".vcf" For Append As #1 
Print #1 , Data
Close #1 
Data = ""
Next r

End Sub
 

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,160
What Column is the Address in.

If its column L the used cells(r,"L") to get the address.
 

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117

ADVERTISEMENT

What Column is the Address in.

If its column L the used cells(r,"L") to get the address.

Ross,

That worked really well, thanks.

One last question, If Col A is "First Name", and Col B is "Last Name"; what would the code be to export "Full Name (First Last) in the file?

Thanks for your help, it is very much appreciated.

Regards

hip
 
Last edited:

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
cells(r,"A") & " " & cells(r,"B")

Is seems so simple when you know how.

By way of explanation we just got a VW Tiguan with a NAV system. I can type destination on the screen but thought it would be easier to import them. The problem is that while the NAV system has the facility to import no one is capable of telling me the precise format for the addresses.

Much jiggery-pokery and I determined the format. Creating the files in the proper format was another issue that with your help is is now solved. There were lots of programs to export from csv to vcf formats but none that seemed work with the NAV system. Now to figure out how to import POI's (same problem, no info on file format).


Thank you 10^6 for your help and patience.

hip
 

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
Ross,

I have to thank you again, this is just so simple and work perfectly.

One more ask. Is there a way that I can delete all the files in the "mypath" folder before writing? Currently I am appending data to each file.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,617
Messages
5,625,883
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top