VBA: create macro button to open file directory and import txt data to excel

[Ouble]

Hi all, ive been searching the web for answers over the weekend but to no avail.

i have no knowledge on vba codes so go easy on me. i would need help on the following:

1. when i click the button, it opens file directory C:\desktop\
2. i will proceed to select .txt or .csv file
3. upon selection, the data in the file will be imported to excel (in the same sheet where i click the button). eg. button position at A1, data imported start from A2
4. below is the sample data in .txt file

"Channel:" "CH4 "
"Description: " "LoadingR "
"Span " 5000
"Zero " 0
"F.S.D. " "0005000"
"Offset " "0000000"
"Units" " "
"Linear " "Interval:" "00:00:01"
"Delayd" "Delay:" "00:00:00"

"----------------------------------------------------------------"

"Nu." "Date" "Time" "Seconds" "Base" "Divs" "Value"

"1" "16NOV/15" "15:33:22" 0 0 92 "0000092"
"2" "16NOV/15" "15:33:23" 1 1 94 "0000094"
"3" "16NOV/15" "15:33:24" 2 2 95 "0000095"
"4" "16NOV/15" "15:33:25" 3 3 92 "0000092"
"5" "16NOV/15" "15:33:26" 4 4 92 "0000092"
"6" "16NOV/15" "15:33:27" 5 5 92 "0000092"
"----------------------------------------------------------------"

"Channel:" "CH6 "
"Description: " "LVDT "
"Span " 2500
"Zero " 0
"F.S.D. " "0025.00"
"Offset " "0000.00"
"Units" "mm "
"Linear " "Interval:" "00:00:01"
"Delayd" "Delay:" "00:00:00"

"----------------------------------------------------------------"

"Nu." "Date" "Time" "Seconds" "Base" "Divs" "Value"

"1" "16NOV/15" "15:33:22" 0 0 22 "0000.22"
"2" "16NOV/15" "15:33:23" 1 1 22 "0000.22"
"3" "16NOV/15" "15:33:24" 2 2 24 "0000.24"
"4" "16NOV/15" "15:33:25" 3 3 25 "0000.25"
"5" "16NOV/15" "15:33:26" 4 4 27 "0000.27"
"6" "16NOV/15" "15:33:27" 5 5 29 "0000.29"
"----------------------------------------------------------------"

"Channel:" "CH14 "
"Description: " "Load Cel "
"Span " 2500
"Zero " 0
"F.S.D. " "002.500"
"Offset " "000.000"
"Units" "1kN "
"Linear " "Interval:" "00:00:01"
"Delayd" "Delay:" "00:00:00"

"----------------------------------------------------------------"

"Nu." "Date" "Time" "Seconds" "Base" "Divs" "Value"

"1" "16NOV/15" "15:33:22" 0 0 -6 "-00.006"
"2" "16NOV/15" "15:33:23" 1 1 -6 "-00.006"
"3" "16NOV/15" "15:33:24" 2 2 -6 "-00.006"
"4" "16NOV/15" "15:33:25" 3 3 -4 "-00.004"
"5" "16NOV/15" "15:33:26" 4 4 -4 "-00.004"
"6" "16NOV/15" "15:33:27" 5 5 -4 "-00.004"
"----------------------------------------------------------------"

5. i will need values from channel CH6 & channel CH14 only
6. CH6 values will be paste into excel from A2 onwards & CH14 from M2 onwards
7. i will need "Nu." "Date" "Time" "Seconds" "Base" "Divs" "Value" data to be in individual columns.
8. heres the tricky part. the sample above shows data from number 1-6. this is not the case for different .txt files whereby the data could be from number 1-223, 1-346,etc...the number of data generated in each .txt file is dependent of the test duration.
9. so how do i tell excel that i want to import the data for CH6 to be paste in cell A2 onwards & CH14 in cell M2 onwards?

im amist of slitting my wrist if this have no solution. its really time consuming to copy and paste every data manually.

any help is greatly appreciated!

Ouble

 

[b.downey]

this should get you most of the way to your solution
Based on the Data supplied, it appears that there is a space separating the values.

Option Explicit


Sub Process()
    Dim strCH As String
    Dim WrtRec As Boolean
    Dim s As String
    Dim v As Variant
    Dim RowNo As Long
    Dim ColNo As Long
    Dim Offset As Integer
    
    Dim strFilename As String
    strFilename = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    
    If strFilename = "" Then Exit Sub
    
    Dim FileNum As Integer
    FileNum = FreeFile()
    Open strFilename For Input As FileNum
    
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.ActiveSheet
    
    Do While Not EOF(FileNum)
        Line Input #1, s
        v = Split(s, Space(1))
        If UBound(v) >= 1 Then
            v(0) = Trim(Replace(v(0), Chr(34), ""))
            Select Case Left(v(0), 3)
                Case "Cha"      'Channel
                    strCH = Trim(Replace(v(1), Chr(34), ""))
                    Select Case strCH
                        Case "CH6"
                            Offset = 1  'Column "A"
                        Case "CH14"
                            Offset = 13 'Column "M"
                        Case Else
                            Offset = 0
                    End Select
                    RowNo = 2
                Case "Nu."
                    WrtRec = True
                Case "---"
                    WrtRec = False
            End Select
            
            If WrtRec And (Offset > 0) Then
                For ColNo = 0 To UBound(v)
                    ws.Cells(RowNo, ColNo + Offset) = Trim(Replace(v(ColNo), Chr(34), ""))
                Next ColNo
                RowNo = RowNo + 1
            End If
        End If
    Loop
    
    Set ws = Nothing
    Close (FileNum)
End Sub

 

[Ouble]

Hi b.downey,

Thanks for your reply.

Ive edited this section

Dim strFilename As String
strFilename = Application.GetOpenFilename("All Files (*.), *.")

It seemed that im unable to import .csv files. How may I go about it?

Huge thanks.

Ouble

 

[Ouble]

I realised that my testing files are in .csv which are more towards Tab delimited instead of space. Your kind help is greatly required.

Ouble

 

[b.downey]

Replace "Space(1)" with "vbTab". That should solve the issue for Tab delimited files.

 

[Ouble]

Hi b.downey,

Thank you Thank you! it works!
You have been a great help.

Ouble

 

[b.downey]

excellent. Glad I could help

 

[Ouble]

Hi b.downey,

Lets say that I would like to import multiple excel data into the same sheet but only require data from the first 2 columns. How may I edit the code? I would also like the imported filename to be copied into excel.

Excel data:
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]No.[/TD]
[TD="class: xl66, width: 64"]Code[/TD]
[TD="class: xl66, width: 64"]Colour[/TD]
[TD="class: xl66, width: 64"]Min[/TD]
[TD="class: xl66, width: 64"]Max[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Ej364[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]100[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Wh827[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]100[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Od428[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]100[/TD]
[/TR]
</tbody>[/TABLE]

To this:
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]filename[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]filename[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66"]No.[/TD]
[TD="class: xl66"]Code[/TD]
[TD][/TD]
[TD="class: xl66"]No.[/TD]
[TD="class: xl66"]Code[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Ej364[/TD]
[TD][/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Sm902[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Wh827[/TD]
[TD][/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Qa379[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Od428[/TD]
[TD][/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Xe298[/TD]
[/TR]
</tbody>[/TABLE]

Sorry for the trouble and many questions.

Ouble