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

Ouble

New Member
Joined
Nov 30, 2015
Messages
18
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
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Replace "Space(1)" with "vbTab". That should solve the issue for Tab delimited files.
 
Upvote 0
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:
No.CodeColourMinMax
1Ej364100100100
2Wh827100100100
3Od428100100100

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

To this:
filenamefilename
No.CodeNo.Code
1Ej3641Sm902
2Wh8272Qa379
3Od4283Xe298

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Sorry for the trouble and many questions.

Ouble
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top