Copy from text file to Excel and replace

[Thomas Kuriakose]

Respected Sirs,

We have a text report which is downloaded from a source and this needs to be converted to excel and then data needs to be analyzed.

Currently we are doing this manually after opening excel , selecting all files (All Files *.*) and then text to columns, delimiter, to get the data in excel.

Kindly let me know if this can be automated with two options -

1. A vba code which will prompt to select text file and then copy the data to excel date wise per sheet. Each date report to be copied to new sheet
2. A vba code which will prompt to select text file and then replace the contents of the exiting sheet.

Kindly find attached the text file and excel file for your reference.

Thank you very much,

with regards,
thomas

 

[Marc L]

Hi !

Save the code workbook before to run this demonstration :​

Sub Macro1and2()
'
' Macro1 Macro
' Macro recorded the 30/08/2016 by The Noob Simulator !
'
With ThisWorkbook:  ChDrive .Path:  ChDir .Path:  End With
       V = Application.GetOpenFilename("Text Files, *.txt", , "  Import a text file :")
    If V = False Then Exit Sub
       W = Split(V, Application.PathSeparator)
       W = Split(W(UBound(W)), ".")(0)
If Evaluate("ISREF('" & W & "'!A1)") Then With Worksheets(W): .UsedRange.Clear: .Activate: End With _
                                     Else Worksheets.Add(, Worksheets(Worksheets.Count)).Name = W
    With ActiveSheet.QueryTables.Add("TEXT;" & V, [A1])
                   .RefreshStyle = xlOverwriteCells
        .TextFileColumnDataTypes = Array(2, 2)
               .TextFilePlatform = xlWindows
           .TextFileTabDelimiter = True
          .TextFileTextQualifier = xlTextQualifierNone
        .Refresh False:  .Delete
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided on this.

Much appreciated,

with regards,
Thomas