Export excel data to individual vcf files

[hip2b2]

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

 

[rpaulson]

what are these two lines?

FN:Panera Kingston
N:Panera Kingston;;;

 

[hip2b2]

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]

try this

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]

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

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]

What Column is the Address in.

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

 

[hip2b2]

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

 

[rpaulson]

cells(r,"A") & " " & cells(r,"B")

 

[hip2b2]

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]

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.