Solved Export Column to Text File Issues (1 Viewer)

Kheribus

Registered User.
Local time
Today, 07:19
Joined
Mar 30, 2015
Messages
97
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!

Code:
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

Lifelong Learner
Local time
Today, 07:19
Joined
Mar 14, 2017
Messages
2,769
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?
 
Last edited:

Kheribus

Registered User.
Local time
Today, 07:19
Joined
Mar 30, 2015
Messages
97
Good question. Sometimes the cells are blank.
 

Kheribus

Registered User.
Local time
Today, 07:19
Joined
Mar 30, 2015
Messages
97
Yes. All I'm trying to do is copy the data in column F to the data.txt file
 

Kheribus

Registered User.
Local time
Today, 07:19
Joined
Mar 30, 2015
Messages
97
This worked:

Code:
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

error reading drive A:
Local time
Today, 23:19
Joined
May 7, 2009
Messages
10,871
you missed 1 dot:
Code:
..
..
With ThisWorkbook.Sheets(NameOfTheSheet)
    lRow = .Cells(Rows.Count, "F").End(xlUp).Row    'arnelgp, see the Point on .Cells

..
..
 

Users who are viewing this thread

Top Bottom