Macro that will search external text file 1

[Scotsman71]

Okay...I'm doing a small project...basically I have a list of my friends addresses. Lets say 20 addresses. Whenever I wish to write them a letter, I have to key it in manually, or manually copy + paste from the text file I have.

I came up with a solution...right now, if I run a macro it will prompt me for the users first name, and it'll look inside the code of the macro to find that name, and copy the lines associated with it and paste it in.

However, I'd like to modify this further. I'd like to have all the addresses in a separate document(txt file probably), and so when I run the macro, it would prompt me for the persons first and last name(as it does now), and then it would look through the text file, locate the name and address, and paste it in. Has anyone seen a macro like this before, or any tips on how to do it? Been having problems executing it.

 

[ssVBAdev]

Store the addresses in a .txt file with, say:

Last Name, First Name, Street, City, ....

Then load the text file, for input, using a Do While Not EOF loop and load the values into an Array.
(the array is handy to load the .txt file first. Then you can poplulate a puldown box with all of the 'available' names from the text file)
Then search the array for the names you want and extract them into your code.

 

[Scotsman71]

Thanks for replying so quickly....now I just have to figure out how to do that hehe Do you know of any examples out there I can use to work from? I'm still a beginner here.

 

[ssVBAdev]

I have examples but I'll need to refine them abit so you'll understand them ) Give me a little bit.

 

[Scotsman71]

Awesome. Thank you very much

 

[ssVBAdev]

Well, I can't find anything that i can adapt real quick so I threw this together...

Your text file must be separated by commas and the data must be in quotes. For example, my test file was C:\Address.txt and the contents are as follows:

"Bill", "Smith", "123 Street", "Detroit", "Michigan"
"John", "Doe", "456 Avenue", "Whitehorse", "Yukon Territory"

Then, add the following code to a form that has a combobox called "ComboBox1" (default name) [you may need to edit the code as I'm not sure how to format code in this forum yet.]:

Private Sub UserForm_Activate()
Dim intContact As Integer 'Keeps the number of contacts
Dim AddressArray()
ReDim AddressArray(1 To 5, 1)
'The first dimension of AddressArray is
'Location along the line of the data
'which is separated by a "," (comma)
'The second dimension of the AddressArray is
'the number of contacts (or lines) in the file.
'We set the number at 1 just to dimension the array.
'You can set this higher of course.

intContact = 1 'Start the count at 1

Open "C:\Address.txt" For Input As #1
'go as far as the end of the file
Do While Not EOF(1)
'we need the error check because the loop will actually
'go past the end of the file by one line to ensure it
'is the end of the file
On Error GoTo DoneLoading
'Load all the info into the array, one line at a time
Input #1, AddressArray(1, intContact), _
AddressArray(2, intContact), _
AddressArray(3, intContact), _
AddressArray(4, intContact), _
AddressArray(5, intContact)
intContact = intContact + 1
If intContact > UBound(AddressArray, 2) Then
'If the array is to small for the number of contacts then
'we need to enlarge the second dimension of the Array
ReDim Preserve AddressArray(1 To 5, UBound(AddressArray, 2) + 1)
End If
Loop
DoneLoading:
Close #1

Debug.Print AddressArray(3, 1)
'should show you the third section of data on the first line of the file
Debug.Print AddressArray(1, 2)
'should show you the first section of data on the second line of the file
Debug.Print intContact - 1
' should show you the number of contacts you have in the file

Dim i As Integer
ComboBox1.Clear
With ComboBox1
.AddItem "Pick a Name..."
For i = 1 To UBound(AddressArray, 2) - 1
.AddItem AddressArray(1, i) & " " & AddressArray(2, i)
.ListIndex = "0"
Next
End With
End Sub

 

[VulcanJedi]

whoa, isn't it just simpler to do a MAIL MERGE w/ Office? It's a built in feature.

Tranpkp

 

[ssVBAdev]

Tranpkp:
Interesting, but:
1) could you use the Mail Merge to populate a combobox on a form?
2) would that not increase the file size of the .xls spreadsheet or can you filter the info during a Mail Merge?
(I'm not too familiar with the Mail Merge)

 

[Scotsman71]

Okay excellent I have it working...would it be easy to amend this to work in MS Word as well? And how do I do that?

 

[ssVBAdev]

Well, unfortunately my VBA strength is with Excel. But, as is, the code will work in Word. There are no references to any objects that are specific to Word. How to transfer the info from the pull down box to your document though, that's another story.
Our friend Tranpkp seemed to have something there but it seems he has abandoned us.
I'd like to learn more about using VBA with word. If you do find something out, please let me know )
With the last bit of code, you can add as many 'sections' or data to the lines in the Address file; just be sure to change the (1 to 5) in the AddressArray dimension to correspond to the number of 'sections' that you have.

 

[Scotsman71]

Thank you very much for all your help ssVBAdev. very much appreciated.

Could anyone else assist in amending this, or doing this for Word?