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

 

[Pinaceous]

Should be good to go.

Let me try the code on a fresh workbook.

Thanks,
Pinaceous

 

[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

Hey Logit,

I've opened a fresh workbook inserted this code into a module after I confirmed the library and the same error pops at:

Open Myfile1 For Output As #1

Kindly let me know.

Thank you,
pinaceous

 

[Logit]

What is the error code you are receiving ?

 

[Pinaceous]

What is the error code you are receiving ?

 

[Logit]

Unusual, works fine here.

Regarding this line of code :

'Assigning a file name with timestamp

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

Change it to :

'Assigning a file name with timestamp

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

(You'll be removing the dashes only)

I learned someone else was having same problem because of "special characters" in the file name.

 

[Logit]

One other thing you can try ... open NOTEPAD and save a blank file with the same name outlined in the macro code.
Place that blank NOTEPAD file in the same location as the workbook and run the macro again. It might be because on your machine
the macro is expecting the file to already exist. So instead of creating the file as it does on my machine, it errors out on yours.