	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.0

opening, reading, manipulating a .txt file in access using VBA

Asked by dirtycarpet in VB Database Programming, Microsoft Access Database, Visual Basic Programming

Tags: access, vba

Hi Experts,
Im trying to manipulate a file in access 2003 using vba. i have a .txt file that contains strings such as
OLD=1+5050854479878++:007415144+8+12'

ok, firstly i set the fso and textstream to get the file into a text box on a form in access.. this i can do. i also set it to read the file line by line using readline.
my problems start when i try to manipulate the strings.. how do i do it? i know i must use inSTR, Left, Right etc but how do i use them?
as an example im trying to get the above line of code to:
OLD 1 5050854479878 007415144 8 12

i have attached the code i have used so far but i know there is a better way as the strings are not always equal length etc
i have also attached the ,txt order file im using
thanks hugely in advance!

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:

           
           
             Private Sub Command1_Click()
    Dim FSO As New FileSystemObject
    Dim output As String
    Dim txtline As String
    Dim txtstr As TextStream
    Dim lefttype As String
    Dim PositionOfSymbol As Integer
    Dim LengthTextString As Integer
    Dim CharRightOfSymbol As Integer
    Dim TextAfterSymbol As String
    
    Set txtstr = FSO.OpenTextFile("C:\Documents and Settings\Njones\Desktop\Order file.txt", ForReading, False)
   
    
    While Not txtstr.AtEndOfStream
    txtline = txtstr.ReadLine
    
    If Len(txtline) >= 6 Then
        lefttype = Left(txtline, 3)
        
        PositionOfSymbol = InStr(txtline, "=")
        LengthTextString = Len(txtline)
        CharRightOfSymbol = LengthTextString - PositionOfSymbol
        TextAfterSymbol = Right(txtline, CharRightOfSymbol)
     End If
     
        Select Case lefttype
        Case "MHD"
            TextAfterSymbol = " " & mid(txtline, 7, 6)
            output = output & "Message Header" & " - " & TextAfterSymbol & vbCrLf
        Case "CLO"
            TextAfterSymbol = " " & mid(txtline, 5, 13) & " - " & mid(txtline, 19, 15) & " - " & mid(txtline, 35, 13) _
             & "," & mid(txtline, 49, 27) & "," & mid(txtline, 77, 10) & ", " & mid(txtline, 89, 8)
            output = output & "Customer Location Number and Address" & " - " & TextAfterSymbol & vbCrLf
        Case "ORD"
            TextAfterSymbol = " Order No:" & mid(txtline, 9, 6) & " - Date: " & mid(txtline, 17, 6)
            output = output & "Order Number and Date" & " - " & TextAfterSymbol & vbCrLf
        Case "DIN"
            TextAfterSymbol = " " & mid(txtline, 5, 6)
            output = output & "Date in Factory" & " - " & TextAfterSymbol & vbCrLf
        Case "OLD"
            TextAfterSymbol = " " & mid(txtline, 7, 13) & "   " & mid(txtline, 23, 15)
            output = output & "Order Line" & " - " & TextAfterSymbol & vbCrLf
        Case "OTR"
            TextAfterSymbol = " " & mid(txtline, 5, 1)
            output = output & "Order Line Total" & " - " & TextAfterSymbol & vbCrLf
        Case "MTR"
            TextAfterSymbol = " " & mid(txtline, 5, 1)
            output = output & "Total lines in Block Order" & " - " & TextAfterSymbol & vbCrLf & vbCrLf
            End Select
            Wend
        Me.tb1.Value = output
        txtstr.Close
        End Sub

           
         

Attachments:

Order-file.txt (17 KB) (File Type Details)

an example order file

opening, reading, manipulating a .txt file in access using VBA

Asked by dirtycarpet in VB Database Programming, Microsoft Access Database, Visual Basic Programming

Tags: access, vba

About this solution