Solved Export Column to Text File Issues (1 Viewer)

[Kheribus]

Hi,

I have the following sub that exports a column (F) from an Excel file to a text file named data.txt. This has been working, but my data set has grown and for some reason this routine does not copy all of the data from column F, stopping at 43,020 lines. I have about 180k lines that need to be pasted into the text file (they are addresses).

Does anyone have any idea why I can't get all of the data in the text file? The file size is about 720Kb - so there should be plenty of space left. Thanks in advance!

Sub exprtToText2()
Dim ff As Long, lRow As Long

' name of your sheet, you may change it if needed
Const NameOfTheSheet As String = "Sheet1"

' update to the folder path, make sure it ends with \
Const sTextTileDirectoryPath As String = "c:\mypathtoproject\redacted"

ff = VBA.FreeFile

With ThisWorkbook.Sheets(NameOfTheSheet)
    lRow = Cells(Rows.Count, "F").End(xlUp).row
        
    Open sTextTileDirectoryPath & .Range("F1").Value & "data.txt" For Output Access Write As #ff
        Print #ff, Join(Evaluate(Replace(Replace("transpose(#F2:F@)", "#", "'" & NameOfTheSheet & "'!"), "@", lRow)), vbNewLine)
    Close #ff
End With
End Sub

 

[Isaac]

Can you explain what this line is doing?
Join(Evaluate(Replace(Replace("transpose(#F2:F@)", "#", "'" & NameOfTheSheet & "'!"), "@", lRow))

Can you explain the goal in simple terms? Is it simply to write every cell value in F that has data, to separate lines in the text file? Is that all?

 

[Kheribus]

Good question. Sometimes the cells are blank.

 

[Kheribus]

Yes. All I'm trying to do is copy the data in column F to the data.txt file

 

[Kheribus]

This worked:

Sub exportTextNoob()

    Dim iCntr As Long
    Dim strFile_Path As String
    strFile_Path = "C:\myfilepath"

    Open strFile_Path For Output As #1
    For iCntr = 1 To Cells(Rows.Count, "F").End(xlUp).row
       Print #1, Range("F" & iCntr)
    Next iCntr
    Close #1
End Sub

 

[arnelgp]

you missed 1 dot:

..
..
With ThisWorkbook.Sheets(NameOfTheSheet)
    lRow = .Cells(Rows.Count, "F").End(xlUp).Row    'arnelgp, see the Point on .Cells

..
..