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:

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
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:

Ouble

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

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

Active Member
Joined
Oct 16, 2011
Messages
484
Replace "Space(1)" with "vbTab". That should solve the issue for Tab delimited files.
 

Ouble

New Member
Joined
Nov 30, 2015
Messages
18
Hi b.downey,

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

Ouble
 

Ouble

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

Forum statistics

Threads
1,082,333
Messages
5,364,675
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top