复制文本文件并粘贴到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. With warm regards,

我试图复制文本文件并使用以下代码将数据粘贴到excel中。代码在目标Excel工作表中复制和粘贴数据的程度令人满意,但另外,打开另一个与文本文件同名的excel文件和工作表,而不会在代码中的任何位置提示,并将数据粘贴到那里好。这是不可取的。我不想将数据拆分为列或执行任何其他操作。这是一个简单而简单的复制和粘贴任务。我搜索了这个和其他各种网站的答案,但未能得到一个适当解决我的问题。我无法弄清楚代码中的缺陷,因此,寻求你的帮助。任何帮助都将是最值得感谢的。温暖的问候,

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

1 个解决方案

#1

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.

您正在获取“额外文件”,因为您在Excel中打开文本文件(使用Workbooks.Open语句),然后从中复制数据。

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

而是使用filesystemobject打开文件并读取数据,然后将其直接写入工作簿:

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?

看看它对你有什么用?

#1