CODEWITHSUNDEEP
vbaexcel
Molar Bear
Molar Bear

Reputation: 89

Export column range to text file from different worksheet

I am trying to get a macro to copy the data from the range of A1 to the last cell with data in column A in my second worksheet. This code will output the file to the same destination as the excel workbook, but the file is empty and I'm unsure why. It is currently exporting as a .txt, and I want it as a .prox, but I'm not sure if changing the output name to "ExportedData.prox" will affect the data

Sub OpenTextFile()

Dim FilePath As String
Dim LastRow As Long
Dim CellData As String
Dim WS2 As Worksheet
Dim WS1 As Worksheet


Set WS1 = Worksheets(1)
Set WS2 = Worksheets(2)

WS2.Activate

FilePath = ThisWorkbook.Path & "\" & "ExportedData.txt"
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Open FilePath For Output As #1
For i = 1 To LastRow
CellData = ActiveCell(i).Value
Print #1, CellData
Next i
Close #1

WS1.Activate

MsgBox "Done", vbMsgBoxSetForeground

End Sub

Upvotes: 3

Views: 702

Answers (1)

Tim Williams
Tim Williams

Reputation: 166825

Try this (untested):

Sub OpenTextFile()

    Dim FilePath As String
    Dim LastRow As Long
    Dim CellData As String
    Dim WS2 As Worksheet
    Dim WS1 As Worksheet


    Set WS1 = Worksheets(1)
    Set WS2 = Worksheets(2)

    FilePath = ThisWorkbook.Path & "\" & "ExportedData.prox"
    LastRow = WS2.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    Open FilePath For Output As #1
    For i = 1 To LastRow
        CellData = WS2.Cells(i, 1).Value '<< assumes ColA is being exported
        Print #1, CellData
    Next i
    Close #1

    WS1.Activate

    MsgBox "Done", vbMsgBoxSetForeground

End Sub

Upvotes: 3

Related Questions