krishna334
Active Member
- Joined
- May 22, 2009
- Messages
- 391
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>
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>
Last edited: