Making a text file with identical name & Path

[anichols]

Hello all!

I have some VBA that is creating a pipe delimited text file, but I am struggling to get the name correct. I have managed to save it in the same location, but attempting to name it the same as the excel file has failed. Any help would be greatly appreciated.

Sub PipeDelimited() 
Dim thisWb As Workbook
Set thisWb = ActiveWorkbook

Open thisWb.Path & "\new workbook.txt" For Output As 1  'Change this path



rowno = 1
colcount = Application.CountA(ActiveSheet.Rows(1))
While ActiveSheet.Cells(rowno, 1) <> ""
    dataout = "" 'start of line
    For C = 1 To colcount
        If C <> colcount Then
            dataout = dataout & "" & Trim(ActiveSheet.Cells(rowno, C)) & "|" 
        Else
            dataout = dataout & "" & Trim(ActiveSheet.Cells(rowno, C)) & ""
        End If
    Next C
    Print #1, dataout
    rowno = rowno + 1
Wend
Close #1
End Sub

Thank you,

Drew

 

[anichols]

I have tried this, hoping to get closer, but I get run time error 438.

Open thisWb.Path & "\" & thisWb & ".txt" For Output As 1  'Change this path

 

[John_w]

    Open Left(thisWb.FullName, InStrRev(thisWb.FullName, ".")) & "txt" For Output As #1

 

[anichols]

Awesome! It works perfectly!
Thank you sir!