macro to extract name of imported file

[howard]

I would like a macro that will extract the name of the imported file in sheet1 K2

Your assistance in this regard is most appreciated

 

[Trevor G]

How are you importing the text file is it through the Data Tab if so you can use something like this if it is a Data Query

Sub GetFileName3()
Dim sFileName as String
sFileName = activecell.QueryTable.Connection
sFileName = Right(sFileName, Len(sFileName)-5)
Sheets("sheet1").Range("K2").Value= sFileName
End Sub

Perhaps this site maybe some help to you Excel: Importing text files into Excel sheets

 

[howard]

Hi Trevor

Thanks for the reply. The file is downloaded into a directory called "C:\downloads" and a macro is used to copy and paste the data from this text file into sheet1 in the destination workbook