Macro to convert ASCII File to Excell

I get files daily from differnet vendors in ASCII format.  The line breaks 
are noted by the ~ST and the columns are denoted by *.  Below is an example 
of a few lines.

ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
*100126*020
8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
WAY~N4
*ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
WAY B
LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
TDC~N3*
TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
WEN~P1**20090716*011****1~R1*D
HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
SHIPMENT~L5*1***
*PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
/H1***PP**I~L1*1*269.55*VS*26
955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
SURCHARGE~C3*USD*1.00
0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
**20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
ROCK*TX*786820001*US~LX*1~
N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
ROCK**786820001*US~PER
*SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
MUN*NT*99
999*HK~PER*DC*CHRISTINE 
LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
*003480062401*148.57~NTE*CUS*DUTIABLE 
SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1


I need to make these into readable lines an columns rather quickly each 
morning.  I would like to use a macro to do this incase I am out others could 
use this as well.

Thank you,
Holly 
0
Utf
1/26/2010 4:48:01 PM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
1429 Views

Similar Articles

[PageSpeed] 49

Try code like that shown below. You need to set a reference to the
scripting runtime library. In VBA, go to the Tools menu, choose
References, and scroll down to "Microsoft Scripting Runtime" and check
that entry.  The scripting runtime library contains the
FileSystemObject and TextStream objects that are used to read the
input file. Once you have set that reference, it will travel with the
workbook, so you don't need to set it every time you use the code, and
if you distribute the workbook to others, they will not need to go
through the referencing steps.

The code will, for example, transform the input text 

abc*def*ghi-ST123*456-STxyz*one*two*three

to an Excel range

abc	def	ghi
123	456
xyz	one	two	three

In the code, change StartRow and StartCol to the location where the
import process will start writing to the worksheet. If you set
StartRow and/or StartCol to less than or equal to zero, the starting
row and column will be the row and column of the currently active
cell.

Sub AAA()

Dim FName As Variant
Dim Lines() As String
Dim Cols() As String
Dim TS As TextStream
Dim FSO As Scripting.FileSystemObject
Dim S As String
Dim LNdx As Long
Dim CNdx As Long
Dim RowNdx As Long
Dim ColNdx As Long
Dim StartRow As Long
Dim StartCol As Long

StartRow = 3 '<<< CHANGE
StartCol = 4 '<<< CHANGE

If StartRow <= 0 Then
    StartRow = ActiveCell.Row
End If
If StartCol <= 0 Then
    StartCol = ActiveCell.Column
End If


Set FSO = New Scripting.FileSystemObject
FName = Application.GetOpenFilename("All files (*.*),*.*")
If FName = False Then
    Exit Sub
End If
Set TS = FSO.OpenTextFile(CStr(FName), ForReading, _
    False, TristateUseDefault)
S = TS.Read(FileLen(CStr(FName)))
Lines = Split(S, "-ST")
For LNdx = LBound(Lines) To UBound(Lines)
    Cols = Split(Lines(LNdx), "*")
    RowNdx = RowNdx + 1
    ColNdx = 0
    For CNdx = LBound(Cols) To UBound(Cols)
        ColNdx = ColNdx + 1
        Cells(StartRow + RowNdx - 1, _
            StartCol + ColNdx - 1).Value = Trim(Cols(CNdx))
    Next CNdx
Next LNdx
TS.Close
End Sub



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Tue, 26 Jan 2010 08:48:01 -0800, TXDalessandros
<TXDalessandros@discussions.microsoft.com> wrote:

>I get files daily from differnet vendors in ASCII format.  The line breaks 
>are noted by the ~ST and the columns are denoted by *.  Below is an example 
>of a few lines.
>
>ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
>*100126*020
>8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
>110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
>SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
>WAY~N4
>*ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
>WAY B
>LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
>TDC~N3*
>TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
>WEN~P1**20090716*011****1~R1*D
>HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
>SHIPMENT~L5*1***
>*PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
>/H1***PP**I~L1*1*269.55*VS*26
>955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
>SURCHARGE~C3*USD*1.00
>0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
>**20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
>1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
>ROCK*TX*786820001*US~LX*1~
>N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
>ROCK**786820001*US~PER
>*SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
>MUN*NT*99
>999*HK~PER*DC*CHRISTINE 
>LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
>*003480062401*148.57~NTE*CUS*DUTIABLE 
>SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1
>
>
>I need to make these into readable lines an columns rather quickly each 
>morning.  I would like to use a macro to do this incase I am out others could 
>use this as well.
>
>Thank you,
>Holly 
0
Chip
1/26/2010 5:40:18 PM
Holly,

