I have a .txt file that contains specific data that needs to be extracted and placed into corresponding columns in Excel. Below shows the code I have thus far but when run, it only extracts the first set of data but does not move onto the next block of text.
In the Excel file I need:
Description (company Name) | Speed (eg 1M) | Service Num. (7-digit number after the speed).
The following is sample data present in the .txt file:
#
interface GigabitEthernet5/
vlan-type aser 7878
description ABC_COMPANY_1M_1254589_4444243
ip binding vpn-instance internet_vpn
ip address 158.214.125.215
#
interface GigabitEthernet5/0
vlan-type frin 2255
description XYZ_COMPANY_6M_1458963_444
ip binding vpn-instance internet_vpn
ip address 148.214.25.214
#
All data required comes after the interface GigabitEthernet line eg.
Description: ABC_COMPANY | Speed: 1M | Service Num: 1254589
There is also loads of data that comes before and after these blocks that does not need extracting.
The code below extracts correctly but does not move onto the next block of data required:
Private Sub CommandButton1_Click()
Dim myFile As String, find1 As String, i As Integer, und As String, speed2 As Integer,_
text As String, Desc As String, r As Long, dashpos As Long, m As Long, textline As String,_
posLat As Integer, posLong As Integer, strLeft As String, strFind As String,_
strRight As String, strMid As String, speed As String
myFile = "C:\dump2.txt"
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
Desc = InStr(text, "interface GigabitEthernet")
speed = InStr(text, "M_")
Range("A1").Value = "Description"
Range("B1").Value = "Speed"
Range("c1").Value = "Service Num"
Range("A2").Value = Mid(text, Desc + 68, 30)
Range("b2").Value = Mid(text, speed + -3, 4)
und = Mid(text, speed + -3, 4)
speed2 = InStr(1, und, "_")
Dim finalString As String
finalString = Right(und, Len(und) - speed2 + 0)
Range("b2").Value = finalString
Desc = InStr(text, "interface GigabitEthernet")
speed = InStr(text, "M_")
Range("C2").Value = Mid(text, speed + 2, 6)
End Sub
Do..Loop) to go through each line of the text file. Just check if the line begins withinterface GigabitEthernetand then do you checks withInStretc.Do Until...Loop. But you're using it merely to load a string with the contents of the file. Instead, as you read each line, perform tests and subsequent actions based on that line to build your output.