Save a range as text file using VBA

[Pinaceous]

Good Day Everyone,

I'm exploring the possibility in producing text files, which capture data upon my Workbook, which produce a text file from a specified data range upon a Worksheet.

Sub saveText()
    ActiveWorkbook.SaveAs filename:= _
        ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", FileFormat:=xlText, _
        CreateBackup:=False
End Sub

When I run this code upon a Worksheet it does produce the transfer of data.

However, it changes the Worksheet tab name to the same name as the exported text file.

Do you know how I can prevent this code from changing the Worksheet tab name upon my Workbook and where I can specify a range of the data for the proposed exported file?

Please let me know.

Thank you!
pinaceous

 

[Micron]

About the sheet name, no idea, but if you don't figure it out and no one has the answer to that, you could rename the sheet once the file is created.

About the range: if it is always the same columns but not the same rows or vice versa you could consider a dynamic named range. However, it would grab all the data in that range. So if the range only grows in rows for example and it was A2:D50 today, that would be the range. If tomorrow A2:D99 that would be the range. The point is, the named dynamic named range option is everything in the range as the range grows/shrinks. Perhaps a simpler option would be to use an input box to get the range that you mouse and drag to select. I believe that for this, the range does not have to be contiguous, so that would be a bonus. The selection is passed to the input box and code deals with the selection. I believe the type of input box for this is 8.

 

[Domenic]

A text file does not contain a worksheet. It's simply a plain text file format containing only data. When it's opened in Excel, the data is placed in a worksheet, and the worksheet is named after the filename. And, as you've already discovered when renaming the worksheet, it retains the filename when re-opened.

 

[Pinaceous]

Thanks Guys!

This was a real big help for my application!

Appreciate your feedback!

R/
pinaceous