I've listed some sample code below for your reference.  The code is NOT 
tested, but it should work and the comments should be enough for you to adapt 
the code accordingly.  At a minimum, you'll need to change the "strFileName" 
and "rngAnchor" variables within the code.  (There are ways to make 
strFileName and rngAnchor more dynamic (as opposed to being hard-coded 
values), but you should be able to do that if required).  Be sure to take a 
look at the .TextToColumns parameters and ensure the accuracy of those 
arguments (i.e. set the parameters to fit your situation and note that I 
haven't listed all possible parameters).  Lastly, are you sure there are no 
line feed characters in the file?

Best,

Matthew Herbert

Sub CustomParseText()
Dim intFile As Integer
Dim strFileName As String
Dim strText As String
Dim varArr As Variant
Dim strSepLine As String
Dim strSepCol As String
Dim strTemp As String
Dim rngAnchor As Range
Dim lngRow As Long
Dim intCol As Integer
Dim intCntCol As Integer

'CHANGE AS NEEDED
strFileName = "C:\test.txt"

'get a free file number for the Open statement
intFile = FreeFile()

'This works too: Open strFileName For Input As #intFile
Open strFileName For Binary Access Read As #intFile

'get the text in the file
strText = input(FileLen(strFileName), intFile)

'close the file b/c there is no more need for it
Close intFile

strSepLine = "~ST"
strSepCol = "*"

'test that strSepLine exists and handle accordingly
If InStr(1, strText, strSepLine) = 0 Then
    MsgBox "No line characters found.  Exiting the procedure."
    Exit Sub
End If

'split by line
varArr = Split(strText, "~ST")

'set the output location (CHANGE AS NEEDED)
Set rngAnchor = Worksheets(1).Range("A1")

With rngAnchor
    lngRow = .Row
    intCol = .Column
End With

With rngAnchor.Parent

    'you may want to test if the data will fit in the worksheet
    '   relative to the anchor location.  For example, you
    '   could do something like the following to test the rows:
    'If (lngRow + UBound(varSplit) - 1) > .Rows.Count Then Exit Sub
    
    'If you don't have uniform column counts on each line within
    '   the file, then the following will need to be adapted,
    '   otherwise, you could use the code to test the column fit
    '   relative to the anchor location:
    'strTemp = varArr(0)
    'intCntCol = Len(strTemp) - Len(Replace(strTemp, strSepCol, ""))
    'If (intCol + intCntCol) > .Columns.Count Then Exit Sub
    
    'output the array to the worksheet (Split creates a
    '   zero-based array)
    Range(.Cells(lngRow, intCol), _
          .Cells(lngRow + UBound(varArr) - 1, intCol)) = _
          Application.WorksheetFunction.Transpose(varArr)
    
    
'--------------------------------------------------------------------------------
    'May need to handle the "Do you want to replace the contents..."
    '   MsgBox that appears with the .TextToColumns if you are
    '   overwriting data.  (You could clear the worksheet and/or
    '   output area prior to output, you could use
    '   Application.DisplayAlerts, or use some other method to
    '   account for this potential scenario).
           
    'split the text into columns, set the parameters accordingly
    Range(.Cells(lngRow, intCol), _
          .Cells(lngRow + UBound(varArr) - 1, intCol)).TextToColumns 
DataType:=xlDelimited, _
          TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, 
Tab:=False, _
          Semicolon:=False, Comma:=False, Space:=False, Other:=True, 
OtherChar:=strSepCol
    
'--------------------------------------------------------------------------------
End With

End Sub

essandros" wrote:

> I get files daily from differnet vendors in ASCII format.  The line breaks 
> are noted by the ~ST and the columns are denoted by *.  Below is an example 
> of a few lines.
> 
> ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
> *100126*020
> 8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
> 110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
> SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
> WAY~N4
> *ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
> WAY B
> LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
> TDC~N3*
> TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
> WEN~P1**20090716*011****1~R1*D
> HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
> SHIPMENT~L5*1***
> *PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
> /H1***PP**I~L1*1*269.55*VS*26
> 955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
> SURCHARGE~C3*USD*1.00
> 0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
> **20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
> 1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
> ROCK*TX*786820001*US~LX*1~
> N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
> ROCK**786820001*US~PER
> *SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
> MUN*NT*99
> 999*HK~PER*DC*CHRISTINE 
> LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
> *003480062401*148.57~NTE*CUS*DUTIABLE 
> SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1
> 
> 
> I need to make these into readable lines an columns rather quickly each 
> morning.  I would like to use a macro to do this incase I am out others could 
> use this as well.
> 
> Thank you,
> Holly 
0
Utf
1/26/2010 6:09:02 PM
The line feed character is ~.  I am trying this now.

"Matthew Herbert" wrote:

> Holly,
> 
> I've listed some sample code below for your reference.  The code is NOT 
> tested, but it should work and the comments should be enough for you to adapt 
> the code accordingly.  At a minimum, you'll need to change the "strFileName" 
> and "rngAnchor" variables within the code.  (There are ways to make 
> strFileName and rngAnchor more dynamic (as opposed to being hard-coded 
> values), but you should be able to do that if required).  Be sure to take a 
> look at the .TextToColumns parameters and ensure the accuracy of those 
> arguments (i.e. set the parameters to fit your situation and note that I 
> haven't listed all possible parameters).  Lastly, are you sure there are no 
> line feed characters in the file?
> 
> Best,
> 
> Matthew Herbert
> 
> Sub CustomParseText()
> Dim intFile As Integer
> Dim strFileName As String
> Dim strText As String
> Dim varArr As Variant
> Dim strSepLine As String
> Dim strSepCol As String
> Dim strTemp As String
> Dim rngAnchor As Range
> Dim lngRow As Long
> Dim intCol As Integer
> Dim intCntCol As Integer
> 
> 'CHANGE AS NEEDED
> strFileName = "C:\test.txt"
> 
> 'get a free file number for the Open statement
> intFile = FreeFile()
> 
> 'This works too: Open strFileName For Input As #intFile
> Open strFileName For Binary Access Read As #intFile
> 
> 'get the text in the file
> strText = input(FileLen(strFileName), intFile)
> 
> 'close the file b/c there is no more need for it
> Close intFile
> 
> strSepLine = "~ST"
> strSepCol = "*"
> 
> 'test that strSepLine exists and handle accordingly
> If InStr(1, strText, strSepLine) = 0 Then
>     MsgBox "No line characters found.  Exiting the procedure."
>     Exit Sub
> End If
> 
> 'split by line
> varArr = Split(strText, "~ST")
> 
> 'set the output location (CHANGE AS NEEDED)
> Set rngAnchor = Worksheets(1).Range("A1")
> 
> With rngAnchor
>     lngRow = .Row
>     intCol = .Column
> End With
> 
> With rngAnchor.Parent
> 
>     'you may want to test if the data will fit in the worksheet
>     '   relative to the anchor location.  For example, you
>     '   could do something like the following to test the rows:
>     'If (lngRow + UBound(varSplit) - 1) > .Rows.Count Then Exit Sub
>     
>     'If you don't have uniform column counts on each line within
>     '   the file, then the following will need to be adapted,
>     '   otherwise, you could use the code to test the column fit
>     '   relative to the anchor location:
>     'strTemp = varArr(0)
>     'intCntCol = Len(strTemp) - Len(Replace(strTemp, strSepCol, ""))
>     'If (intCol + intCntCol) > .Columns.Count Then Exit Sub
>     
>     'output the array to the worksheet (Split creates a
>     '   zero-based array)
>     Range(.Cells(lngRow, intCol), _
>           .Cells(lngRow + UBound(varArr) - 1, intCol)) = _
>           Application.WorksheetFunction.Transpose(varArr)
>     
>     
> '--------------------------------------------------------------------------------
>     'May need to handle the "Do you want to replace the contents..."
>     '   MsgBox that appears with the .TextToColumns if you are
>     '   overwriting data.  (You could clear the worksheet and/or
>     '   output area prior to output, you could use
>     '   Application.DisplayAlerts, or use some other method to
>     '   account for this potential scenario).
>            
>     'split the text into columns, set the parameters accordingly
>     Range(.Cells(lngRow, intCol), _
>           .Cells(lngRow + UBound(varArr) - 1, intCol)).TextToColumns 
> DataType:=xlDelimited, _
>           TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, 
> Tab:=False, _
>           Semicolon:=False, Comma:=False, Space:=False, Other:=True, 
> OtherChar:=strSepCol
>     
> '--------------------------------------------------------------------------------
> End With
> 
> End Sub
> 
> essandros" wrote:
> 
> > I get files daily from differnet vendors in ASCII format.  The line breaks 
> > are noted by the ~ST and the columns are denoted by *.  Below is an example 
> > of a few lines.
> > 
> > ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
> > *100126*020
> > 8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
> > 110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
> > SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
> > WAY~N4
> > *ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
> > WAY B
> > LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
> > TDC~N3*
> > TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
> > WEN~P1**20090716*011****1~R1*D
> > HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
> > SHIPMENT~L5*1***
> > *PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
> > /H1***PP**I~L1*1*269.55*VS*26
> > 955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
> > SURCHARGE~C3*USD*1.00
> > 0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
> > **20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
> > 1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
> > ROCK*TX*786820001*US~LX*1~
> > N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
> > ROCK**786820001*US~PER
> > *SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
> > MUN*NT*99
> > 999*HK~PER*DC*CHRISTINE 
> > LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
> > *003480062401*148.57~NTE*CUS*DUTIABLE 
> > SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1
> > 
> > 
> > I need to make these into readable lines an columns rather quickly each 
> > morning.  I would like to use a macro to do this incase I am out others could 
> > use this as well.
> > 
> > Thank you,
> > Holly 
0
Utf
1/26/2010 7:39:03 PM
Holly,

Sorry for the vagaries, but what I mean by "line feed" is this:  Is there a 
vbLf (Chr(10) or CHAR(10)), vbCr (Chr(13) or CHAR(13)), or vbCrLf (Chr(13) + 
Chr(10)) character in the text file?  (You can do a search within VBE help 
for "Miscellaneous Constants" to reference what I'm talking about).  If so, 
then you will likely get some unexpected results and thus need to adjust for 
these special characters.

Best,

Matt


"TXDalessandros" wrote:

> The line feed character is ~.  I am trying this now.
> 
> "Matthew Herbert" wrote:
> 
> > Holly,
> > 
> > I've listed some sample code below for your reference.  The code is NOT 
> > tested, but it should work and the comments should be enough for you to adapt 
> > the code accordingly.  At a minimum, you'll need to change the "strFileName" 
> > and "rngAnchor" variables within the code.  (There are ways to make 
> > strFileName and rngAnchor more dynamic (as opposed to being hard-coded 
> > values), but you should be able to do that if required).  Be sure to take a 
> > look at the .TextToColumns parameters and ensure the accuracy of those 
> > arguments (i.e. set the parameters to fit your situation and note that I 
> > haven't listed all possible parameters).  Lastly, are you sure there are no 
> > line feed characters in the file?
> > 
> > Best,
> > 
> > Matthew Herbert
> > 
> > Sub CustomParseText()
> > Dim intFile As Integer
> > Dim strFileName As String
> > Dim strText As String
> > Dim varArr As Variant
> > Dim strSepLine As String
> > Dim strSepCol As String
> > Dim strTemp As String
> > Dim rngAnchor As Range
> > Dim lngRow As Long
> > Dim intCol As Integer
> > Dim intCntCol As Integer
> > 
> > 'CHANGE AS NEEDED
> > strFileName = "C:\test.txt"
> > 
> > 'get a free file number for the Open statement
> > intFile = FreeFile()
> > 
> > 'This works too: Open strFileName For Input As #intFile
> > Open strFileName For Binary Access Read As #intFile
> > 
> > 'get the text in the file
> > strText = input(FileLen(strFileName), intFile)
> > 
> > 'close the file b/c there is no more need for it
> > Close intFile
> > 
> > strSepLine = "~ST"
> > strSepCol = "*"
> > 
> > 'test that strSepLine exists and handle accordingly
> > If InStr(1, strText, strSepLine) = 0 Then
> >     MsgBox "No line characters found.  Exiting the procedure."
> >     Exit Sub
> > End If
> > 
> > 'split by line
> > varArr = Split(strText, "~ST")
> > 
> > 'set the output location (CHANGE AS NEEDED)
> > Set rngAnchor = Worksheets(1).Range("A1")
> > 
> > With rngAnchor
> >     lngRow = .Row
> >     intCol = .Column
> > End With
> > 
> > With rngAnchor.Parent
> > 
> >     'you may want to test if the data will fit in the worksheet
> >     '   relative to the anchor location.  For example, you
> >     '   could do something like the following to test the rows:
> >     'If (lngRow + UBound(varSplit) - 1) > .Rows.Count Then Exit Sub
> >     
> >     'If you don't have uniform column counts on each line within
> >     '   the file, then the following will need to be adapted,
> >     '   otherwise, you could use the code to test the column fit
> >     '   relative to the anchor location:
> >     'strTemp = varArr(0)
> >     'intCntCol = Len(strTemp) - Len(Replace(strTemp, strSepCol, ""))
> >     'If (intCol + intCntCol) > .Columns.Count Then Exit Sub
> >     
> >     'output the array to the worksheet (Split creates a
> >     '   zero-based array)
> >     Range(.Cells(lngRow, intCol), _
> >           .Cells(lngRow + UBound(varArr) - 1, intCol)) = _
> >           Application.WorksheetFunction.Transpose(varArr)
> >     
> >     
> > '--------------------------------------------------------------------------------
> >     'May need to handle the "Do you want to replace the contents..."
> >     '   MsgBox that appears with the .TextToColumns if you are
> >     '   overwriting data.  (You could clear the worksheet and/or
> >     '   output area prior to output, you could use
> >     '   Application.DisplayAlerts, or use some other method to
> >     '   account for this potential scenario).
> >            
> >     'split the text into columns, set the parameters accordingly
> >     Range(.Cells(lngRow, intCol), _
> >           .Cells(lngRow + UBound(varArr) - 1, intCol)).TextToColumns 
> > DataType:=xlDelimited, _
> >           TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, 
> > Tab:=False, _
> >           Semicolon:=False, Comma:=False, Space:=False, Other:=True, 
> > OtherChar:=strSepCol
> >     
> > '--------------------------------------------------------------------------------
> > End With
> > 
> > End Sub
> > 
> > essandros" wrote:
> > 
> > > I get files daily from differnet vendors in ASCII format.  The line breaks 
> > > are noted by the ~ST and the columns are denoted by *.  Below is an example 
> > > of a few lines.
> > > 
> > > ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
> > > *100126*020
> > > 8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
> > > 110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
> > > SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
> > > WAY~N4
> > > *ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
> > > WAY B
> > > LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
> > > TDC~N3*
> > > TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
> > > WEN~P1**20090716*011****1~R1*D
> > > HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
> > > SHIPMENT~L5*1***
> > > *PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
> > > /H1***PP**I~L1*1*269.55*VS*26
> > > 955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
> > > SURCHARGE~C3*USD*1.00
> > > 0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
> > > **20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
> > > 1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
> > > ROCK*TX*786820001*US~LX*1~
> > > N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
> > > ROCK**786820001*US~PER
> > > *SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
> > > MUN*NT*99
> > > 999*HK~PER*DC*CHRISTINE 
> > > LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
> > > *003480062401*148.57~NTE*CUS*DUTIABLE 
> > > SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1
> > > 
> > > 
> > > I need to make these into readable lines an columns rather quickly each 
> > > morning.  I would like to use a macro to do this incase I am out others could 
> > > use this as well.
> > > 
> > > Thank you,
> > > Holly 
0
Utf
1/26/2010 8:31:02 PM
I was able to remove the hard returns in the txt file with a find and replace 
prior to using this so that helped.  

Is there anyway now since the columns are not uniform to find say a cell 
value of AW and then copy the cell adjancent to it to another worksheet?

"Matthew Herbert" wrote:

> Holly,
> 
> Sorry for the vagaries, but what I mean by "line feed" is this:  Is there a 
> vbLf (Chr(10) or CHAR(10)), vbCr (Chr(13) or CHAR(13)), or vbCrLf (Chr(13) + 
> Chr(10)) character in the text file?  (You can do a search within VBE help 
> for "Miscellaneous Constants" to reference what I'm talking about).  If so, 
> then you will likely get some unexpected results and thus need to adjust for 
> these special characters.
> 
> Best,
> 
> Matt
> 
> 
> "TXDalessandros" wrote:
> 
> > The line feed character is ~.  I am trying this now.
> > 
> > "Matthew Herbert" wrote:
> > 
> > > Holly,
> > > 
> > > I've listed some sample code below for your reference.  The code is NOT 
> > > tested, but it should work and the comments should be enough for you to adapt 
> > > the code accordingly.  At a minimum, you'll need to change the "strFileName" 
> > > and "rngAnchor" variables within the code.  (There are ways to make 
> > > strFileName and rngAnchor more dynamic (as opposed to being hard-coded 
> > > values), but you should be able to do that if required).  Be sure to take a 
> > > look at the .TextToColumns parameters and ensure the accuracy of those 
> > > arguments (i.e. set the parameters to fit your situation and note that I 
> > > haven't listed all possible parameters).  Lastly, are you sure there are no 
> > > line feed characters in the file?
> > > 
> > > Best,
> > > 
> > > Matthew Herbert
> > > 
> > > Sub CustomParseText()
> > > Dim intFile As Integer
> > > Dim strFileName As String
> > > Dim strText As String
> > > Dim varArr As Variant
> > > Dim strSepLine As String
> > > Dim strSepCol As String
> > > Dim strTemp As String
> > > Dim rngAnchor As Range
> > > Dim lngRow As Long
> > > Dim intCol As Integer
> > > Dim intCntCol As Integer
> > > 
> > > 'CHANGE AS NEEDED
> > > strFileName = "C:\test.txt"
> > > 
> > > 'get a free file number for the Open statement
> > > intFile = FreeFile()
> > > 
> > > 'This works too: Open strFileName For Input As #intFile
> > > Open strFileName For Binary Access Read As #intFile
> > > 
> > > 'get the text in the file
> > > strText = input(FileLen(strFileName), intFile)
> > > 
> > > 'close the file b/c there is no more need for it
> > > Close intFile
> > > 
> > > strSepLine = "~ST"
> > > strSepCol = "*"
> > > 
> > > 'test that strSepLine exists and handle accordingly
> > > If InStr(1, strText, strSepLine) = 0 Then
> > >     MsgBox "No line characters found.  Exiting the procedure."
> > >     Exit Sub
> > > End If
> > > 
> > > 'split by line
> > > varArr = Split(strText, "~ST")
> > > 
> > > 'set the output location (CHANGE AS NEEDED)
> > > Set rngAnchor = Worksheets(1).Range("A1")
> > > 
> > > With rngAnchor
> > >     lngRow = .Row
> > >     intCol = .Column
> > > End With
> > > 
> > > With rngAnchor.Parent
> > > 
> > >     'you may want to test if the data will fit in the worksheet
> > >     '   relative to the anchor location.  For example, you
> > >     '   could do something like the following to test the rows:
> > >     'If (lngRow + UBound(varSplit) - 1) > .Rows.Count Then Exit Sub
> > >     
> > >     'If you don't have uniform column counts on each line within
> > >     '   the file, then the following will need to be adapted,
> > >     '   otherwise, you could use the code to test the column fit
> > >     '   relative to the anchor location:
> > >     'strTemp = varArr(0)
> > >     'intCntCol = Len(strTemp) - Len(Replace(strTemp, strSepCol, ""))
> > >     'If (intCol + intCntCol) > .Columns.Count Then Exit Sub
> > >     
> > >     'output the array to the worksheet (Split creates a
> > >     '   zero-based array)
> > >     Range(.Cells(lngRow, intCol), _
> > >           .Cells(lngRow + UBound(varArr) - 1, intCol)) = _
> > >           Application.WorksheetFunction.Transpose(varArr)
> > >     
> > >     
> > > '--------------------------------------------------------------------------------
> > >     'May need to handle the "Do you want to replace the contents..."
> > >     '   MsgBox that appears with the .TextToColumns if you are
> > >     '   overwriting data.  (You could clear the worksheet and/or
> > >     '   output area prior to output, you could use
> > >     '   Application.DisplayAlerts, or use some other method to
> > >     '   account for this potential scenario).
> > >            
> > >     'split the text into columns, set the parameters accordingly
> > >     Range(.Cells(lngRow, intCol), _
> > >           .Cells(lngRow + UBound(varArr) - 1, intCol)).TextToColumns 
> > > DataType:=xlDelimited, _
> > >           TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, 
> > > Tab:=False, _
> > >           Semicolon:=False, Comma:=False, Space:=False, Other:=True, 
> > > OtherChar:=strSepCol
> > >     
> > > '--------------------------------------------------------------------------------
> > > End With
> > > 
> > > End Sub
> > > 
> > > essandros" wrote:
> > > 
> > > > I get files daily from differnet vendors in ASCII format.  The line breaks 
> > > > are noted by the ~ST and the columns are denoted by *.  Below is an example 
> > > > of a few lines.
> > > > 
> > > > ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
> > > > *100126*020
> > > > 8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
> > > > 110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
> > > > SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
> > > > WAY~N4
> > > > *ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
> > > > WAY B
> > > > LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
> > > > TDC~N3*
> > > > TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
> > > > WEN~P1**20090716*011****1~R1*D
> > > > HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
> > > > SHIPMENT~L5*1***
> > > > *PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
> > > > /H1***PP**I~L1*1*269.55*VS*26
> > > > 955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
> > > > SURCHARGE~C3*USD*1.00
> > > > 0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
> > > > **20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
> > > > 1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
> > > > ROCK*TX*786820001*US~LX*1~
> > > > N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
> > > > ROCK**786820001*US~PER
> > > > *SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
> > > > MUN*NT*99
> > > > 999*HK~PER*DC*CHRISTINE 
> > > > LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
> > > > *003480062401*148.57~NTE*CUS*DUTIABLE 
> > > > SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1
> > > > 
> > > > 
> > > > I need to make these into readable lines an columns rather quickly each 
> > > > morning.  I would like to use a macro to do this incase I am out others could 
> > > > use this as well.
> > > > 
> > > > Thank you,
> > > > Holly 
0
Utf
1/26/2010 8:54:01 PM
Holly,

You shouldn't need to do a find and replace on the raw data; simply adjust 
the macro to handle the raw data.  (As you said, if you're not around and 
someone else has to run this process and the someone else doesn't do the find 
and replace, then it won't work anyway).  As far as finding the "AW," are you 
looking to obtain every entry after "AW," or do you have something else in 
mind?  If you want every value after the "AW," and between the "*"s, then see 
the code below.  For example, if your data were like 
"...*AW*003479667520*276...", the macro would return "003479667520".  Note 
that you'll have to do something with the result, which is being printed to 
the Immediate Window (VBE: View|Immediate Window) for now.

Best,

Matt

Sub FindTextAfterAW()
Dim intFile As Integer
Dim strFileName As String
Dim strText As String
Dim lngPosTxt As Long
Dim lngPosStar1 As Long
Dim lngPosStar2 As Long

'CHANGE AS NEEDED
strFileName = "C:\test.txt"

'get a free file number for the Open statement
intFile = FreeFile()

'This works too: Open strFileName For Input As #intFile
Open strFileName For Binary Access Read As #intFile

'get the text in the file
strText = Input(FileLen(strFileName), intFile)

'close the file b/c there is no more need for it
Close intFile

lngPosTxt = 1
lngPosStar1 = 0
lngPosStar2 = 0
Do
    lngPosTxt = InStr(lngPosStar2 + 1, strText, "AW")
    If lngPosTxt = 0 Then Exit Do
    
    'locate the text after the "AW" and in between the two "*"s
    lngPosStar1 = InStr(lngPosTxt + 1, strText, "*")
    If lngPosStar1 = 0 Then Exit Do
    
    lngPosStar2 = InStr(lngPosStar1 + 1, strText, "*")
    If lngPosStar2 = 0 Then Exit Do
    
    'do something with the result
    If lngPosStar1 + 1 = lngPosStar2 Then
        Debug.Print "No text between the stars"
    Else
        Debug.Print Mid(strText, lngPosStar1 + 1, _
                        lngPosStar2 - lngPosStar1 - 1)
    End If
Loop

End Sub

"TXDalessandros" wrote:

> I was able to remove the hard returns in the txt file with a find and replace 
> prior to using this so that helped.  
> 
> Is there anyway now since the columns are not uniform to find say a cell 
> value of AW and then copy the cell adjancent to it to another worksheet?
> 
> "Matthew Herbert" wrote:
> 
> > Holly,
> > 
> > Sorry for the vagaries, but what I mean by "line feed" is this:  Is there a 
> > vbLf (Chr(10) or CHAR(10)), vbCr (Chr(13) or CHAR(13)), or vbCrLf (Chr(13) + 
> > Chr(10)) character in the text file?  (You can do a search within VBE help 
> > for "Miscellaneous Constants" to reference what I'm talking about).  If so, 
> > then you will likely get some unexpected results and thus need to adjust for 
> > these special characters.
> > 
> > Best,
> > 
> > Matt
> > 
> > 
> > "TXDalessandros" wrote:
> > 
> > > The line feed character is ~.  I am trying this now.
> > > 
> > > "Matthew Herbert" wrote:
> > > 
> > > > Holly,
> > > > 
> > > > I've listed some sample code below for your reference.  The code is NOT 
> > > > tested, but it should work and the comments should be enough for you to adapt 
> > > > the code accordingly.  At a minimum, you'll need to change the "strFileName" 
> > > > and "rngAnchor" variables within the code.  (There are ways to make 
> > > > strFileName and rngAnchor more dynamic (as opposed to being hard-coded 
> > > > values), but you should be able to do that if required).  Be sure to take a 
> > > > look at the .TextToColumns parameters and ensure the accuracy of those 
> > > > arguments (i.e. set the parameters to fit your situation and note that I 
> > > > haven't listed all possible parameters).  Lastly, are you sure there are no 
> > > > line feed characters in the file?
> > > > 
> > > > Best,
> > > > 
> > > > Matthew Herbert
> > > > 
> > > > Sub CustomParseText()
> > > > Dim intFile As Integer
> > > > Dim strFileName As String
> > > > Dim strText As String
> > > > Dim varArr As Variant
> > > > Dim strSepLine As String
> > > > Dim strSepCol As String
> > > > Dim strTemp As String
> > > > Dim rngAnchor As Range
> > > > Dim lngRow As Long
> > > > Dim intCol As Integer
> > > > Dim intCntCol As Integer
> > > > 
> > > > 'CHANGE AS NEEDED
> > > > strFileName = "C:\test.txt"
> > > > 
> > > > 'get a free file number for the Open statement
> > > > intFile = FreeFile()
> > > > 
> > > > 'This works too: Open strFileName For Input As #intFile
> > > > Open strFileName For Binary Access Read As #intFile
> > > > 
> > > > 'get the text in the file
> > > > strText = input(FileLen(strFileName), intFile)
> > > > 
> > > > 'close the file b/c there is no more need for it
> > > > Close intFile
> > > > 
> > > > strSepLine = "~ST"
> > > > strSepCol = "*"
> > > > 
> > > > 'test that strSepLine exists and handle accordingly
> > > > If InStr(1, strText, strSepLine) = 0 Then
> > > >     MsgBox "No line characters found.  Exiting the procedure."
> > > >     Exit Sub
> > > > End If
> > > > 
> > > > 'split by line
> > > > varArr = Split(strText, "~ST")
> > > > 
> > > > 'set the output location (CHANGE AS NEEDED)
> > > > Set rngAnchor = Worksheets(1).Range("A1")
> > > > 
> > > > With rngAnchor
> > > >     lngRow = .Row
> > > >     intCol = .Column
> > > > End With
> > > > 
> > > > With rngAnchor.Parent
> > > > 
> > > >     'you may want to test if the data will fit in the worksheet
> > > >     '   relative to the anchor location.  For example, you
> > > >     '   could do something like the following to test the rows:
> > > >     'If (lngRow + UBound(varSplit) - 1) > .Rows.Count Then Exit Sub
> > > >     
> > > >     'If you don't have uniform column counts on each line within
> > > >     '   the file, then the following will need to be adapted,
> > > >     '   otherwise, you could use the code to test the column fit
> > > >     '   relative to the anchor location:
> > > >     'strTemp = varArr(0)
> > > >     'intCntCol = Len(strTemp) - Len(Replace(strTemp, strSepCol, ""))
> > > >     'If (intCol + intCntCol) > .Columns.Count Then Exit Sub
> > > >     
> > > >     'output the array to the worksheet (Split creates a
> > > >     '   zero-based array)
> > > >     Range(.Cells(lngRow, intCol), _
> > > >           .Cells(lngRow + UBound(varArr) - 1, intCol)) = _
> > > >           Application.WorksheetFunction.Transpose(varArr)
> > > >     
> > > >     
> > > > '--------------------------------------------------------------------------------
> > > >     'May need to handle the "Do you want to replace the contents..."
> > > >     '   MsgBox that appears with the .TextToColumns if you are
> > > >     '   overwriting data.  (You could clear the worksheet and/or
> > > >     '   output area prior to output, you could use
> > > >     '   Application.DisplayAlerts, or use some other method to
> > > >     '   account for this potential scenario).
> > > >            
> > > >     'split the text into columns, set the parameters accordingly
> > > >     Range(.Cells(lngRow, intCol), _
> > > >           .Cells(lngRow + UBound(varArr) - 1, intCol)).TextToColumns 
> > > > DataType:=xlDelimited, _
> > > >           TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, 
> > > > Tab:=False, _
> > > >           Semicolon:=False, Comma:=False, Space:=False, Other:=True, 
> > > > OtherChar:=strSepCol
> > > >     
> > > > '--------------------------------------------------------------------------------
> > > > End With
> > > > 
> > > > End Sub
> > > > 
> > > > essandros" wrote:
> > > > 
> > > > > I get files daily from differnet vendors in ASCII format.  The line breaks 
> > > > > are noted by the ~ST and the columns are denoted by *.  Below is an example 
> > > > > of a few lines.
> > > > > 
> > > > > ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
> > > > > *100126*020
> > > > > 8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
> > > > > 110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
> > > > > SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
> > > > > WAY~N4
> > > > > *ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
> > > > > WAY B
> > > > > LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
> > > > > TDC~N3*
> > > > > TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
> > > > > WEN~P1**20090716*011****1~R1*D
> > > > > HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
> > > > > SHIPMENT~L5*1***
> > > > > *PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
> > > > > /H1***PP**I~L1*1*269.55*VS*26
> > > > > 955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
> > > > > SURCHARGE~C3*USD*1.00
> > > > > 0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
> > > > > **20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
> > > > > 1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
> > > > > ROCK*TX*786820001*US~LX*1~
> > > > > N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
> > > > > ROCK**786820001*US~PER
> > > > > *SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
> > > > > MUN*NT*99
> > > > > 999*HK~PER*DC*CHRISTINE 
> > > > > LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
> > > > > *003480062401*148.57~NTE*CUS*DUTIABLE 
> > > > > SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1
> > > > > 
> > > > > 
> > > > > I need to make these into readable lines an columns rather quickly each 
> > > > > morning.  I would like to use a macro to do this incase I am out others could 
> > > > > use this as well.
> > > > > 
> > > > > Thank you,
> > > > > Holly 
0
Utf
1/27/2010 2:49:01 AM
I posted this yesterday but it doesn't seem to have made it to the
server.


Try code like that shown below. You need to set a reference to the
scripting runtime library. In VBA, go to the Tools menu, choose
References, and scroll down to "Microsoft Scripting Runtime" and check
that entry.  The scripting runtime library contains the
FileSystemObject and TextStream objects that are used to read the
input file. Once you have set that reference, it will travel with the
workbook, so you don't need to set it every time you use the code, and
if you distribute the workbook to others, they will not need to go
through the referencing steps.

The code will, for example, transform the input text 

abc*def*ghi-ST123*456-STxyz*one*two*three

to an Excel range

abc	def	ghi
123	456
xyz	one	two	three

In the code, change StartRow and StartCol to the location where the
import process will start writing to the worksheet. If you set
StartRow and/or StartCol to less than or equal to zero, the starting
row and column will be the row and column of the currently active
cell.

Sub AAA()

Dim FName As Variant
Dim Lines() As String
Dim Cols() As String
Dim TS As TextStream
Dim FSO As Scripting.FileSystemObject
Dim S As String
Dim LNdx As Long
Dim CNdx As Long
Dim RowNdx As Long
Dim ColNdx As Long
Dim StartRow As Long
Dim StartCol As Long

StartRow = 3 '<<< CHANGE
StartCol = 4 '<<< CHANGE

If StartRow <= 0 Then
    StartRow = ActiveCell.Row
End If
If StartCol <= 0 Then
    StartCol = ActiveCell.Column
End If


Set FSO = New Scripting.FileSystemObject
FName = Application.GetOpenFilename("All files (*.*),*.*")
If FName = False Then
    Exit Sub
End If
Set TS = FSO.OpenTextFile(CStr(FName), ForReading, _
    False, TristateUseDefault)
S = TS.Read(FileLen(CStr(FName)))
Lines = Split(S, "-ST")
For LNdx = LBound(Lines) To UBound(Lines)
    Cols = Split(Lines(LNdx), "*")
    RowNdx = RowNdx + 1
    ColNdx = 0
    For CNdx = LBound(Cols) To UBound(Cols)
        ColNdx = ColNdx + 1
        Cells(StartRow + RowNdx - 1, _
            StartCol + ColNdx - 1).Value = Trim(Cols(CNdx))
    Next CNdx
Next LNdx
TS.Close
End Sub



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Tue, 26 Jan 2010 08:48:01 -0800, TXDalessandros
<TXDalessandros@discussions.microsoft.com> wrote:

>I get files daily from differnet vendors in ASCII format.  The line breaks 
>are noted by the ~ST and the columns are denoted by *.  Below is an example 
>of a few lines.
>
>ISA*00*          *00*          *ZZ*DHLCIDI        *ZZ*TRN200175      
>*100126*020
>8*U*00400*000000142*1*P*>~GS*IA*DHLCIDI*TRN200175*20100126*0208*142*X*004010~ST*
>110*1420001~B3**AUSD000000017**MX**20100125*27629****DHLC*20100125~B3A*DR*1~C3*U
>SD**USD~ITD*05*3****20100209*15~N1*BT*DELL DMS*25*00797056329~N3*501 DELL 
>WAY~N4
>*ROUND ROCK*TX*786820001*US~LX*1~N1*SH*DELL DMS*25*00797056329~N3*200 DELL 
>WAY B
>LDG RR 5 RO~N4*ROUND ROCK*TX*786820001*US~PER*SH*DELL COMPUTER~N1*CN*DEL 
>TDC~N3*
>TUN HUA S RD~N4*TAIPEI**99999*TW~PER*DC*S FANGI J 
>WEN~P1**20090716*011****1~R1*D
>HLC*AIR*AUS*DHL*TPE~RMT*AW*003479667520*276.29~NTE*CUS*DUTIABLE 
>SHIPMENT~L5*1***
>*PKG90~L0*1*39*LB*20*A1***1*PCS**L~SL1*CX***6329   
>/H1***PP**I~L1*1*269.55*VS*26
>955****400****FRT CHRG AMT~L1*2*6.74*VS*674****405****FUEL 
>SURCHARGE~C3*USD*1.00
>0*USD~L3*39*B***27629*******L~SE*29*1420001~ST*110*1420002~B3**AUSD000000018**MX
>**20100125*14857****DHLC*20100125~B3A*DR*1~C3*USD**USD~ITD*05*3****20100209*15~N
>1*BT*DELL DMS*25*00797056329~N3*501 DELL WAY~N4*ROUND 
>ROCK*TX*786820001*US~LX*1~
>N1*SH*DELL DMS*25*00797056329~N3*501 DELL WAY*TX~N4*ROUND 
>ROCK**786820001*US~PER
>*SH*HOLLY ARNOLD~N1*CN*MITAC COMPUTER~N3*1/F 97 HO YEUNG ST*NT~N4*TUEN 
>MUN*NT*99
>999*HK~PER*DC*CHRISTINE 
>LEE~P1**20090720*011****1~R1*DHLC*AIR*AUS*DHL*HKG~RMT*AW
>*003480062401*148.57~NTE*CUS*DUTIABLE 
>SHIPMENT~L5*1****PKG90~L0*1*14*LB*3*A1***1
>
>
>I need to make these into readable lines an columns rather quickly each 
>morning.  I would like to use a macro to do this incase I am out others could 
>use this as well.
>
>Thank you,
>Holly 
0
Chip
1/27/2010 9:18:29 PM
Reply:

Similar Artilces:

Converting QuatroPro to Excel
How can convert a quatropro spreadsheet to an excel spreadsheet. Any help? Thanks, Bob Open the file in Quattro Pro and save it in one of the supplied Excel formats. -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Bob" <anonymous@discussions.microsoft.com> wrote in message news:36c601c40128$9e26a970$a601280a@phx.gbl... > How can convert a quatropro spreadsheet to an excel > spreadsheet. Any help? > Thanks, > Bob ...

How do I use a "subtract" function in Excel?
Hi Kelly if you want to subtract the value in B1 from the value in A1 and have the answer display in C1 then in C1 type =A1-B1 Hope this helps Cheers JulieD "Kelly" <Kelly@discussions.microsoft.com> wrote in message news:5DF4C83F-6BF1-40A8-BC8A-692058A5C70B@microsoft.com... > ...

Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...

Excel 2007
When I select cells to copy as a picture in Excel 2007, the resolution is terrible. Text and objects with shadow's are very blotchy when pasting the picture. How do you change the resolution of a 'Copy Picture'? ...

Excel not Access
I have designed an Access database that holds records relating to my stores audit results going back for about 5 years plus a load more information relating to these stores. This was used to produe a pack once a month, however a change in senior management means that I have got to shelve this and prodce a similar pack in Excel. The idea would be that the user could select a month or a 12 mnth date range that would produce data that could then be used to populate a number of excel templates that have been designed. Having not used excel for years I would be grateful for any suggestion...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

Opening Excel Workbooks
I'm running into an issue where if I click on an Excel file through My Documents, it doesn't automatically bring it up. I get the toolbar but the actual spreadsheet doesn't appear on the screen. I have to click on the taskbar to get it to pop up. If I already have Excel active and I open a file through Excel, this doesn't happen. Any ideas? Here is a similar thread: http://www.excelforum.com/showthread.php?s=&threadid=237195 Rolli -- Message posted from http://www.ExcelForum.com Hi, Take a look at Tools-Options-General tab- uncheck ignore other application...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

Where is the workspace file stored?
I was given a project folder containing several dozen files. So where is the workspace file normally located? If I don't have a workspace file how can I cerate one? Using VC version 6. TIA I haven't used VC6 for some time, but if I remember right you can just open the .dsp file and it will create a .dsw file for you. It typically goes in the same folder with the code. Tom "Phisherman" <noone@nobody.com> wrote in message news:be93539ccf8a6f25ibh4vt6ggmtbi024mq@4ax.com... >I was given a project folder containing several dozen files. So where > is the wor...

help with simple maths in excel
I want to do a simple arithmatic excercise in excel for my grand daughter. It is stuff like 2+2 =4 Smart me has hit a problem at the first hurdle... I need to put 2 in one cell the + in another cell and then 2 in another = in another and then she puts the answer in the next one. So the above would have 4 cells completed and she would put the answer in the 5th one. When I use the + or = sign in a cell of its own it (excel) thinks I am doing an equation, is there a way around this? I will work on the answer like if she gets it correct or wrong how I will do that...a sound or som...

Change File Locations to Private Drive (not Folder)
Hi, I know that I can use "File Locations" in "Options" so that whenever I use the "Open..." command in Word, it will open to a specific folder. I'm wondering if there is a way that this can be applied not to a folder but to a specific drive on a network. Our company has a main public drive and has also assigned each of the Staff their own private drive. Is there a way to access the "main page" (for lack of a better term) of my private drive each time I use "Open..." in Word? Right now it goes to "My Computer" or ...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

cant start outlook pst file to large
Hi I hope somebody may be able to help im new to outlook and now I cant start outlook message says cannot open your default email folders pst file its too large as I cannot open outlook I cannot delete old folders See if this helps: http://support.microsoft.com/kb/296088 -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Ray" <Ray@discussions.microsoft.com> wrote in message news:B3B824DC-3C9B-4B1B-8EF3-61...

Personal Macro Workbook
I had some macro stored in the personal macro workbook but now I can not find the workbook. When I try to do a new macro and store it in the "Personal Macro Workbook" I get the error message "Personal Macro Workbook in the startup folder must stay open for recording". I click ok and get a message that I'm unable to record. I can record macro to other workbooks. What happened to the personal macro workbook ? Thanks ...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

Drag and Drop file/directory names on Tree Control.
hi, I am writing an application in which i have one Tree control. Now i want that , when user drag & drop any file/folder on tree control the file name/folder name should be add in the tree control item. User may drag and drop file from desktop or may be from directory. I just want the path of that file/foder name nothing else. so plz tell me will it be possible ... Plz Help me............. Klic..... > I am writing an application in which i have one Tree control. >Now i want that , when user drag & drop any file/folder on tree >control the file nam...

How To Copy MS Word mailing labels into Excel
I have a word doc that I want to put into Excel. I want to add some more fields to the names and addresses. Is this simple or do I have to learn how to program? Michael Rodriguez City of Grand Prairie Michael, have you tried to copy and paste the data into excel? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Michael Rodriguez" <mrodrigu@gptx.org> wrote in messa...

Trying to read XML file into dataset
Hello. Developing a web site in VS2005, SP1, VB, .NET 2.0, ASP.NET 2.0 on XP Pro, SP2. I eventually want to use a gridview table to view xml data, but right now i can't seem to get my xml data into a dataset so that i can use it. I have an XML file and can open it in IE. There is no XSD or XSL file to go with it. I can't seem to get the XML data into a dataset. I tried using the following code, but it just does nothing. It doesn't give an error. It just executes the code and does nothing. Dim dataSet As DataSet = New DataSet dataSet.ReadXml("...

keyboard shortcut in addin macro
How do you assign a keyboard shortcut or a custom button to run a macro which is in an addin. These macros dont appear on the macro list Thanks -- Patrick Hi have a look at the OnKey method in the VBA help -- Regards Frank Kabel Frankfurt, Germany "Patrick" <Patrick@discussions.microsoft.com> schrieb im Newsbeitrag news:FBEB937C-C3FC-47BB-8E8A-EE0BBCB14DFB@microsoft.com... > How do you assign a keyboard shortcut or a custom button to run a macro which > is in an addin. > > These macros dont appear on the macro list > > Thanks > -- > Patrick Patr...

Convert date
How can I convert a date in this format (mm-dd-yyyy) to a Julian-date (i.e., YYYYDDD)? For example: 09-13-2003 to 2003256 Hi see: http://www.cpearson.com/excel/jdates.htm -- Regards Frank Kabel Frankfurt, Germany gary wrote: > How can I convert a date in this format (mm-dd-yyyy) to a > Julian-date (i.e., YYYYDDD)? > > For example: 09-13-2003 to 2003256 On Wed, 8 Sep 2004 10:24:57 -0700, "gary" <anonymous@discussions.microsoft.com> wrote: >How can I convert a date in this format (mm-dd-yyyy) to a >Julian-date (i.e., YYYYDDD)? > >For exa...

opening .bak file
Hi, I hope someone can help! I recently was performing a compacting operation on my outlook express folders. My computer shutdown due to a power failure and I lost some emails from a few of my folders. I was however able to get the .bak files (of the .dbx files that were lost) out of the recycle bin. If I could open one of these .bak files or convert it back to the .dbx file from which it was created I would retrieve these emails. Can anyone instruct me as to open this .bak file? Thanks for any help! Arnie K. How did you get the bak files out of the Recycle Bin? All e...

Excel Problem
I have a 23.8 meg excel 2000 spreadsheet set for manual calculation saved to my local hard drive. Every time I try to open it, it takes forver and sometimes never opens but I do not get any error messages, let me just tell you that I am running a P4, 1 GB memory, Office 2K with SP3, and nothing else running when I try to open it. As I said it is set for manual calculation, and it is cleared to not auto calculate when opening or closing. Any idea's as to why this is happening? -- Todd I don't know why you're having this problem but I would like to point something out for w...

Converting XML > Dataset
Hi ! Here's my problem in english :-) I want to convert the mssecure.xml file from Microsoft into an dataset to store it in an SQL-Database. My code in VB looks like this : Dim ds As New DataSet Dim doc As New Xml.XmlDocument doc.Load("D:\mssecure.xml") Dim sReader As New StringReader(doc.InnerXml) ds.ReadXml(sReader) The problem is : At the ds.readxml(sreader) command i get an error which told me, that there is an problem whith a relationship in "ProductFamilies" I hope my english is good enough to explain... Please help ! Thanks !!! Christoph Duesmann C...