CODEWITHSUNDEEP
excelvbatext-files
James Fenix
James Fenix

Reputation: 27

Adding data from a specific column in Excel Workbook to a text file

I was wondering if there's a way in Excel VBA to open a text file with the same file path as the workbook, copy data from a specific column, or range and paste them into the text file.

I'm hoping to do this to a workbook that has multiple sheets. So I was hoping to create a text file for each of the sheets as their name and input data from a column.

Basically, what I need to do is create a text file named "sheet 1" and input data from column"A" of sheet 01. Then create a text file named "sheet 2" and input data from column"A" of sheet 02.

It would be better if I could input data from range "A3" until the end of data in column"A", rather than inputting data from the whole column.

Thanks!

I tried to export data to a text file but it exported the whole sheet to the text file.

Upvotes: 1

Views: 468

Answers (1)

VBasic2008
VBasic2008

Reputation: 54838

Export Single-Column Ranges to Text Files

Sub ExportColumnsA()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim pSep As String: pSep = Application.PathSeparator
    Dim TextFile As Long: TextFile = FreeFile
    
    Dim ws As Worksheet, rg As Range, fCell As Range, lCell As Range
    Dim Data(), rCount As Long, r As Long, rString As String, fPath As String
    
    For Each ws In wb.Worksheets
        Set fCell = ws.Range("A3")
        Set lCell = fCell.Resize(ws.Rows.Count - fCell.Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If Not lCell Is Nothing Then ' data in column
            ' Reference the range.
            Set rg = ws.Range(fCell, lCell)
            ' Write the values from the range to an array.
            rCount = rg.Rows.Count
            If rCount = 1 Then
                ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
            Else
                Data = rg.Value
            End If
            ' Write the values from the array to a string.
            rString = CStr(Data(1, 1))
            For r = 2 To rCount
                rString = rString & vbLf & CStr(Data(r, 1))
            Next r
            ' Write the string to the text file.
            fPath = wb.Path & pSep & ws.Name & ".txt"
            Open fPath For Output As #TextFile
                Print #TextFile, rString;
            Close #TextFile
        'Else ' no data in column; do nothing
        End If
    Next ws
    
    MsgBox "Columns ""A"" exported.", vbInformation

End Sub

Upvotes: 3

Related Questions