VB代码以从* .txt文件&中提取一些参数通过使用浏览按钮在excel中将其转换 [英] VB Code to extract some parameters from *.txt file & convert the same in excel by use of Browse Button

问题描述

如何从原始文件(即记事本(* .txt文件)&在excel中将其转换为?

例如.我的原始文件有点像这样:

How can I extract some parameters from raw file i.e from notepad(*.txt file) & convert the same in excel?

eg. I have raw file somewhat like this:

FIELD REPORTING COMMAND <TU_>
<

FIELD REPORTING COMMAND <TU_>
<

FIELD REPORTING COMMAND <TU_>
<

FIELD REPORTING COMMAND <TU_>
<

FIELD REPORTING COMMAND <TU_>
<

FIELD REPORTING COMMAND <TU_>
< ZZZZ;
END OF DIALOGUE SESSION

[Connection to 10.131.64.102 closed by foreign host]
rtrwb1>telnet 10.131.0.102
Trying 10.131.0.102 ... Open
GNFMN1
GNFMNO


ENTER USERNAME < GNFMN1
ENTER PASSWORD < ******
MSCi      ASN_GMSC                  2010-10-08  13:05:12


                       WELCOME TO THE DX 200 SERIES DIALOGUE


MAIN LEVEL COMMAND <___>
< ZDRI;

LOADING PROGRAM VERSION 2.10-0
EXECUTION STARTED

MSCi      ASN_GMSC                  2010-10-08  13:05:16

INPUT      STATE      USED INPUT  PRIORITY
----- --------------- ---------- ----------
2M1   CONNECTED           -          3
2M2   CONNECTED           -          2
2M3   CONNECTED           -          5
2M4   CONNECTED           -          4
FS1   CONNECTED          FS1         7
FS2   CONNECTED           -          6
SYNCHRONIZATION UNIT WORKING MODE ......... HIERARCHIC SYNCHRONIZATION
FUNCTION AUTOMATIC RETURN FROM PLESIOCHRONOUS OPERATION ..........  ON
FUNCTION AUTOMATIC USE OF REPAIRED INPUTS ........................  ON
SYNCHRONIZATION UNIT    0 OSCILLATOR CONTROL WORD VALUE .......  32110
SYNCHRONIZATION UNIT    1 OSCILLATOR CONTROL WORD VALUE .......  30146
SYNCHRONIZATION UNIT    0 OSCILLATOR CONTROL MODE ............. NORMAL
SYNCHRONIZATION UNIT    1 OSCILLATOR CONTROL MODE ............. NORMAL
TIMER: SYNCHRONIZATION SIGNAL MALFUNCTION TOLERANCE TIME ...     5 MIN
TIMER: REPAIRED SYNCHRONIZATION INPUT OBSERVATION TIME .....    10 MIN
COMMAND EXECUTED

在上述相同的原始文件中，我需要使用浏览按钮在excel中显示列，该浏览器将要求显示在相邻文本框&中显示的原始文件的路径.然后点击处理"按钮，将打开一个excel文件&将o/p显示为:

网元名称:ASN_GMSC
同步单位:0振荡器控制字值:32110
同步单元:1个振荡器控制字值:30146
计时器:13:05:16
日期:2010-10-08

只需要这些字段.
请按照上面要求的代码更新我.

Out of the same above raw file I need to display columns in excel by use of browse button in which it would ask for path of raw file which would be displayd in in neighbouring textbox & then on clicking Process button it will open up a excel file & display o/p as:

NE Name:ASN_GMSC
Synchronisation Unit: 0 Oscillator Control Word value: 32110
Synchronisation Unit: 1 Oscillator Control Word value: 30146
Timer:13:05:16
Date:2010-10-08

Only these fields are required.
Please update me in code as above required one

推荐答案

我无法理解输出，因此我将展示如何逐行打开和读取* .txt文件使用打开"对话框.

I can''t uderstand output, so i will show how to open and read *.txt file line by line using Open dialog box.

Sub GetDX200Data()
Dim sFileName As String, iNumFile As Integer
Dim sTmp As String, dstWsh As Worksheet
Dim iRow As Long

''On error goto Error handler
On Error GoTo Err_GetDX200Data

''set variable for destination worksheet
Set dstWsh = ThisWorkbook.Worksheets(1)
''get *.txt file name
sFileName = GetMyFileName()
''in case of Cancel button was pressed
If sFileName = "" Then GoTo Exit_GetDX200Data

''start import from first row
iRow = 1
''to open *.txt file, get number
iNumFile = FreeFile()
''open *.txt file
Open sFileName For Input As iNumFile
    ''until it''s not then end of file
    Do While Not EOF(iNumFile)
        ''read line to variable sTmp
        Line Input #iNumFile, sTmp
        ''insert data into cell
        dstWsh.Range("A" & iRow) = sTmp
        ''increase counter
        iRow = iRow + 1
    Loop
''close file
Close #iNumFile

Exit_GetDX200Data:
    On Error Resume Next
    ''free memory used for  object variable: worksheet
    Set dstWsh = Nothing
    Exit Sub

Err_GetDX200Data:
    MsgBox Err.Description, vbExclamation, Err.Number
    ''goto Exit
    Resume Exit_GetDX200Data
End Sub

和用于调用打开对话框的功能

And function used for call Open dialog box

''return: file name with path
Function GetMyFileName() As String
Dim sTmp As Variant ''subtype: String

sTmp = Application.GetOpenFilename("Text Files (*.txt),*.txt", 0, "Open DX file...", "Open", False)
''in case of ''Cancel'' pressed
If CStr(sTmp) = CStr(False) Then sTmp = ""
GetMyFileName = CStr(sTmp)

End Function

这篇关于VB代码以从* .txt文件&中提取一些参数通过使用浏览按钮在excel中将其转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！