Unable to copy and paste a text file into excel

**Jack40** · 07-22-2012, 10:44 PM

Hi all,

i am trying to open multiple text files, copy and paste in single excel sheet ( one by one). i am use below code it open all the text file into excel but unable to copy and paste in single excel sheet. can any one help..

Sub loopyarray()

   Dim filenames As Variant

   ' set the array to a variable and the True is for multi-select
   filenames = Application.GetOpenFilename(, , , , True)

      counter = 1

      ' ubound determines how many items in the array
      While counter <= UBound(filenames)

         'Opens the selected files
         Workbooks.Open filenames(counter)

         ' displays file name in a message box
         MsgBox filenames(counter)

         'increment counter
         counter = counter + 1
         

      Wend
   End Sub

Thanks

Jack

**GaidenFocus** · 07-22-2012, 11:28 PM

I always have to convert TXT into XLS before i can import it into excel.

name file.txt as file.xls

**arlu1201** · 07-23-2012, 03:53 AM

Jack40,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

**Jack40** · 07-23-2012, 10:53 PM

Hi all,

Here is the code for copy data from mulitple text file and paste in single sheet..

Sub Button1_Click()
   Dim filenames As Variant
   Application.DisplayAlerts = False
   ' set the array to a variable and the True is for multi-select
   filenames = Application.GetOpenFilename(, , , , True)
   counter = 1
' ubound determines how many items in the array
      While counter <= UBound(filenames)

       'Opens the selected files
         Workbooks.Open filenames(counter)
         ActiveWindow.Activate
         Range("A1").Select
         Range(Selection, Selection.End(xlDown)).Select
         Selection.Copy
         Windows("cONVERT.xlsm").Activate
         Sheets("Report").Select
         ActiveSheet.Paste
         Range("A1").Select
         Selection.End(xlDown).Select
         ActiveCell.Offset(1, 0).Activate
         ActiveWindow.ActivateNext
         ActiveWindow.Close
         Application.DisplayAlerts = False
         ' displays file name in a message box
         'MsgBox filenames(counter)

         'increment counter
         counter = counter + 1
     Wend
   End Sub

Regards

Jack