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声明的，我在文件中写了数百行，很少会截断行——也就是说，结束被截断。
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?
编辑:我刚刚意识到总是最后几行被截断。所以我想我需要关闭或刷新文件?
4 个解决方案
#1
15
You can use Close #fnum1 to close the file handle and it should flush the remaining buffer contents.
您可以使用Close #fnum1关闭文件句柄，它应该刷新剩余的缓冲区内容。
#2
4
Yes, you should be closing the files with the Close method. 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 here for 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(文件系统对象)可能是值得的，我认为它最初是为了让VBScript进行文件处理，但是我更喜欢在文件处理中构建VB6s和VBAs。在这里可以看到更多的细节(这里有一个大的示例展示如何在其中一个页面中完成大多数您需要的事情)。
#3
1
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.
不确定这会不会给你更好的结果(在性能和/或格式方面)，但也许值得一试。
#4
1
Just a necro solution: I have found the Close #1 method to still leave a truncated file. Instead, or in addition to, use the Application.Quit for Excel to close Excel. This flushes the cache and completes the write to the text file.
只有一个necro解决方案:我找到了Close #1方法，仍然保留一个被截断的文件。相反，或者除了使用应用程序之外。退出Excel关闭Excel。这将刷新缓存并完成对文本文件的写入。
#1
15
You can use Close #fnum1 to close the file handle and it should flush the remaining buffer contents.
您可以使用Close #fnum1关闭文件句柄，它应该刷新剩余的缓冲区内容。
#2
4
Yes, you should be closing the files with the Close method. 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 here for 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(文件系统对象)可能是值得的，我认为它最初是为了让VBScript进行文件处理，但是我更喜欢在文件处理中构建VB6s和VBAs。在这里可以看到更多的细节(这里有一个大的示例展示如何在其中一个页面中完成大多数您需要的事情)。
#3
1
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.
不确定这会不会给你更好的结果(在性能和/或格式方面)，但也许值得一试。
#4
1
Just a necro solution: I have found the Close #1 method to still leave a truncated file. Instead, or in addition to, use the Application.Quit for Excel to close Excel. This flushes the cache and completes the write to the text file.
只有一个necro解决方案:我找到了Close #1方法，仍然保留一个被截断的文件。相反，或者除了使用应用程序之外。退出Excel关闭Excel。这将刷新缓存并完成对文本文件的写入。