Macro to copy daily text file

[Guest]

I have a different text file (same format) to be copied into a spreadsheet daily.
Eg Day 1 text file to be copied into sheet D1, Day 2 text file to be copied into sheet D2. What is the fastest way?
I tried using macro copy (with relative reference) - text file in same sheet overriding on daily basis. It does not work.Appreciate all help

 

[mudraker]

Try this for starters

Note This does not test to see if sheet name already exists
You will also need to modify the
Workbooks.OpenText
statement to suit your needs


Dim wsNew As Worksheets
Set wsNew = Sheets.Add
wsNew.Name = "D" & Day(Now())

Workbooks.OpenText FileName:="D:\My Documents\Book2.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1)

ActiveWorkbook.Sheets(1).Cells.Copy Destination:=wsNew.Cells
ActiveWorkbook.Close savechanges:=Fals