Excel Forum

Code: Select all

Sub Collect_data_from_NC_code()

    Dim myFile As String
    Dim text As String
    Dim buf As String
    Dim textline() As String
    Const TxtIn1 = "(K:"
    Const TxtIn2 = "(B:"
    Const TxtIn3 = "(S:"
    Const TxtFin = "(ORIGIN 2 NOT USE)"
    Const TxtFin2 = "(END OF FOOTER)"
    Const TxtOut0 = "POPEN;"
    Const TxtOut1 = "DPRNT[SR01*"
    Const TxtOut2 = "DPRNT[SR02*"
    Const TxtOut3 = "DPRNT[SR03*"

    
    Dim K As String
    Dim B As String
    Dim S As String
    Dim i As Integer
    Dim TxtSR01 As String
    Dim TxtSR02 As String
    Dim TxtSR03 As String
    
    
    
    Dim X As Integer



    For X = 9 To 38
    
    'Open file
    strFile_Path_In = Cells(X, 1)
    strFile_Path_Out = Cells(X, 11)
    
    'MsgBox (X)
    'MsgBox (Cells(X, 2))
    
    'strFile_Path_In = "D:\Mr.Jirawat\04_OA-support\02-MC\03_Other\Conprosys\Macro\Macro making\file1"
    'strFile_Path_Out = "D:\Mr.Jirawat\04_OA-support\02-MC\03_Other\Conprosys\Macro\Macro making\fileout2"
    
    'textline = Split(buf, vbLf)
    
    
    Open strFile_Path_In For Input As #1
        L = 1
        
        Do Until EOF(1)
        
            Line Input #1, buf
        Loop
        Close #1
        
               
        textline = Split(buf, vbLf)
        
        
        For i = 0 To UBound(textline)
        
            text = text & textline(i) & vbLf
            If InStr(textline(i), TxtIn1) > 0 Then
                
                'MsgBox (textline(i))
        
                TxtSR01 = Replace(textline(i), TxtIn1, "")
                TxtSR01 = Replace(TxtSR01, " ", "")
                TxtSR01 = Replace(TxtSR01, ")", "")
            End If
            If InStr(textline(i), TxtIn2) > 0 Then
                TxtSR02 = Replace(textline(i), TxtIn2, "")
                TxtSR02 = Replace(TxtSR02, " ", "")
                TxtSR02 = Replace(TxtSR02, ")", "")
            End If
            If InStr(textline(i), TxtIn3) > 0 Then
                TxtSR03 = Replace(textline(i), TxtIn3, "")
                TxtSR03 = Replace(TxtSR03, " ", "")
                TxtSR03 = Replace(TxtSR03, ")", "")
            End If
            
            If InStr(textline(i), TxtFin) > 0 Then
                ' Found line to insert
                text = text & TxtOut0 & vbLf
                text = text & TxtOut1 & TxtSR01 & "];" & vbLf
                text = text & TxtOut2 & TxtSR02 & "];" & vbLf
                text = text & TxtOut3 & TxtSR03 & "];" & vbLf
            End If
          
            If InStr(textline(i), TxtFin2) > 0 Then
                ' Found line to insert
                text = text & TxtOut0 & vbLf
                text = text & TxtOut1 & TxtSR01 & "];" & vbLf
                text = text & TxtOut2 & TxtSR02 & "];" & vbLf
                text = text & TxtOut3 & TxtSR03 & "];" & vbLf
                
            End If
            
        'MsgBox (textline(1))
        
        Next i
        
      'MsgBox (text)
           
           
    Open strFile_Path_Out For Output As #1
    Print #1, text
    MsgBox ("Endloop")
    text = ""
    Close #1
     
     
 
        'Range("A1").Value = Mid(text, K + 3, 5)
        'Range("A2").Value = Mid(text, B + 3, 2)
        'Range("A3").Value = Mid(text, S + 3, 4)
        
    Next X
    
        
End Sub