MS Excel VB to export data to text file

[123raajesh]

Hi,

I am new vba and like to know if there is a way using excel macros or VB in Windows to:


1) create .txt file name using value or content of a cell say A1. It could be alphanumeric.
2) Then get excel data from range column B, where the rows count varies. Should get only data not empty rows of column B.
3) Save the text file on desktop, show message of exact location of file saved.
4) Now as its saved, if macro is run again with same text file name, should replace the previous file if its name is same.
5) The macro is run from another sheet in same workbook, not from the sheet which contains data. Say Macro is from sheet1 and data is in sheet2, the data contains quotes singe or double and pipe, which should not be affected on export.
6) And the clipboard should be cleared after export, as in this code.
We are using office 2010. Pleas suggest simple and easiest way.

Please suggest.

[Tinbendr]

1. Fname = Worksheets(1).Range("A1") & ".txt"

2. Something like this.

4. The code in the thread will overwrite any file with same name.

3. You can set the path to the desktop.

5. Just make sure you FULLY QUALIFY the source worksheet. E.g. Print #1, Worksheets(MySheet").Cells(a, 2)

6. The clipboard isn't used.

[apo]

Hi..

Place a CommandButton on Sheet1 with the below code in it.

Tested by having filename in A1 on Sheet2 and the data on Sheet2 Column B (Row 1 down)..

Change the Filepath (code in Red) to suit.

Please Login or Register  to view this content.

[123raajesh]

@apo I later thought of having a folder on desktop of users. So, whatever the users desktop location may be, should create folder and save the text file there. I tried this using ur code.

Please Login or Register  to view this content.

Thanks for your code and much appreciate it.

[123raajesh]

@apo for some reason this script is failing at .writeline Join(Application.Transpose(myArea), vbCrLf). Runtime error '13' Type mismatch. Any suggestions please