Import a text file via macro, then select and import certain data.

[jamesrawlinson]

Hi all,

Macro noob here, would love some help.

I have a simple Excel table where I would like to import a .txt file via the button on the spreadsheet and auto populate the cells 'calibration flashed'.

So UNIT 1 would populate cells C8 and C9 with the info contained in the UNIT 1 section of the .txt file and so on.

The reason this is difficult is there is a set .txt format, so some bits of info will need to be ignored in the txt file.

Documents are attached as is a screen shot of the sheet and the text file.

EXAMPLE.PNG

This is a simple version of what I'd like in the end, but this will help me with some starter macros.

Thanks for any help!
James

[CheeseSandwich]

The attached may help, Note: i have removed your merged cells and replaced them with: Centre accross selection formatting.

Code in file below:

Please Login or Register  to view this content.

[jamesrawlinson]

Thank you very much.

Could you give me a hint as to how to change that line to enable a 'browse for file' rather than a dedicated path?

Attachment 781919

[jamesrawlinson]

Thank you very much.

Could you give me a hint as to how to change that line to enable a 'browse for file' rather than a dedicated path?

[CheeseSandwich]

Maybe like this:

Please Login or Register  to view this content.

[jamesrawlinson]

Thank you for the aid - I'm trying to make the macro more complex but am struggling to make it heading dependent.

If you take a look at the screenshot below you will see what I am trying to achieve:

Attachment 782244

Please could you take a look at the attached documents and advise on some changes?

Regards

[Marc L]

First, according to forum rules thanks to not create any duplicate thread anymore …

According to your last attachment as it is (no mod is required and no needs to 'Clear Cells') a VBA basics demonstration for starters :

Sub Demo1()
  Const D = ": "
    Dim V, R&, W, X(1, 0)
        V = Application.GetOpenFilename("Text files,*.txt"):  If V = False Then Exit Sub
        R = FreeFile
        Open V For Input As #R
        V = Split(Input(LOF(R), #R), vbCrLf)
        Close #R
        R = 6
    While Not IsEmpty(Cells(R, 2))
        W = Application.Match("Reading Part Numbers and IDs from: " & Cells(R, 2).Text, V, 0)
        If IsNumeric(W) Then X(0, 0) = Split(V(W + 1), D)(1): X(1, 0) = RTrim(Split(V(W + 3), D)(1))
        Cells(R + 2, 4).Resize(2) = X
        Erase X
        R = R + 5
    Wend
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[jamesrawlinson]

Apologies for the dual post.

Thanks for the code, very helpful.

I am trying to make it work in a different way, the text file I have supplied is much simpler than what I will actually use (I have simplified it / removed private info).

For the Macro to work it needs to:

1. Search for a specific line ''Reading Part Numbers and IDs from: PDM''
2. then it needs to pick the Software version and Calibration version from that section
3. then it will place it in the correct cells (which it already does)

this then needs to be repeated for each of the 4 sections (PDM, CBM, BBH, BBH2). This will then mean when the text file is much larger it will search for a specific line, then pick the Software version and Calibration version from that section.

See image:

Attachment 782319

That is the last iteration I need, sorry I'm really new to this and struggling through.

Thank again all - if you could help with this last iteration that would be great and I will continue to learn.

[Marc L]

As your last attachment is not valid …

I can't catch what you mean, just try my VBA demonstration with your post #6 attachment …

[jamesrawlinson]

It works very well, but only for that specific text file. I'll add the image as an attachment so you can see it correctly.

Your code works, but to work with the mode complext .txt file it needs to be edited to work as mentioned above.

Hopefully the image is attached.

[Marc L]

According to your post #6 attachment it is exactly what my VBA demonstration does …

So you must well elaborate your need with details in order there is nothing to guess as guessing can't be coding !
As proceeding like you did means you are enough confident with your Excel / VBA skills to fit any helper code
but if it's not the case you just wasted helpers time …

You can attach a workbook with a before state worksheet and an expected result worksheet
according to a 'sanitized' exact layout source text file …

[Marc L]

It works very well, but only for that specific text file.

As it also works with your initial post text file …

[jamesrawlinson]

Thank you. Certainly not trying to waste time, just trying to learn and make this work.

As request I have made a full file to see the issue. I have also attached two images to make the request clear.

The info in the text file that is required is right at the bottom of the text.

[Marc L]

Thanks for the rep' !

Your initial explanation is so far from your real need, to really avoid if you are not able to fit any helper code …
Accuracy is the only path to an efficient solution.

According to the last attachment another Excel / VBA basics demonstration where 'Clear Cells' button is useless :

Sub Demo2()
    Dim V, R&, W, S, X, Y, L&
        V = Application.GetOpenFilename("Text files,*.txt"):  If V = False Then Exit Sub
        R = FreeFile
        Open V For Input As #R
        V = Split(Input(LOF(R), #R), "Filter set OK for ECU: ")
        Close #R
        W = [{"--Software Version (JLR) * : *","--Calibration Version (JLR) * : *","--Veh Mfr Software Number * : *"}]
    For R = 6 To Cells(Rows.Count, 2).End(xlUp).Row
        With Cells(R, 2).CurrentRegion.Rows
           .Item("3:" & .Count).Columns(3).ClearContents
            S = .Cells(1) & vbCrLf & "*"
        For Each X In V
            If X Like S Then
                Y = Split(X, vbCrLf)
            For L = 3 To .Count
                    X = Application.Match(.Cells(L, 1), [{"SW Version ","Calibration Version ","Mfr SW Number"}], 0)
                If IsNumeric(X) Then
                    X = Application.Match(W(X), Y, 0)
                    If IsNumeric(X) Then .Cells(L, 3) = Split(Y(X - 1), ": ")(1)
                End If
            Next
                Exit For
            End If
        Next
            R = R + .Count
        End With
    Next
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