Copy multiple text files to different sheet of the same Excel file.

To meet this requirement, You need to copy multiple files to different sheet of the same Excel file. Each text file has one sheet, and the sheet name uses the name of the text file, using VBA can easily implement this function, but it is still a little unfamiliar with the VB syntax and is backed up here.

 Sub  Importtextfiles ()  ''  Import text files to a Excel file.  '  '  Dim  Filepath, filenamefilepath = "  D: \ items \  "  Filename = Dir (Filepath +"  *. Txt  "  )  Do   While Filename <> ""  Sheets. Add after: = Worksheets (worksheets. Count)  With Activesheet. querytables. Add (connection: = "  Text;  " & Filepath & Filename _, destination: = Range ("  $ A $1  "  ). Fieldnames = True  . Rownumbers = False  . Filladjacentformulas = False  . Preserveformatting = True  . Refreshonfileopen = False  . Refreshstyle =Xlinsertdeletecells. savepassword = False  . Savedata = True  . Adjustcolumnwidth = True  . Refreshperiod = 0  . Textfilepromptonrefresh = False  . Textfileplatform = 936  . Textfilestartrow =1  . Textfileparsetype = Xldelimited. textfiletextqualifier = Xltextqualifierdoublequote. textfileconsecutivedelimiter = True  . Textfiletabdelimiter = True  . Textfilesemicolondelimiter = False  . Textfilecommadelimiter = False  . Textfilespacedelimiter = True  . Textfilecolumndatatypes = Array ( 2 , 1 , 2 , 1  ). Textfiletrailingminusnumbers = True  . Refresh backgroundquery: = False      End   With  Range (  " A1: d1  "  ). Select selection. Font. Bold = True  Activesheet. Name = Left (Filename, Len (Filename )- 4  ) Filename = Dir  ()  Loop  End sub