VBA FilesystemObject

Question

在将某些列保存到文本文件的VBA中，这个函数有问题。它只保存B列的值，它忽略另一个for循环，其中我在列C的值中执行循环。我尝试添加一个直接写入文件-> objTextStream.writeline (“复制此字符串”)，只是为了查看问题出在哪里，但仍然复制该字符串仍未保存在文本文件中。

似乎是问题所在。提前谢谢各位。

Sub SaveToTxt(toRange As Integer)

Dim MyRange As Range
Dim MyRange2 As Range
Dim objFileSyatem As Object, objTextStream As Object
Dim cell As Range


Set MyRange = ActiveSheet.Range _
("B1:B" + CStr(toRange) & Cells(Rows.Count, 1).End(xlUp).Row)

Set MyRange2 = ActiveSheet.Range _
("C1:C" + CStr(toRange) & Cells(Rows.Count, 1).End(xlUp).Row)


'create filesystem and textstream objects, the name of the .txt file
'is one of parameters of CreateTextFile method
Set objFileSyatem = CreateObject( _
"Scripting.FileSystemObject")
Set objTextStream = objFileSyatem.CreateTextFile( _
"C:\Users\wmeniola\work work work\Ranges deletion\test.txt", True)

'loop through all cells in MyRange and save contents as
'separate lines in newly created .txt file
For Each cell In MyRange.Cells
objTextStream.writeline (cell.Value)
Next

For Each cell In MyRange2.Cells
objTextStream.writeline (cell.Value)
Next


objTextStream.writeline ("COPY THIS STRING")
objTextStream.Close

End Sub

Answer 1

你的问题已成定局

Set MyRange = ActiveSheet.Range _
  ("B1:B" + CStr(toRange) & Cells(Rows.Count, 1).End(xlUp).Row)

它正在创造一个比你预期的更大的范围。

例如，如果A列中有100行数据，并且您使用toRange = 100调用您的sub，那么您将得到MyRange = B1:B100100。这将打印大量空白行到您的文本文件！

我觉得你想

Set MyRange = ActiveSheet.Range("B1:B" & toRange)

MyRange2也是如此

或者，不要费心地传递toRange，然后使用

With ActiveSheet
    Set MyRange = .Range("B1:B" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With