VBA Code to export to .txt file - Needs tweaking

example.xls

Example sheet is attached. The values in red are what I want to export to a text file in the format:


*ELASTIC, TYPE = ANISOTROPIC,
5000, 4600, 5000, 3840, 3840, 2000, 0, 0,
0, 1500, 0, 0, 0, 0, 1500, 0,
0, 0, 0, 0, 200, 0,

Thanks for the response!

Hey guys,

After spending yesterday afternoon trying to figure it out, I am still struggling. I would be most appreciative if someone had a spare 5 mins to help me out

Kind regards

Tom

Thanks guys.

First post is edited to include code tags, apologies.

arlu - When i run your code, a new Excel workbook is made and in cell A1 I the data that I need. Rather than opening in a new excel file and pasting the data into one cell, I would like a pop up box to appear asking the user where to save the .txt file to and to give it a name. Then I would like it to save to the specified file.

snb - This method seems a lot simpler. The text file that is written out using your code writes everything out all on one line, seperated by commas. Unfortunately, it needs to be seperated by lines too (in the same manner that they are seperated in rows on the Excel file, as shown below). I would like to add in a pop up box for user to specify a location and file name too. Would this be possible?

*ELASTIC, TYPE = ANISOTROPIC,
5000, 4600, 5000, 3840, 3840, 2000, 0, 0,
0, 1500, 0, 0, 0, 0, 1500, 0,
0, 0, 0, 0, 200, 0,


Thanks in advance for the help guys

Gents, thanks again for the responses. A few comments:

snb - Using that modified code, the text file still includes commas where there are empty cells (as shown below):


*ELASTIC, TYPE = ANISOTROPIC,,,,,,,
12400,1800,12400,1420,1420,2700,0,0
0,2300,0,0,0,0,2300,0
0,0,0,0,5300,0,,


Also, would it be possible to change the 'input' box to a more conventional type 'explorer' input box where the user can browse folders?


Arlu - Same things happens with your code - the 'blank commas' are still there. Also, after the user enters the filename to save it as, a file is not actually created. An 'unsaved' excel file is created with a name different to the one which the user typed in the input box, and the user then has to go through the saving process again, selecting text file type. Ideally the process would flow something like:

User clicks macro button > User enters file name to save to and presses 'save' > then, either the user is return to the excel sheet OR the text file is automatically opened in notepad.

Thanks again for the help guys, I really do appreciate it!

I don't know, my bad.

The need for the user input is because different people will be using the macro, and everyone likes to store their files in their own 'special place'. There are 13 network locations mapped on my machine, and two hard disks: I know for a fact that my drive 'letters' are not going to be the same as the other users of this file!