如何从 Excel VBA 可靠地写入文本文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3085615/
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 can I write to a text file reliably from Excel VBA?
提问by HenricAbsolom
I'm trying to use Excel VBA to write to a text file. I'm doing a few of these:
我正在尝试使用 Excel VBA 写入文本文件。我正在做其中一些：
MyFile1 = "C:\outputFromExcel1.txt"
fnum1 = FreeFile()
Open MyFile1 For Output As fnum1
and then writing to them like this:
然后像这样写信给他们：
Print #fnum1, text
All variables in the above are declared just with Dim. I'm writing hundreds of lines to the files and, very rarely, lines are being truncated -- i.e. the ends are being chopped off. Is there a better way to write to a file in Excel VBA?
上面的所有变量都只用Dim. 我正在向文件中写入数百行，而且很少有行被截断——即末端被切断。有没有更好的方法在 Excel VBA 中写入文件？
EDIT:I've just realized that it's always the last lines to be written that are truncated. So I guess I need to close or flush the files somehow?
编辑：我刚刚意识到它总是被截断的最后几行。所以我想我需要以某种方式关闭或刷新文件？
回答by Ben Hoffstein
You can use Close #fnum1to close the file handle and it should flush the remaining buffer contents.
您可以使用Close #fnum1关闭文件句柄，它应该刷新剩余的缓冲区内容。
回答by Hans Olsson
Yes, you should be closing the files with the Closemethod. I'm not sure if that's what causing the problems but you should be doing that either way.
是的，您应该使用该Close方法关闭文件。我不确定这是否是导致问题的原因，但无论哪种方式，您都应该这样做。
If you're doing a lot of filehandling in your VBA code it might be worth looking at using FSO (FileSystemObject), I think it was originally for letting VBScript do file processing, but I prefer it to both VB6s and VBAs built in file handling. See herefor more details (and there's a big sample showing off how to do most things you need in one of those pages as well).
如果您在 VBA 代码中进行大量文件处理，则可能值得考虑使用 FSO (FileSystemObject)，我认为它最初是为了让 VBScript 进行文件处理，但与 VB6 和 VBA 内置的文件处理相比，我更喜欢它. 有关更多详细信息，请参阅此处（并且有一个大样本展示了如何在其中一个页面中完成您需要的大多数事情）。
回答by Jim Snyder
Just a necro solution: I have found the Close #1method to still leave a truncated file. Instead, or in addition to, use the Application.Quitfor Excel to close Excel. This flushes the cache and completes the write to the text file.
只是一个死灵解决方案：我找到了Close #1仍然保留截断文件的方法。作为替代或补充，使用Application.Quitfor Excel 关闭 Excel。这将刷新缓存并完成对文本文件的写入。
回答by Ben Hoffstein
Have you considered writing the text to a different sheet (or a different workbook) and then using:
您是否考虑过将文本写入不同的工作表（或不同的工作簿），然后使用：
ActiveWorkbook.SaveAs Filename:="C:\MyFile.txt", FileFormat:=xlText
Not sure if this would give you better results (in terms of performance and/or formatting), but perhaps worth a try.
不确定这是否会给您带来更好的结果（在性能和/或格式方面），但也许值得一试。
