VBA Save page as txt file

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.

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

Well-known Member
Joined
Oct 4, 2007
Messages
1,205
Try this on a COPY of your file.

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

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

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Try this on a COPY of your file.

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

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

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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:
VBA Code:
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
 

Forum statistics

Threads
1,141,777
Messages
5,708,465
Members
421,571
Latest member
ChaosPup

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top