VBA Save worksheet as text

[Pinaceous]

Good Day All,

I managed to come up with a vba code that I am comfortable in using with respect to converting a specified worksheet into a txt file.

However, I would like to know where I can specify which sheet range?

Sub ExportToTXT()

Dim columnA As Variant

With Worksheets(9)
columnA = Application.Transpose(.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Value)
End With

ThisWorkbook.SaveAs ThisWorkbook.path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", 20

End Sub

For example, if I want to capture only for the range between A1:AY36.

Where would I put this into the code?

Kindly let me know.

Thank you!
pinaceous

 

[Logit]

See if this works. Change the range to suit.

Sub ExportToTXT()

With Worksheets(9)
    Range("A1:E54")
End With

ThisWorkbook.SaveAs ThisWorkbook.path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", 20

End Sub

 

[Pinaceous]

Good Day Logit!

Thanks for your post but I am getting an error message with respect to:

 Range("A1:E54")

Any suggestions?

Thanks!
pinaceous

 

[Logit]

What is the error message ?

 

[Pinaceous]

 

[Logit]

Here is a different approach. Edit the range as required.

[CODE=vba]Option Explicit Sub Saveastext1() 'Declaring the variables Dim Myfile1 As String Dim Line1 As String Dim MyRange1 As Range, x, y Dim i As Long 'Assigning a file name with timestamp Myfile1 = "EmployeeData-" & Format(Now, "mmdd-hhmmss") & ".txt" 'Open the file to write data on it. Open Myfile1 For Output As #1 'Setting the range of data to be exported. Set MyRange1 = Range("A1:E5") 'Looping through each cell to save data and apply delimeter as comma For x = 1 To MyRange1.Rows.Count For y = 1 To MyRange1.Columns.Count Line1 = IIf(y = 1, "", Line1 & ",") & MyRange1.Cells(x, y) Next y Print #1, Line1 Next x 'close the file Close #1 End Sub[/CODE]

 

[Pinaceous]

Here is a different approach. Edit the range as required.

[CODE=vba]Option Explicit Sub Saveastext1() 'Declaring the variables Dim Myfile1 As String Dim Line1 As String Dim MyRange1 As Range, x, y Dim i As Long 'Assigning a file name with timestamp Myfile1 = "EmployeeData-" & Format(Now, "mmdd-hhmmss") & ".txt" 'Open the file to write data on it. Open Myfile1 For Output As #1 'Setting the range of data to be exported. Set MyRange1 = Range("A1:E5") 'Looping through each cell to save data and apply delimeter as comma For x = 1 To MyRange1.Rows.Count For y = 1 To MyRange1.Columns.Count Line1 = IIf(y = 1, "", Line1 & ",") & MyRange1.Cells(x, y) Next y Print #1, Line1 Next x 'close the file Close #1 End Sub[/CODE]

Hey Logit,

I do appreciate the code but it does not function for me?

Does it work for you?

Thanks,
pinaceous

 

[Logit]

The previous version is saving your TEXT file to the DOCUMENTS folder.

This version will save the TEXT file to the same location as the current workbook :

Option Explicit

Sub Saveastext1()

'Declaring the variables

   Dim Myfile1 As String

   Dim Line1 As String

   Dim MyRange1 As Range, x, y
  
   Dim i As Long

'Assigning a file name with timestamp

   Myfile1 = ThisWorkbook.Path & "\" & "EmployeeData-" & Format(Now, "mmdd-hhmmss") & ".txt"

'Open the file to write data on it.

   Open Myfile1 For Output As #1

'Setting the range of data to be exported.

   Set MyRange1 = Range("A1:E5")

'Looping through each cell to save data and apply delimeter as comma

   For x = 1 To MyRange1.Rows.Count

       For y = 1 To MyRange1.Columns.Count

           Line1 = IIf(y = 1, "", Line1 & ",") & MyRange1.Cells(x, y)

       Next y

       Print #1, Line1

   Next x

'close the file

   Close #1

End Sub

 

[Pinaceous]

Hey Logit!

I really do appreciate your code!

But I am getting an error message on line:

Open Myfile1 For Output As #1

Do I have to set something up in my library?

Please let me know.

Thank you,
pinaceous

 

[Logit]

Be certain to include the following references in your workbook :