Error while copying extracted text file data

[krishna334]

Hi All,
I am trying to develop a code which extracts the data from text files inside a folder (Folder test in my desktop) into one sheet.
The Macro is in the workbook “Text Extract” which is an excel 2007 file. The data of each text should be copied to Sheet1 of this workbook one below the other.
For testing purpose I have kept only one text file in the folder and was trying to copy the data from the text data extracted sheet to Cell A1 of Sheet1 of workbook “Text Extract”.
The code works fine till copying the data, but shows below error in the line “Selection.Paste”:
Run time error 438: Object doesn’t support this property or method.
Can someone help me out.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Below is the code:
Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer
Dim k As Integer

strPath = "C:\Users\kkumar\Desktop\test\"
strFile = Dir(strPath)

While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend

'List filenames in Column A of the active sheet
If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
Workbooks.OpenText Filename:="C:\Users\kkumar\Desktop\test\" & colFiles(i), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array _
(14, 2), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:=True

ActiveSheet.UsedRange.Copy
Windows("Text Extract.xlsm").Activate
Range("a1").Select
Selection.Paste
Next i

End If

End Sub
<o></o>

 

[VoG]

Try

Selection.PasteSpecial Paste:=xlPasteAll

 

[krishna334]

Wow!! Thanks VOG..it worked..
by the way why was selection.paste not working?

 

[VoG]

Selection doesn't have a Paste method but it does have a PasteSpecial method.

 

[krishna334]

Thanks VoG. I got it.

Now, using your inputs i have made it loop though all the text files and also i have coded it to close the sheet from which the data is being copied but there is always a pop up window in which i need to select "No" option to move on to the text file.
How do i code so that selecting No is also automated Or how can i code so that this pop up does not appear?

Below is the pop up message:

"There is a large amount of information on the clipboard. do you want to be able to paste the information into another program later?"

In the options i ve yes and no and i need to select NO.


Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer
Dim k As Integer

strPath = "C:\Users\kkumar\Desktop\test\"
strFile = Dir(strPath)

While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend

'List filenames in Column A of the active sheet
If colFiles.Count > 0 Then

For i = 1 To colFiles.Count


Workbooks.OpenText Filename:="C:\Users\kkumar\Desktop\test\" & colFiles(i), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array _
(14, 2), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:=True

ActiveSheet.UsedRange.Copy
Windows("Text Extract.xlsm").Activate
k = ActiveSheet.UsedRange.Rows.Count + 1
Range("a" & k).Select
Selection.PasteSpecial Paste:=xlPasteAll
Windows("" & colFiles(i)).Close
Next i

End If

End Sub

 

[VoG]

If you clear the clipboard you should not see that message. Try

Selection.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

 

[krishna334]

Thanks Again Vog!!
Now the macro is complete.