How to ensure all characters fit on notepad when converting from excel

[NJH88]

Hello,

I have a macro that when executed, converts data on an excel document into a text file (in notepad) It all runs fine and there are 518 characters in total on 1 row. The only issue is that the characters run onto the next row as if it is too short to have all characters in 1 row, however i do need them to be in 1 row. Is there any vba code I can input to ensure this happens or is this an issue with notepad?

Many Thanks

[Izandol]

Do you have Word Wrap turned on in Notepad?

[NJH88]

Hi Izandol,

No have just checked and it is not selected. It just seems to be really short and need to make it wider. Seems really odd

[Izandol]

If you do not have Word Wrap turned on Notepad will not show one row of text on separate lines therefore your macro must create more than one line. May you post your code?

[nilem]

Hi NJH88,
can you show the code of your macro?

[NJH88]

Please Login or Register  to view this content.

[NJH88]

Please Login or Register  to view this content.

[NJH88]

The top code is to gather all the text from userform into the worksheet, the second macro is to convert the data from the wprksheet into notepad (text format).

Any help would be greatly appreciated!

[Norie]

NJH88

Instead of saving as text in print format why not write directly to a text file.

That should be straightforward and would give you complete control over how the data is written to the the file.

What data do you want in the text file?

[Izandol]

Do you have carriage returns or line feeds in any of the text boxes?

[NJH88]

Thanks Norie,

That sounds like a great idea, although not sure how to do it or what code to use? Basically, I have a row of 518 characters in excel, there are several rows of this and includes numeric, alpha, special characters and blanks. I just want all of the rows that are in excel to convert into a text file (have used notepad for this). Does that make sense?
Thankyou

[NJH88]

Yes ive got them both Izandol

[Izandol]

That will be the problem because Notepad will use them to make a new line. What are you doing with your text file? If it is to be loaded into another program I think you will have the same problem unless you can replace those characters.

[nilem]

Hi NJH88, try it

Please Login or Register  to view this content.

edited
or another option

Please Login or Register  to view this content.

[Norie]

Where is the data located?

Cells or textboxes?

[NJH88]

Nilem thankyou, the first option works great, all the text fits into one line. The only issue I have is that there are quotation marks that appear at the start of the line, think is is because there are special characters included oin the data. Any way to remove these?

Have tried the second option but it is giving me a type mismatch error 13 on the 'ReDim y(1 To UBound(x))'

Norie the data that is transferred to excel is from text boxes and the data that is converted to a text file is in cells.

thanks guys

[NJH88]

Thanks Nilem! The first code fits everything in one line, thankyou. The only thing is that when the text is in notepad there are quotation marks around the text, any way I can get rid of these? Also, the second code is bringing up a error messge 13 object not recognised error.....

Norie, the data that is in excel is transferred over from concatenated textboxes, the data transferred into textfile is in cells in a worksheet.

Thanks guys

[nilem]

It would be interesting to look at the example of your file. Can you upload it to the forum?

[NJH88]

tool2.xlsm

Thankyou,

HEre is the file

[nilem]

VBA project in your file is closed, but try this

Please Login or Register  to view this content.

[NJH88]

tool2.xlsm

Sorry Nilem, I have removed the password now for VBA. I tried to insert the code into the module but it was coming up with error message 1004?

[NJH88]

FORMAT TOOL.xlsm

Here it is

thankyou

[nilem]

seems it works, try again

[NJH88]

It works Nilem, thankyou very much for your help. Much appreciated