MultiSelect Textbox
 

Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Where is the information you are pulling in located at... In a variable? In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to put it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a variable? In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can manipulate
it? Do you put the result of the query in a variable, array, worksheet, the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Rick,
I can put results it into the textbox then manipulate it from there or how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can manipulate
it? Do you put the result of the query in a variable, array, worksheet, the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there or how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array, worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one
per
line) on a worksheet? The Textbox itself? An array variable? Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
..Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
..AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there or how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array, worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one
per
line) on a worksheet? The Textbox itself? An array variable? Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

I left that part off on purpose because your first post didn't show it in
the "what I want it to look like afterwards" section... removing the
next-to-last statement will allow that part of the text to show...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
TextBox1.Text = Txt

I see you also switched from a TextBox (again, mentioned in your first post)
to a ListBox. I'm not sure if that was a desired change or only one you made
to try and solve the problem you were having. The above code assumes you
still want to use a TextBox. If not, write back and I'll modify it for a
ListBox.

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement
to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton
Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there or
how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you
query
the database, the result of that query goes somewhere... my question
is
where? We can pull the text apart relatively easily, but I need to
know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array,
worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im
using
ADODB/SQL query to get the data out of the database. I would like
to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one
per
line) on a worksheet? The Textbox itself? An array variable? Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

'Here is where im at. The For iC = 1 To Len(Trim(sT)) loop is leaving off the
end of the sting "Safe Fund- 300.00" Yes I have switched to a Multiline
texted box so to be able to modify and push the results back to the database.
And Im really doing this as an .exe program with vb.net 2005.
'sT = Tax-82.54 Disc-0 Online-206.50
'Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total Coupons-'10.00€¡Safe Fund- 300.00

sSQL = "SELECT RegReconcile.* FROM RegReconcile
WHERE(((RegReconcile.REG_Z_COUNTER)=" & sRegisterZ & "));"
rs = New ADODB.Recordset
rs.Open(sSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly,
ADODB.LockTypeEnum.adLockReadOnly)

If (Not rs.EOF) Then
sT = rs.Fields("Z_NOTES").Value
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
sZNotes = sZNotes & sO & vbCrLf
sO = ""
End If
Next
rs.Close()
rs = Nothing
With Me.txtNotes
.Text = sZNotes
End With
End If

"Rick Rothstein" wrote:

I left that part off on purpose because your first post didn't show it in
the "what I want it to look like afterwards" section... removing the
next-to-last statement will allow that part of the text to show...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
TextBox1.Text = Txt

I see you also switched from a TextBox (again, mentioned in your first post)
to a ListBox. I'm not sure if that was a desired change or only one you made
to try and solve the problem you were having. The above code assumes you
still want to use a TextBox. If not, write back and I'll modify it for a
ListBox.

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement
to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton
Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there or
how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you
query
the database, the result of that query goes somewhere... my question
is
where? We can pull the text apart relatively easily, but I need to
know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array,
worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im
using
ADODB/SQL query to get the data out of the database. I would like
to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells (one
per
line) on a worksheet? The Textbox itself? An array variable? Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Uh, you might have considered mentioning the VB.NET 2005 part a lot sooner
in this thread... it is a **major** factor. The Excel programming this
newsgroup tends to handle is the VBA version of VB6 built into Excel... VB6
and VB.NET (any version) are pretty much as different from each other as
night and day. The type of parsing you are doing does not really need Excel
at all, so I'm thinking you might be better served asking your question in a
newsgroup devoted to the .NET version of VB rather than here in an Excel
oriented newsgroup. (I don't know the .NET version of VB, so I can't help
you with that part at all.) You best bet is to look for newsgroups with the
word "dotnet" in their names. Some examples...

microsoft.public.dotnet.general
microsoft.public.dotnet.languages.vb

--
Rick (MVP - Excel)


"Mike" wrote in message
...
'Here is where im at. The For iC = 1 To Len(Trim(sT)) loop is leaving off
the
end of the sting "Safe Fund- 300.00" Yes I have switched to a Multiline
texted box so to be able to modify and push the results back to the
database.
And Im really doing this as an .exe program with vb.net 2005.
'sT = Tax-82.54 Disc-0 Online-206.50
'Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-'10.00€¡Safe Fund- 300.00

sSQL = "SELECT RegReconcile.* FROM RegReconcile
WHERE(((RegReconcile.REG_Z_COUNTER)=" & sRegisterZ & "));"
rs = New ADODB.Recordset
rs.Open(sSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly,
ADODB.LockTypeEnum.adLockReadOnly)

If (Not rs.EOF) Then
sT = rs.Fields("Z_NOTES").Value
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
sZNotes = sZNotes & sO & vbCrLf
sO = ""
End If
Next
rs.Close()
rs = Nothing
With Me.txtNotes
.Text = sZNotes
End With
End If

"Rick Rothstein" wrote:

I left that part off on purpose because your first post didn't show it in
the "what I want it to look like afterwards" section... removing the
next-to-last statement will allow that part of the text to show...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
TextBox1.Text = Txt

I see you also switched from a TextBox (again, mentioned in your first
post)
to a ListBox. I'm not sure if that was a desired change or only one you
made
to try and solve the problem you were having. The above code assumes you
still want to use a TextBox. If not, write back and I'll modify it for a
ListBox.

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment
statement
to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton
Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there
or
how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you
query
the database, the result of that query goes somewhere... my
question
is
where? We can pull the text apart relatively easily, but I need to
know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array,
worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im
using
ADODB/SQL query to get the data out of the database. I would
like
to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells
(one
per
line) on a worksheet? The Textbox itself? An array variable?
Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like
this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Rick
You maybe right but why wont this work in excel then ??? It leaves off "Safe
Fund- 300.00"

Test and see for yourself

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
..Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
..AddItem sO
sO = ""
End If
Next
End With
End Sub
"Rick Rothstein" wrote:

Uh, you might have considered mentioning the VB.NET 2005 part a lot sooner
in this thread... it is a **major** factor. The Excel programming this
newsgroup tends to handle is the VBA version of VB6 built into Excel... VB6
and VB.NET (any version) are pretty much as different from each other as
night and day. The type of parsing you are doing does not really need Excel
at all, so I'm thinking you might be better served asking your question in a
newsgroup devoted to the .NET version of VB rather than here in an Excel
oriented newsgroup. (I don't know the .NET version of VB, so I can't help
you with that part at all.) You best bet is to look for newsgroups with the
word "dotnet" in their names. Some examples...

microsoft.public.dotnet.general
microsoft.public.dotnet.languages.vb

--
Rick (MVP - Excel)


"Mike" wrote in message
...
'Here is where im at. The For iC = 1 To Len(Trim(sT)) loop is leaving off
the
end of the sting "Safe Fund- 300.00" Yes I have switched to a Multiline
texted box so to be able to modify and push the results back to the
database.
And Im really doing this as an .exe program with vb.net 2005.
'sT = Tax-82.54 Disc-0 Online-206.50
'Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-'10.00€¡Safe Fund- 300.00

sSQL = "SELECT RegReconcile.* FROM RegReconcile
WHERE(((RegReconcile.REG_Z_COUNTER)=" & sRegisterZ & "));"
rs = New ADODB.Recordset
rs.Open(sSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly,
ADODB.LockTypeEnum.adLockReadOnly)

If (Not rs.EOF) Then
sT = rs.Fields("Z_NOTES").Value
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
sZNotes = sZNotes & sO & vbCrLf
sO = ""
End If
Next
rs.Close()
rs = Nothing
With Me.txtNotes
.Text = sZNotes
End With
End If

"Rick Rothstein" wrote:

I left that part off on purpose because your first post didn't show it in
the "what I want it to look like afterwards" section... removing the
next-to-last statement will allow that part of the text to show...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
TextBox1.Text = Txt

I see you also switched from a TextBox (again, mentioned in your first
post)
to a ListBox. I'm not sure if that was a desired change or only one you
made
to try and solve the problem you were having. The above code assumes you
still want to use a TextBox. If not, write back and I'll modify it for a
ListBox.

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment
statement
to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton
Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from there
or
how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When you
query
the database, the result of that query goes somewhere... my
question
is
where? We can pull the text apart relatively easily, but I need to
know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array,
worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database. Im
using
ADODB/SQL query to get the data out of the database. I would
like
to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three cells
(one
per
line) on a worksheet? The Textbox itself? An array variable?
Some
place
else? Where do you want the split apart text to be placed... the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like
this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

