VBA Import text file to excel with desired result

[roykana]

Dear All Master,
I want to import from text file to excel and want the desired result as below and for column H I mark yellow then the result must be text.
I also attached a text file link
thanks
roykana

DESIRED RESULT

VBA IMPORT TEXT FILE.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	No Transaction	Date	Dept.	Code Pel.	Name Customer	Address	No.	Cd. Item	Name Item	Qty	Unit	Price	Pot. %	Total	Pot. :	Tax :	Costs :	Total End :
2	0002/KSR/TK/1221	19-12-21		GENERAL	GENERAL		1	410288	TAMAKA R 410288 RC/L-TOP	1	PCS	115000	0	115000	40000	0	0	800000
3	0002/KSR/TK/1221	19-12-21		GENERAL	GENERAL		2	829740	TAMAKA R 829740 RC/L-TOP	2	PCS	90000	0	180000
4	0002/KSR/TK/1221	19-12-21		GENERAL	GENERAL		3	410240	TAMAKA R 410240 RC/L-TOP	1	PCS	125000	0	125000
5	0002/KSR/TK/1221	19-12-21		GENERAL	GENERAL		4	56117	ALFIN TRAVEL 56117 D1680 TG	1	PCS	105000	0	105000
6	0002/KSR/TK/1221	19-12-21		GENERAL	GENERAL		5	222445	TAMAKA R 222445 RC/L-TOP/USB	1	PCS	155000	0	155000
7	0002/KSR/TK/1221	19-12-21		GENERAL	GENERAL		6	111195	TAMAKA R 111195 RC/L-TOP/USB	1	PCS	160000	0	160000
8	0003/KSR/TK/1221	19-12-21		GENERAL	GENERAL		1	2019	ALFIN WB 2019 BATIK	1	PCS	35000	0	35000	0	0	0	35000
MASTER

Option Explicit

Sub Importtextfile()
    '// Declare a variable as
    Dim nRow            As Long
    Dim sExtension      As String
    Dim oFolder         As FileDialog '// FileDialog object
    Dim vSelectedItem   As Variant
    Dim wsSelect        As Worksheet
    '// Stop Screen Flickering
    Application.ScreenUpdating = False

    '// Create a FileDialog object as a File Picker dialog box
    Set oFolder = Application.FileDialog(msoFileDialogOpen)
    Set wsSelect = Sheets("MASTER")

    '// Use a With...End With block to reference FileDialog.
    With oFolder
        '// Allow multiple selection.
        .AllowMultiSelect = True
        '// Use the Show method to display the files.
        If .Show = -1 Then

    '// Extension
    sExtension = Dir("*.txt")

    '// Step through each SelectedItems
    For Each vSelectedItem In .SelectedItems

        '// Sets Row Number for Data to Begin
        nRow = Range("A1").End(xlUp).Offset(1, 0).Row
With wsSelect.Range("A1").CurrentRegion.Clear
End With
        '// Below is importing a text file
        With wsSelect.QueryTables.Add(Connection:= _
            "TEXT;" & sExtension, Destination:=Range("$A$" & nRow))
            .Name = sExtension
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = ""
            .TextFileTrailingMinusNumbers = False
            .Refresh BackgroundQuery:=False
        End With
        sExtension = Dir
    Next
            '// If Cancel...
            Else
            End If
    End With

    Application.ScreenUpdating = True

    '// Set object to Nothing. Object? see Link Object
    Set oFolder = Nothing
End Sub

link text file

 

[roykana]

I should not comment it if you are happy with it (even if you use useless things)…​

​

According to your post #13 where the table already exists - so no need to create it ! - the combination of my post #6 VBA procedure​

with my post #16 well works on my side …​

@Marc L
yes it is true that before the table already exists then I eliminate the table and also I add columns so that's why I use the method of creating the table. But what's important to me is that the code you give you goes perfectly.

 

[Marc L]

​

As the table already exists you just need to resize it rather than eliminate it and recreate it …​

 

[roykana]

​

As the table already exists you just need to resize it rather than eliminate it and recreate it …​

@Marc L
okay I follow your advice and from the code I posted #19 please you can fix it so that it becomes the end result of the solution code and also importantly I can in the future I can add columns not to be difficult

 

[Marc L]

First clear the data range combining UsedRange & Offset or just via the ListObject DataBodyRange property​

then add the data whatever rows and / or columns then just resize the table according to the whole range …​

 

[roykana]

​

First clear the data range combining UsedRange & Offset or just via the ListObject DataBodyRange property​

then add the data whatever rows and / or columns then just resize the table according to the whole range …​

@Marc L
Running perfectly as you recommend.
You are indeed very best and
I have the latest maybe you can help me .

vlookup dictionary approximate match

Dear all Master, below I want the desired result in column E which I marked in yellow and also I use a formula so it's easy to understand what I mean and I want to add a little in the code below without changing the structure of the code below and I also made a vba code for in column E in sheet...

www.mrexcel.com

thanks
roykana