Copy text files from text file to Worksheet With VBA

[Abhishekghorpade]

I have multiple text files saved in the Path “C:\Users\E5554593\Desktop\New folder”. I need to import the data from text files to excel. Each file data should come in separate tabs, Tab name will be same as text file name.
Can anyone help me out with this please.. Its consuming lot of time

 

[Abhishekghorpade]

Its working sorry my bad... Thank you so much for your time and help

 

[GR00007]

Please run in debug. Open the macro, and click F8 and step through each line of code. You can hover the mouse over variables to see their value after a step is executed note where an error occurs.
Since it's doing one file it's probably getting to the loop, make sure the file name changes in MyFile which is just prior to the loop. Also check vTabName - it's stripping off ".txt" prior to changing the name of a new tab.
If you already have an existing tab name and it tries to create a new one there will be an error, there was no error routine here - would have to know how you want to handle them, if a tab name exists data to bottom of existing? All sorts of questions on that.

 

[GR00007]

Its working sorry my bad... Thank you so much for your time and help

Great, I'm glad it's working.

 

[Abhishekghorpade]

is there a way to delete the rows that contains text like "PAGE", "GLOBAL PLAN"

 

[GR00007]

You can add a filter to exclude those values - here's the code revised to filter column A so it does not include rows that have PAGE or GLOBAL PLAN in column A:

Sub Macro1()
'   HouseKeeping
    MyPath = "%USERPROFILE%\Desktop\New Folder\"
    MyExt = "*.txt"
    ThisBook = ActiveWorkbook.Name
    MyFile = Dir(MyPath & MyExt)
    Do While MyFile <> ""
        Workbooks.OpenText Filename:=MyPath & MyFile, Origin:=437, _
            StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(14 _
            , 1), Array(33, 1), Array(50, 1), Array(75, 1), Array(82, 1), Array(86, 1)), _
            TrailingMinusNumbers:=True
        Cells.Select
        Selection.Columns.AutoFit
        vTabName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
        vLR = ActiveSheet.UsedRange.Rows.Count
        Selection.AutoFilter
        ActiveSheet.Range("A1:G" & vLR).AutoFilter Field:=1, Criteria1:= _
            "<>*Page*", Operator:=xlAnd, Criteria2:="<>*Global*"
        Range("A1:G" & vLR).Select
        Selection.Copy
        Windows(ThisBook).Activate
        Sheets.Add After:=ActiveSheet
        vx = ActiveSheet.Name
        Sheets(vx).Name = vTabName
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Windows(vTabName & ".txt").Activate
        Application.DisplayAlerts = False
        ActiveWindow.Close savechanges:=False
        Application.DisplayAlerts = True
        MyFile = Dir
    Loop
End Sub

 

[Abhishekghorpade]

Thank you so much its working as expected. I have on more concern, This report is going to be used by many people and chance of spoiling the report is high.. Can you make it as read only/password protected.. So that no one can edit or see the coding yet still use it

 

[GR00007]

Check out this thread:
https://www.mrexcel.com/forum/excel...sword-protect.html?highlight=password+protect