Have a .txt file that contains specific data that needs to be extracted and placed into corresponding columns in Excel. New to VBA coding so having difficulty in making this work... 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:
Appreciate any help with this... many thanks in advance.
#
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:
Code:
Private Sub CommandButton1_Click()
Dim myFile As String, find1 As String, i As Integer, und As String, speed2 s 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 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]
Do Until EOF(1)
Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , textline
text = text & textline
Loop
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]
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 SubAppreciate any help with this... many thanks in advance.
