mseawell
11-03-2016, 12:52 PM
Hello,
I'm attempting to create a tool to parse a text file for particular data points and import them into an Excel spreadsheet. Ideally, I would like to use a Command Button to select the text and import the data I want into a new WorkBook sheet.
Here's the code I have so far:
Dim myFile As String, text As String, textline As String, LineLocation As String, DN As Integer, CardCode As String, GND As String
Sub Button1_Click()
'Add a new sheet
ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Working LENs"
'Add column headers
Range("A1").Value = "Line Location"
Range("B1").Value = "DN "
Range("C1").Value = "CardCode"
Range("D1").Value = "GND"
'Show open file dialog box
myFile = Application.GetOpenFilename()
'Open file
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
'Close file
Close #1
LineLocation = InStr(text, "LEN:")
DN = InStr(text, "DN")
CardCode = InStr(text, "CARDCODE:")
GND = InStr(text, "GND:")
Range("=$A$2").Value = Mid(text, LineLocation + 5, 20)
Range("=$B$2").Value = Mid(text, DN + 3, 10)
Range("=$C$2").Value = Mid(text, CardCode + 9, 8)
Range("=$D$2").Value = Mid(text, GND + 4, 2)
End Sub
Private Sub CommandButton1_Click()
End Sub
With this code I'm able to create the headers for each row and import the first row of data. But it is not importing the data from the remainder of my text file.
Also, it is creating the new sheet but the data is imported into the sheet where the command button is located.
I have attached an example of my text file I'm wanting to parse and the output I'm wanting to acheive.
Thanks,
Mark
I'm attempting to create a tool to parse a text file for particular data points and import them into an Excel spreadsheet. Ideally, I would like to use a Command Button to select the text and import the data I want into a new WorkBook sheet.
Here's the code I have so far:
Dim myFile As String, text As String, textline As String, LineLocation As String, DN As Integer, CardCode As String, GND As String
Sub Button1_Click()
'Add a new sheet
ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Working LENs"
'Add column headers
Range("A1").Value = "Line Location"
Range("B1").Value = "DN "
Range("C1").Value = "CardCode"
Range("D1").Value = "GND"
'Show open file dialog box
myFile = Application.GetOpenFilename()
'Open file
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
'Close file
Close #1
LineLocation = InStr(text, "LEN:")
DN = InStr(text, "DN")
CardCode = InStr(text, "CARDCODE:")
GND = InStr(text, "GND:")
Range("=$A$2").Value = Mid(text, LineLocation + 5, 20)
Range("=$B$2").Value = Mid(text, DN + 3, 10)
Range("=$C$2").Value = Mid(text, CardCode + 9, 8)
Range("=$D$2").Value = Mid(text, GND + 4, 2)
End Sub
Private Sub CommandButton1_Click()
End Sub
With this code I'm able to create the headers for each row and import the first row of data. But it is not importing the data from the remainder of my text file.
Also, it is creating the new sheet but the data is imported into the sheet where the command button is located.
I have attached an example of my text file I'm wanting to parse and the output I'm wanting to acheive.
Thanks,
Mark