CODEWITHSUNDEEP
vbaexcel
Synectouche
Synectouche

Reputation: 173

VBA FilesystemObject

I am having a problem with this function in VBA that saves certain columns to text file. It only saves the values of Column B, and it ignores the another for loop wherein I loop in the values of the Column C. I tried to add a direct writing to file -> objTextStream.writeline ("COPY THIS STRING") just to see where's the problem but still COPY THIS STRING still wasn't saved in the text file.

What's seems to be the problem. Thanks in advance guys.

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

Upvotes: 0

Views: 781

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

Your issue is in the line

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

It is creating a larger range than you expect.

For example, if there are say 100 rows of data in columns A and you call your sub with toRange = 100, you will get MyRange = B1:B100100. This will print a whole lot of blank rows to your text file!

I think you want just

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

and similarly for MyRange2

Or, don't bother passing toRange, and use

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

Upvotes: 1

Related Questions