Error Message - Text file opened on Excel

[lxd]

My intention is to open a txt file in Excel, do text-to-column separation, then copy the data and paste on a template workbook the macro opens. The following macro does well upto parsing the data, but fails at copying the data. I get error message when it gets to that point. Two questions, how can I (i). successfully copy the data and paste it on the template, and (ii). close the first file (source file) without saving it.

I am using Excel 2007 and the macro is on the template. Thanks.

The code I have been using:

Private Sub OpenFile()
ChDir "C:\Documents and Settings\Desta\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Sun\Desktop\Drums.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:=">", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)),TrailingMinusNumbers:=True

Cells.Select
Selection.Copy

Workbooks.Open Filename:= _
"C:\Documents and Settings\Sun\Desktop\Drums Template.xls", Origin:= xlWindows

ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False

Windows("Drums.xls").Close

End Sub

 

[Blade Hunter]

Private Sub OpenFile()
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Sun\Desktop\Drums.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, _
Space:=True, Other:=True, OtherChar:=">", FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1)), TrailingMinusNumbers:=True
Cells.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\Sun\Desktop\Drums Template.xls", Origin:=xlWindows
'How do you know that the correct area will be selected for pasting?
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Workbooks("Drums.txt").Close False 'I changed this to txt as I presume it is the text file you want to close
End Sub

If you tell us what the error message is, it would really help us in helping you.

Cheers

Dan<!-- / message -->

 

[lxd]

It worked perfectly. Many thanks!