MultiSelect Textbox
 

Because your instruction to place the text in the ListBox is conditioned on
finding the character with ASCII code 135... that character does not exist
at the end of your text string so the last formed text string is never
written out to the ListBox. Here is a modification to your code that handles
the problem...

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT) & Chr(135))
If Asc(Mid(sT & Chr(135), iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick
You maybe right but why wont this work in excel then ??? It leaves off
"Safe
Fund- 300.00"

Test and see for yourself

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub
"Rick Rothstein" wrote:

Uh, you might have considered mentioning the VB.NET 2005 part a lot
sooner
in this thread... it is a **major** factor. The Excel programming this
newsgroup tends to handle is the VBA version of VB6 built into Excel...
VB6
and VB.NET (any version) are pretty much as different from each other as
night and day. The type of parsing you are doing does not really need
Excel
at all, so I'm thinking you might be better served asking your question
in a
newsgroup devoted to the .NET version of VB rather than here in an Excel
oriented newsgroup. (I don't know the .NET version of VB, so I can't help
you with that part at all.) You best bet is to look for newsgroups with
the
word "dotnet" in their names. Some examples...

microsoft.public.dotnet.general
microsoft.public.dotnet.languages.vb

--
Rick (MVP - Excel)


"Mike" wrote in message
...
'Here is where im at. The For iC = 1 To Len(Trim(sT)) loop is leaving
off
the
end of the sting "Safe Fund- 300.00" Yes I have switched to a Multiline
texted box so to be able to modify and push the results back to the
database.
And Im really doing this as an .exe program with vb.net 2005.
'sT = Tax-82.54 Disc-0 Online-206.50
'Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-'10.00€¡Safe Fund- 300.00

sSQL = "SELECT RegReconcile.* FROM RegReconcile
WHERE(((RegReconcile.REG_Z_COUNTER)=" & sRegisterZ & "));"
rs = New ADODB.Recordset
rs.Open(sSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly,
ADODB.LockTypeEnum.adLockReadOnly)

If (Not rs.EOF) Then
sT = rs.Fields("Z_NOTES").Value
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
sZNotes = sZNotes & sO & vbCrLf
sO = ""
End If
Next
rs.Close()
rs = Nothing
With Me.txtNotes
.Text = sZNotes
End With
End If

"Rick Rothstein" wrote:

I left that part off on purpose because your first post didn't show it
in
the "what I want it to look like afterwards" section... removing the
next-to-last statement will allow that part of the text to show...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
TextBox1.Text = Txt

I see you also switched from a TextBox (again, mentioned in your first
post)
to a ListBox. I'm not sure if that was a desired change or only one
you
made
to try and solve the problem you were having. The above code assumes
you
still want to use a TextBox. If not, write back and I'll modify it for
a
ListBox.

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick
Here is what I have but it is leaving off the last part of the
string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) < 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub


"Rick Rothstein" wrote:

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment
statement
to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a
CommandButton
Click
event).

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
I can put results it into the textbox then manipulate it from
there
or
how
ever you think would be eaisier. Variable maybe ?? Im not sure.

"Rick Rothstein" wrote:

I don't do databases, so I can't address that part at all. When
you
query
the database, the result of that query goes somewhere... my
question
is
where? We can pull the text apart relatively easily, but I need
to
know
where the text is at (that is, where can VB see it at) so I can
manipulate
it? Do you put the result of the query in a variable, array,
worksheet,
the
TextBox, somewhere else?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Rick,
The information is located in a field of an access database.
Im
using
ADODB/SQL query to get the data out of the database. I would
like
to
put
it
into the Textbox.

"Rick Rothstein" wrote:

Where is the information you are pulling in located at... In
a
variable?
In
3 variables (one per line)? A cell on a worksheet? Three
cells
(one
per
line) on a worksheet? The Textbox itself? An array variable?
Some
place
else? Where do you want the split apart text to be placed...
the
TextBox?

--
Rick (MVP - Excel)


"Mike" wrote in message
...
Im pulling some data from an acces database that looks like
this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00€¡CC-146€¡€¡Visa-139.14€¡MC-€¡Debit-187.26€¡€¡PM-3.00€¡RJR-€¡Lor-€¡S&M-€¡USST-7.00€¡Other-€¡Total
Coupons-10.00€¡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00