CODEWITHSUNDEEP
excelvba
ghatotkach
ghatotkach

Reputation: 3

Copy a text file and paste in excel

I was trying to copy a text file and paste the data in excel using the following code. The code works satisfactorily to the extent copying and pasting data in the destination excel sheet, but additionally, opens up another excel file and sheet with the same name as the Text file, without being prompted anywhere in the code, and pastes the data there as well. This is not desirable. I don't want to split the data to columns or perform any other actions. It is a plain and simple copy and paste task. I have searched this and various other websites for an answer but failed to get one that appropriately addresses my problem. I am unable to figure out the flaw in the code, and therefore, seek your help. Any help would be most thankfully acknowledged.

Here is my code: 

Sub CopyTextFile()

Set TxtFileName = Workbooks.Open("D:\Spares\Inventory\list_of_spares.txt")
TxtFileName.Sheets(1).Range("A1").CurrentRegion.Copy
Workbooks("Macro Test.xlsm").Activate
ActiveWorkbook.Sheets(1).Range("A1").Select
ActiveSheet.Paste

End Sub

Upvotes: 0

Views: 26193

Answers (2)

Bhushan Raderam
Bhushan Raderam

Reputation: 1

I found a way to make your code work, you had to close the second workbook that was open, your code helped me thats why i am pointing out what was missing. 

Sub CopyTextFile()

Set TxtFileName = Workbooks.Open("D:\Spares\Inventory\list_of_spares.txt")
TxtFileName.Sheets(1).Range("A1").CurrentRegion.Copy
Workbooks("Macro Test.xlsm").Activate
ActiveWorkbook.Sheets(1).Range("A1").Select
ActiveSheet.Paste

Workbooks(2).Close

End Sub

If you mention Workbooks(1).Close, it would close the first opened excel, here we want to close the second workbook hence Workbooks(2).Close

I found some excel vba code on this link which helped:https://www.excel-easy.com/vba/examples/close-open.html

Upvotes: 0

Dave
Dave

Reputation: 4356

You're getting the "extra file" because you are opening the text file in Excel (with the Workbooks.Open statement) and then copying the data from it.

Instead, open the file with a filesystemobject and read the data, then write it directly into your workbook:

Sub CopyTextFile()
Dim oFso : Set oFso = CreateObject("Scripting.FileSystemObject")
Dim oFile : Set oFile = oFso.OpenTextFile("D:\Spares\Inventory\list_of_spares.txt", 1)
Dim sText
sText = oFile.ReadAll
oFile.Close
ThisWorkbook.Sheets(1).Range("A1").Value = sText
End Sub

See how that works for you?

Upvotes: 4

Related Questions