reddragon477
10-05-2017, 06:03 AM
Dear All,
I am new to excel VBA and have been taking some interest to automate few tasks using VBA. I have some pdfs where I need to re-enter the data into the excel sheet manually and later create pdf reports. So first I successfully created a way to convert pdf to text file and now I would like to parse specific data from this text file to a blank excel sheet.
My sample code:
Option Explicit
Private Sub CommandButton1_Click()
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
myFile = "C:\test\sample.txt"
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
posLat = InStr(text, "Inst. name")
posLong = InStr(text, "Country")
Range("A1").Value = Mid(text, posLat + 49, 22)
Range("A2").Value = Mid(text, posLong + 30, 40)
End Sub
Basically its a tabulated pdf converted to text file and the information on the 1st column is the same for all, but the form/pdf information could vary and I would like to extract this data to the excel sheet. So I tried using
But I have two problems
1) The information could be bigger that the range limit I used in "InStr()".
2) Secondly, certain information like address could be spanning in other lines of the text file.
So I guess I need to use a loop or if statement method. Which I am not sure of. Could someone kindly direct me in the right way for me to extract the data to the excel sheet.
I have attached the sample text file for reference.20582
Thank you all
I am new to excel VBA and have been taking some interest to automate few tasks using VBA. I have some pdfs where I need to re-enter the data into the excel sheet manually and later create pdf reports. So first I successfully created a way to convert pdf to text file and now I would like to parse specific data from this text file to a blank excel sheet.
My sample code:
Option Explicit
Private Sub CommandButton1_Click()
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
myFile = "C:\test\sample.txt"
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
posLat = InStr(text, "Inst. name")
posLong = InStr(text, "Country")
Range("A1").Value = Mid(text, posLat + 49, 22)
Range("A2").Value = Mid(text, posLong + 30, 40)
End Sub
Basically its a tabulated pdf converted to text file and the information on the 1st column is the same for all, but the form/pdf information could vary and I would like to extract this data to the excel sheet. So I tried using
But I have two problems
1) The information could be bigger that the range limit I used in "InStr()".
2) Secondly, certain information like address could be spanning in other lines of the text file.
So I guess I need to use a loop or if statement method. Which I am not sure of. Could someone kindly direct me in the right way for me to extract the data to the excel sheet.
I have attached the sample text file for reference.20582
Thank you all