VBA Save page as txt file

[realred2]

This forum has been a great help so far, I've been able to work my way almost to a completed workbook with your help. Thank you Everyone!

I've managed to get all the info I need to a single sheet, and compacted to rows without extra spaces between them from a long list that had gaps between information. Now to work on outputting that info as a tab delimited .txt file.

I can record a macro and create a button, but it doesn't do what I want cleanly.

First, I need to select page "CS INFO PAGE", then clear the contents of E2:E1300, then I want to Save this page as tab delimited (.txt) file in the same folder the original .xlsb file was opened from with a name that pulls from a different page ("ADDRESS MATRIX"), and cell H4, with "CS Info" added before the .txt portion.

Basically, I want hit a button that runs a VBA Macro to clear E2 to E1300 of any information, then "Save as" the page "CS INFO PAGE" to a .txt file in the same folder as the initial excel file.

I would like to not have to "Save as" the original file later when I want to save all the pages again. So if I just hit the "Save" button at the top of excel, it will continue to save as the original file name and type.

Thank you again for any help!

 

[realred2]

I figured out how to clear my E2:E1300 on the end of the VBA that pulls the info over to this page, and I recorded a macro that saves as. I've attached my starting point below.

Sub SaveTXTFile()
'
' SaveTXTFile Macro
'
    
    Cells.Select

    ChDir "C:\Users\xxx\Desktop\CAD Working Folder"
    ActiveWorkbook.saveAS Filename:= _
        "C:\Users\xxx\Desktop\CAD Working Folder\03 ADDRESS MATRIX1.txt", FileFormat _
        :=xlText, CreateBackup:=False
End Sub

As you can tell, it currently saves to a desktop working folder, but when fully implemented, the location should be whatever network folder the original "03 ADDRESS MATRIX1.xlsb" file is in.

Thanks Again for any help!

 

[rpaulson]

Try this on a COPY of your file.

this does NOT save you original excel file in any way. It exports a CSV file.

Sub outputtotextfile()
wline = ""

Dim rs As Worksheet
Set rs = Worksheets("CS INFO PAGE")

rs.Range("E2:E1200").ClearContents

For r = 1 To rs.Cells(Rows.Count, "A").End(xlUp).Row
    lcol = rs.Cells(r, Columns.Count).End(xlToLeft).Column
    wline = wline & Join(Application.Transpose(Application.Transpose(rs.Range("A" & r).Resize(, lcol))), vbTab) & vbNewLine
Next r
   
   
    myPath = Application.ActiveWorkbook.Path & "\"
    myFile = Worksheets("ADDRESS MATRIX").[H4] & " CS INFO PAGE.txt"
    Open myPath & myFile For Output As #1 'Replaces existing file
    Print #1, wline
    Close #1

End Sub

hth,

Ross

 

[realred2]

Try this on a COPY of your file.

this does NOT save you original excel file in any way. It exports a CSV file.

Sub outputtotextfile()
wline = ""

Dim rs As Worksheet
Set rs = Worksheets("CS INFO SHEET")

rs.Range("E2:E1200").ClearContents

For r = 1 To rs.Cells(Rows.Count, "A").End(xlUp).Row
    lcol = rs.Cells(r, Columns.Count).End(xlToLeft).Column
    wline = wline & Join(Application.Transpose(Application.Transpose(rs.Range("A" & r).Resize(, lcol))), vbTab) & vbNewLine
Next r
 
 
    myPath = Application.ActiveWorkbook.Path & "\"
    myFile = Worksheets("ADDRESS MATRIX").[H4] & " CS INFO PAGE.txt"
    Open myPath & myFile For Output As #1 'Replaces existing file
    Print #1, wline
    Close #1

End Sub

hth,

Ross

The line:
wline = wline & Join(Application.Transpose(Application.Transpose(rs.Range("A" & r).Resize(, lcol))), vbTab) & vbNewLine
gives me an error. (Run-Time error '13': Type mismatch)

Sorry it's taken so long to get back to you, thank you for your help.

 

[realred2]

I managed to find several VBA code examples that I could put parts together and get what I was looking for as a output (I think), I'll have to see if the tab before values in each line is a problem on my other program. If anyone can tell me how to remove them, that would be great.

Anyway, for anyone looking, below is my code for this:

Sub ExportToTxt()
    Dim fileStream As Object
    Set fileStream = CreateObject("ADODB.Stream")
    fileStream.Charset = "utf-8"
    fileStream.Open

    Dim rangeToExport As Range
    Set rangeToExport = Worksheets("CS INFO SHEET").Range("A1").CurrentRegion

    Dim firstCol, lastCol, firstRow, lastRow As Integer
    firstCol = rangeToExport.Columns(1).Column
    lastCol = firstCol + rangeToExport.Columns.Count - 1
    firstRow = rangeToExport.Rows(1).Row
    lastRow = firstRow + rangeToExport.Rows.Count - 1

    Dim r, c As Integer
    Dim str, delimiter As String
    For r = firstRow To lastRow
        str = ""
        For c = firstCol To lastCol
            If c = 1 Then
                delimiter = ""
            Else
                delimiter = vbTab ' tab
            End If
            str = str & delimiter & rangeToExport.Cells(r, c).value
        Next c
        fileStream.WriteText str & vbCrLf ' vbCrLf: linebreak
    Next r

   Dim mypath As String
   Dim myfile As String
    mypath = Application.ActiveWorkbook.Path & "\"
       myfile = Worksheets("ADDRESS MATRIX").[H4] & " - CS INFO PAGE.txt"
    fileStream.savetofile mypath & myfile, 2
    fileStream.Close
    MsgBox (".txt File Saved")
End Sub