vba 如何使用vba代码打开txt文件并将其内容复制到excel？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46642087/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to open a txt file with vba code and and copy its contents to excel?
提问by Divya
I need to open multiple txt files from same folder and copy its contents to a single excel file (like a template) to modify the values and then, I need to copy the modified values from excel to txt files. How can we do this VBA automation? any reply would be helpful.
我需要从同一文件夹中打开多个 txt 文件并将其内容复制到单个 excel 文件（如模板）以修改值，然后，我需要将修改后的值从 excel 复制到 txt 文件。我们如何进行这种 VBA 自动化？任何答复都会有所帮助。
回答by Teasel
You can open a file by using the following method (found there, adapt it!)
您可以使用以下方法打开文件（在那里找到，调整它！）
Sub OpenAndImportTxtFile()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import
Set wbO = Workbooks.Open("path and name of your file")
wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False
End Sub
And export your sheet with the following method (found there)
并使用以下方法导出您的工作表（在那里找到）
Sub SaveFile()
Dim ans As Long
Dim sSaveAsFilePath As String
On Error GoTo ErrHandler:
sSaveAsFilePath = "path and name of your file"
If Dir(sSaveAsFilePath) <> "" Then
ans = MsgBox("File " & sSaveAsFilePath & " exists. Overwrite?", vbYesNo + vbExclamation)
If ans <> vbYes Then
Exit Sub
Else
Kill sSaveAsFilePath
End If
End If
Sheet1.Copy '//Copy sheet to new workbook
ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextWindows '//Save as text (tab delimited) file
If ActiveWorkbook.Name <> ThisWorkbook.Name Then '//Double sure we don't close this workbook
ActiveWorkbook.Close False
End If
My_Exit:
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume My_Exit
End Sub
Call them with
打电话给他们
OpenAndImportTxtFile
SaveFile
回答by KarmaWin
There are pretty good guides I used to do the same work like you.
有很多很好的指南，我曾经像你一样做同样的工作。
For Text to Excel:
对于文本到 Excel：
http://www.excel-easy.com/vba/examples/read-data-from-text-file.html
http://www.excel-easy.com/vba/examples/read-data-from-text-file.html
vba: Importing text file into excel sheet
For Excel to Text:
对于 Excel 到文本：
http://www.excel-easy.com/vba/examples/write-data-to-text-file.html
http://www.excel-easy.com/vba/examples/write-data-to-text-file.html
Good luck
祝你好运
回答by ASH
It sounds like you want to merge all text files into one single file. How about this option?
听起来您想将所有文本文件合并为一个文件。这个选项怎么样？
Sub CombineTextFiles()
Dim lFile As Long
Dim sFile As String
Dim vNewFile As Variant
Dim sPath As String
Dim sTxt As String
Dim sLine As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show Then
sPath = .SelectedItems(1)
If Right(sPath, 1) <> Application.PathSeparator Then
sPath = sPath & Application.PathSeparator
End If
Else
'Path cancelled, exit
Exit Sub
End If
End With
vNewFile = Application.GetSaveAsFilename("CombinedFile.txt", "Text files (*.txt), *.txt", , "Please enter the combined filename.")
If TypeName(vNewFile) = "Boolean" Then Exit Sub
sFile = Dir(sPath & "*.txt")
Do While Len(sFile) > 0
lFile = FreeFile
Open CStr(sFile) For Input As #lFile
Do Until EOF(lFile)
Line Input #1, sLine
sTxt = sTxt & vbNewLine & sLine
Loop
Close lFile
sFile = Dir()
Loop
lFile = FreeFile
Open CStr(vNewFile) For Output As #lFile
Print #lFile, sTxt
Close lFile
End Sub
