Keeping Date Format in String

Hello,

I've tried looking around for someone that posted the exact question I'm looking to have answered and I couldn't find anything so I apologize if I'm duplicating.

Essentially, what I'm trying to do, is change a spreadsheet in xls format to csv format. As part of that, I have a formula that combines all the columns with a ";" separating the information. Here is the formula:

ActiveCell.FormulaR1C1 = "=RC[-11]&"";""&RC[-10]&"";""&RC[-9]&"";""&RC[-8]&"";""&RC[-7]&"";""&FORMAT(RC[-6],""Short Date"")&"";""&FORMAT(RC[-5],""Short Date"")&"";""&RC[-4]&"";""&RC[-3]&"";""&RC[-2]&"";""&RC[-1]"

I believe I'm getting an error because of the 2 columns that I need as "short date". When I originally just had that part of the formula like the rest of it, it would change the date in to a number.

If someone could please let me know what tweak I need to make to the formula to keep the formatting of each column, I'd appreciate it.

Please let me know if you need more information.

Thanks!

Jeff

Norie,

This formula is in VBA. I'm creating a macro and the formula is part of the process.

Thanks

Sorry - I should've tried before I asked... That worked perfectly.

Thanks again Norie!

I apoligize - I see what you mean. I changed the formula to read:

ActiveCell.FormulaR1C1 = "=RC[-11]&"";""&RC[-10]&"";""&RC[-9]&"";""&RC[-8]&"";""&RC[-7]&"";""&TEXT(RC[-6],""Short Date"")&"";""&TEXT(RC[-5],""Short Date"")&"";""&RC[-4]&"";""&RC[-3]&"";""&RC[-2]&"";""&RC[-1]"

...and now "7/1/2012" turned in to "00ort 1at2012". Should I change "Short Date" to "mm/dd/yyyy"?

Thanks