Exporting to a flat txt file

[iainiow]

Hi,

im trying to work out a way of exporting a range of data from an excel worksheet to a flat text file.

My original procedure was to create the data required by the file, use shell to open notepad, then use various send key commands to paste the data in to the new notepad file.. I then used send keys to save and clsoe the text file. worked fine on my PC, but on a colleagues, the send keys commands didnt want to know for some reason. Same PC's, same operating system, same office versions and as far as I can see they were configured correctly.

I tohught that maybe a redesign could be my answer, instead of copying from excel to notepad, maybe I could save the spreadsheet directly to the text file I need. ive been through the various txt saving options under "save as" but all wrap my save data with " " . These will mess up the import of the file in to my target system.

Does anyone know of a good way of exporting this data without the quotes, or why my original file worked on one PC but not the other?

I need to export my data as comma seperated, but with the txt suffix (rather than csv).

any ideas welcome!!

[dominicb]

Good morning iainiow

Excel provides quite a robust framework for writing direct to .txt files, using the Write# and Print# commands. Using Write# will put each value inside a set of quotes, which is undesirable, so we'll use the Print# command instead.

Print# writes a series of values seperated by a tab value. If you don't use a semicolon as I have done in line 9 of the code below then a carriage return (linefeed) character is inserted after each value.

Please Login or Register  to view this content.

To use this code, highlight the range you want exporting and run the macro. The file that is generated is hardcoded into line 2 - adjust this to suit.

HTH

DominicB

[iainiow]

that is pretty much perfect - thanks for taking the time to provide such a comprehensive reply.

one further question if i may on this subject - is there a way from this that I can tell Excel where the line break should be?

For example,

say the range i am selecting is coming from columns A to M and the rows from 1-80. I would like excel to treat row 1 as line on on the text file, then row 2 as line 2 on the text file.



This is how the code stands now:

Please Login or Register  to view this content.

this outputs fine to the file and saves fine, but treats each cell as a line in the document, rather than each row as a line.

Iain

[dominicb]

Hi iainiow

I've amended the code to look like this - only a subtle change to line 4, and a new line 8 which adds a linefeed sequence at the end of a row.

Please Login or Register  to view this content.

Does this sort out the points you commented on?

HTH

DominicB

[R_S_6]

A great bit of code. I was after some code to do just such a while ago, but this hits the nail on the head. Great!

Cheers

[Kenneth Hobson]

DominicB's code gives you alot of control.

In this method, I Replace the Tabs that are inserted between cells to a space character.

Please Login or Register  to view this content.

[iainiow]

apologies for the delayed reply - other pressures got hold of my yesterday afternoon and diverted me from my task.

The examples have been great and ive managed to get exactly the output i was looking for!!

Thanks for your replies, they are much appreciated

[Onweerwolf]

Hi iainiow

I've amended the code to look like this - only a subtle change to line 4, and a new line 8 which adds a linefeed sequence at the end of a row.

Please Login or Register  to view this content.

Does this sort out the points you commented on?

HTH

DominicB

Old thread I know, but this code is brilliant. This is what I was looking for.

However I have a question if it could be adjusted a little to fit my needs: The data I want to export/save-as-text with this code always exists of 1 column and x rows of text. The number of rows (x) is given by a value found in a specific cell. Say cell E1.

So what I would like the macro to do is check the value of E1, then select the number of rows that E1 gives from the correct column. (column is always B)

So if E1=100 then it should select range B1:B100.

After that it should export the selection as usual.

Can anyone help me out?

[millz]

Old thread I know, but this code is brilliant.

Please read the forum rules. As per rule #2, you should start your own thread and provide a link to here.

As for your requirements, try adding the line (highlighted red below) to DominicB's code.

Please Login or Register  to view this content.

[Onweerwolf]

Thanks! Works like a charm!