How to read textbox with multiple lines separated by slushes?

I have a textbox name text1.text. It has a multiple text. Each line are separated with slushes till the last  line. here's the data of the textbox;

3356\Paracetamol 500mg\30
123490\Amoxicillin 350mg Cap\25
7685\Cefalexin\90

I want to read the first line each data separated by "\" till the last line. How? Pls...
Whing Dela CruzAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Whing Dela CruzConnect With a Mentor Author Commented:
Thanks a lot GrahamSkan, its really working... More Power to you! and God Bless!
0
 
HooKooDooKuConnect With a Mentor Commented:
What exactly do you mean "read"?

If the textbox is a multiline text box, then the entire contents of the textbox is actually a single string of text, with Carriage-Return/Line-Feed pairs denoting where the lines seperate (not sure, but a simple Carriage-Return by itself might also be able to denote the end of the line).

So one solution might be to so a simple string replace to convert the '\' to Carriage-Return/Line-Feed pairs, then use Instr() to find the Carriage-Return/Line-Feed pairs.

Dim A$(100)
Dim str as String
Din
str = Text1.text
Replace( str, "/", vbCrLf )
Dim Count as integer
Dim I as integer
Dim Last as integer

Last = 1
I = InStr( str, vbCrLf )
do while( I > 0 )
    Count = Count + 1
    A$(Count) = Mid$(str, Last, I - Last)
    Last = I + 2
    I = Instr( Last, str, vbCrLf )
loop
0
 
GrahamSkanConnect With a Mentor RetiredCommented:
You could do it like this:

Private Sub Command1_Click()
Dim strlines() As String
    Dim strItems() As String
    Dim l As Integer
    Dim I As Integer
    strlines = Split(Me.Text1.Text, vbCrLf)
    For l = 0 To 2
        strItems() = Split(strlines(l), "\")
        For I = 0 To 2
            Debug.Print l, I, strItems(I)
        Next I
    Next l
End Sub

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Whing Dela CruzAuthor Commented:
Hi HooKooDooKu,
 What I mean about read is;
msgbox first line 'read the first line each data separated by "\" and able to loop till the last line.  
0
 
GrahamSkanRetiredCommented:
Does my code suit you? I don't know what you want to do with the data, so I just show it in the Immediate window.
0
 
Whing Dela CruzAuthor Commented:
GrahamSkan, you got exactly what I need, Thanks a lot.. Could i ask more question? I need more help about the things I've trying to develop.  it is textbox to excell. Thru your help i was able develop the code below, but the problem is.. it only save one data, the last data of the textbox...
Private Sub Command1_Click()
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   
   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.range("A1").Value = "Code"
   oSheet.range("B1").Value = "Description"
   oSheet.range("c1").Value = "Quantity"
   oSheet.range("A1:B1:C1").Font.Bold = True

Dim strlines() As String
    Dim strItems() As String
    Dim l As Integer
    Dim I As Integer
    strlines = Split(Me.Text1.Text, vbCrLf)
    For l = 0 To 2
        strItems() = Split(strlines(l), "\")
        For I = 0 To 2
        Next I
            oSheet.range("A2").Value = strItems(0)
            oSheet.range("B2").Value = strItems(1)
            oSheet.range("C2").Value = strItems(2)
            
    Next l

   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Report.xlsx"
   oExcel.Quit
   
End Sub

Open in new window

0
 
GrahamSkanConnect With a Mentor RetiredCommented:
A couple of problems.

The For/Next loop lines 24 and 25 does nothing, and the lines 26 to 25 do not vary the row. Try this. (I've changed the variable I to c, because I looks a bit like l
Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   oExcel.Visible = True
   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.range("A1").Value = "Code"
   oSheet.range("B1").Value = "Description"
   oSheet.range("c1").Value = "Quantity"
   oSheet.range("A1:B1:C1").Font.Bold = True

Dim strlines() As String
    Dim strItems() As String
    Dim l As Integer
    Dim c As Integer
    strlines = Split(Me.Text1.Text, vbCrLf)
    For l = 0 To 2
        strItems() = Split(strlines(l), "\")
        For c = 0 To 2
            oSheet.Cells(l + 2, c + 1).Value = strItems(c)
         Next c
           
    Next l

   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Report.xlsx"
   oExcel.Quit

Open in new window

0
 
Whing Dela CruzAuthor Commented:
Thank you so much... excellent solution...
0
All Courses

From novice to tech pro — start learning today.