Need Help

[alexm999]

Im working on the following attached TXT file which has a customer
name, address and so on... I need to extract only the name and addres
from all the records and I will have another 5 or 6 files just lik
these next week.

The names and addresses will be used for a mailer...

Can this be done quickly using VB?

See attached TXT file info

Attachment filename: 101_inactives.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=45661

 

[Trevor Shuttleworth]

Alex

yes, you can do it with VBA. The following macro was simply recorded while
carrying out various actions manually.

First, copy the data in the text file. Then past it into Excel. Use Text
to Columns delimited by a ":" to separate the text. Then switch on
autofilter and set up a custom filter to select CUSTOMER or ADDRESS. Select
the visible rows, copy them and past them to a new sheet. Autofit the
columns and select cell A1. Done.

Sub Macro4()
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=** CUSTOMER #", Operator:= _
xlOr, Criteria2:="=ADDRESS"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Selection.Columns.AutoFit
Range("A1").Select
End Sub

It's not as hard as it might sound, honest. It does need some effort to
tidy it up but it demonstrates the principle

You'll get something like this (only lots more of it):

** CUSTOMER # A10 NAME KURTZHALS,KARI
ADDRESS 215 W HUMBIRD STREET, RICE LAKE WI 54868
** CUSTOMER # A100 NAME MING,NATASHA
ADDRESS 510 WAGNER, EAU CLAIRE WI 54701
** CUSTOMER # A102 NAME STABENOW,SHERYL
ADDRESS 4078 117TH STREET, CHIPPEWA FALLS WI 54729


Regards

Trevor

 

[alexm999]

Im getting errors in your code..

 

[alexm999]

This is where the error is:

Selection.TextToColumns Destination:=Range("B1")
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)

 

[Trevor Shuttleworth]

Alex

the code wrapped in the post. Try this:

Sub Macro4()
Columns("A:A").Select
Selection.TextToColumns _
Destination:=Range("B1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=":", _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, _
Criteria1:="=** CUSTOMER #", _
Operator:=xlOr, _
Criteria2:="=ADDRESS"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Selection.Columns.AutoFit
Range("A1").Select
End Sub

Regards

Trevor