Copy three parameters/data from notepad to excel

[axebrock]

Hi all

I have been trying to build a macro to copy three parameters/data from notepad and copy it to excel. It seems easy but i didn't know how can i start with it. I could start to try opening the notepad file with the following codes.




Private Sub CommandButton1_Click()
Dim File As String, text As String, textline As String
File = "C:\test\test.txt"
File = Application.GetOpenFilename()
Open File For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1

I can't seem to upload the text file here so i capture the screens and placed it in test.docx. I have attached the excel file (Book_test.xls) for your references.
Basically, i want to copy only three parameters from the notepad namely the, Energy, STD VOLUME and CV, and then paste it to the excel file according to their respective date and time. i hope someone can give me some pointers. Thanks in advance.

[mdmackillop]

Try zipping your text file to post it.

[axebrock]

Hi mdmackillop
thanks for highlighting to me
i have attached the test.txt in a zip file.

[mdmackillop]

This does depend upon a standard txt layout

Option Explicit


Private Sub Test()
Dim File As String, text As String, textline As String
Dim x
Dim i As Long, j As Long
Dim arr(2)
File = "C:\VBAX\test.txt"
Open File For Input As #1
Do Until EOF(1)
j = j + 1
Line Input #1, textline
Select Case j
Case 14
For i = 10 To 2 Step -1
textline = Replace(textline, String(i, " "), " ")
Next
arr(0) = Split(textline)(1)
arr(1) = Split(textline)(2)
Case 18
For i = 10 To 2 Step -1
textline = Replace(textline, String(i, " "), " ")
Next
arr(2) = Split(textline)(4)
End Select
Loop
Call Testing(arr)
Close #1
End Sub


Sub Testing(arr As Variant)
MsgBox arr(0) & vbCr & arr(1) & vbCr & arr(2)
End Sub

[axebrock]

hi mdmackillop

thanks for your code, amazing it works. however it copied to the msg box, i want it tocopy to excel. may i know how do u managed to locate the position of thedata in the notepad which i wanna to copy ? The excel file which i attached isthe exact format and data i wanna to copy from notepad.

[mdmackillop]

Just add in a print line and watch the Immediate window as you step through

Line Input #1, textline
Debug.Print j & " - " & textline


If you had a very large text file you could let the code find a marker e.g. Instr(1,"BBTU") and relate the count to that line.

The Testing code can as easily enter the value into a cell or or other function, depending upon your requirements.

[axebrock]

hi mdmackillop

other than using the filepath (File = "C:\VBAX\test.txt") method, if i want to open text file other than test.txt. For example, let say i want to open test2.txt which mean i have to go to the source code and change test.txt to text2.txt, then i can open text2.txt. Is there a way to set the filepath to accept any text filename ? Thanks

[mdmackillop]

You could use an Inputbox or GetOpenFilename

[snb]

or:


Sub M_snb()
With Application.FileDialog(1)
.initialfilename="C:\Test\*.txt"
.FilterIndex = 6
If .Show Then Sheets.Add , Sheets(Sheets.Count), , .SelectedItems(1)
End With
End Sub