CODEWITHSUNDEEP
vbaexcel
PhilB
PhilB

Reputation: 23

Error type mismatch - How do I create a .txt file from all of the data in column A sheet 1

I keep getting the type mismatch error on this line of code.

stream.WriteLine CellData

Full code below

Sub Write_to_File()

Dim FilePath As String
Dim CellData As Variant
Dim LastCol As Long
Dim LastRow As Long


Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim stream As TextStream




' Create a TextStream.
Set stream = fso.OpenTextFile("C:\...\test_labs.txt", ForWriting, True)

var3 = Worksheets("label").Columns(1)

CellData = ""

     CellData = var3
     stream.WriteLine CellData

stream.Close
MsgBox ("Done")

End Sub

I am trying to get all of column A in sheet 1 to write to the text file. This code is run from a command button in sheet 2. I'm still new to VBA so any help is appreciated.

Thanks

Upvotes: 2

Views: 358

Answers (1)

CLR
CLR

Reputation: 12289

The following would work, but I wouldn't recommend you dump the entire column into a file, it may take some time. Better to limit it to non-empty lines, or perhaps find the last used cell and only output up to that point?

' Create a TextStream.
Set stream = fso.OpenTextFile("K:\test_labs.txt", ForWriting, True)

For Each CellData In Worksheets("label").Columns(1).Cells
     stream.WriteLine CellData
Next
stream.Close
MsgBox ("Done")
End Sub

The following section would look for the last row populated in column A (stores it in your LastRow variable that you already declared) and stops there:

' Create a TextStream.
Set stream = fso.OpenTextFile("K:\test_labs.txt", ForWriting, True)
With Worksheets("label")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Each CellData In .Range("A1:A" & LastRow).Cells
         stream.WriteLine CellData
    Next
End With
stream.Close
MsgBox ("Done")
End Sub

For information: the reason that you were getting the error in the first place was because stream.WriteLine expects a scalar String input argument, but this line:

var3 = Worksheets("label").Columns(1)

sets var3 to an array.

Upvotes: 2

Related Questions