Saved selection to txt file: formatting issue (excess " " created) How to get rid of?

[niels007]

Hello people,

Once again I must turn to the extremely helpfull resource of this forum! In the last episode, I was helped with exporting excel selections bits to a txt file. I can now do that, to any directory with any filename. Super! But...

The way the files are formatted isn't quite correct. The exported files are used in a computer game, where they serve as the physics parameters for a driving game.

Lots of cells are made like this:
="SteerLockRange=(" & F137 & "," & F139 & "," & ((F138-F137)/F139)+1 & ")"

Looks like this in Excel:
SteerLockRange=(10,1,25)

When I save this as a txt file though, this is how it looks:
"SteerLockRange=(10,1,25)"

It added " at both ends, which is not good! You can see there are multiple instances of & used.. Confusingly, when there is just one instance of & such as:
="SteerLockSetting=" & ROUND(((((F138-F137)/(F139))-1)/2),0)

Resulting in Excel:
SteerLockSetting=12

And when saved as a txt file:
SteerLockSetting=12

Now it does NOT add the " " .. .. That is great, but in the first example I'd also want there to be no 'start' and 'end' "" ..

I sure hope there is a sollution. When I manually copy/paste the excel lines to a txt file, it works fine, never a "" too many.. Surely this must also be possible saving the excel file to a txt file?

Thanks (again! )

Niels


PS1: When saving files in excel using a macro, is there a way to stop it asking if I want to overwrite the file? I.e. let it overwrite by default?

PS2: When closing a new workbook, created in a macro, is there a way to close this newly created one without excel asking me if I want to save it?

[dominicb]

Good afternoon niels007

Could you post the piece of code that exports the cells to the .txt file.
Or alternatively, zip and post up a mock workbook that will export a few cells to a .txt file. Once we can recreate the problem it should help in finding a fix.

HTH

DominicB

[niels007]

Hi Dominic,

Will do, here is a simple zipped excel file replicating the problem.

Thanks,

Niels

[dominicb]

Hi niels007

With regards to your code, I took a slightly different approach than you, and output the required cells using the Write# instruction, which basically allows you to build up a .txt file one cell at a time, and guess what? The quotes still get added. Now this makes me think that it’s not going to be such a simple job to create a .txt file from Excel without those quotes.

However, why can’t we look at this from the other point of view. If we can’t save the file without the quotes, we can certainly ignore the quotes when importing them back into Excel. Would this be an option? Do you have any code yet for reading this text file back in?

DominicB

[niels007]

Thanks for looking into it Dominic. A friend of mine on MSN said he encountered this problem himself a while ago and he may still have some code somewhere that fixes this.. I'll be posting my results!

/Niels

[niels007]

Well that sadly didn't do the trick. He suggested to use the "@" numberformat for the cells, but the excess quotes are still there.

The reason its important to me is that its quite a large excel sheet, where 210 files have to be written with the click of one macro ideally..

Imagine copy/pasting 210 ranges manually from excel to notepad.. that works, from the quotes point of view, but is unworkable from an efficiency point of view

I also tried referring to the 'comma' sign and 'close bracket' sign as cells, Comma in A1, close bracket in A2 so this:
="SteerLockRange=(" & F137 & "," & F139 & "," & ((F138-F137)/F139)+1 & ")"

turns into
="SteerLockRange=(" & F137 & A1 & F139 & A1 & ((F138-F137)/F139)+1 & A2

Same results, so its got nothing to do with the actual amount of quotes..
Edit:
The problem lies with the 'comma' signs, as soon as one is in there, outputting to TXT adds quotes, probably some sort of comma separation / stubborn CSV type layout it tries to do..
/edit


I'm starting to sweat here! many many hours of work have gone into this excel project, aiming for the magic one button 'save all files'... which might be hanging from a thread!

Any more ideas folks?

ta,
Niels