Saving sheet to text file

[Rob]

Hi,
Can someone please help me with a date formatting problem.

When I save a sheet to a TXT file manually (using the SaveAs option, etc.),
I have no problems and the dates remain as they are shown on the sheet as
d/m/yy (eg 15/9/05), but when I use the following macro, the dates on that
sheet are saved to the text file as month/day/year (eg 9/15/05)

Sub ExportToMYOB()
ActiveWorkbook.Save
Sheet2.SaveAs Filename:="C:\Premier9\Data files\ExportToMYOB.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close
End Sub

Why is it so?

Rob

 

[Dave Peterson]

How did you verify this?

Did you open the file in a text editor (like NotePad)?

When I did save dates as a text file, the format of the date was saved:
dmy or mdy or ymd or....

But if I wasn't careful when I reopened in Excel--not NotePad, then excel would
use my pc's short date format for anything that looked like a date.

9/12/2005 was brought in as September 12, 2005--even though the original file
had a value of December 9, 2005.

I could use the text import wizard and tell excel how to treat that field,
however.

 

[Rob]

Hi Dave,

Thankyou for your reply.

I'm simply saving the sheet as a text file and then importing the data by
another (accounting software) program, (not back to Excel).
For some reason, running the script causes the formatting to be converted to
m/d/yyyy after I export it (when it's showing, and formatted, as d/m/yyyy in
Excel) . Checking the text file, using Notepad, shows that is in the wrong
format, so I wondered if there was something peculiar with the script, as
doing it longhanded doesn't give the problem.
Does the script need something (if it was possible) to make sure the
formatting is retained?
It seems really odd to me that doing it via a macro and doing it manually
give different formats!

Just a BTW, which I can't see has any bearing, the date column contains
formulas taking the date from another sheet.

Rob

 

[Dave Peterson]

I'm sorry, I don't have a followup guess.

It worked fine for me when I did it.

Maybe you could just write out the values the way you want.

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

 

[Rob]

Thanks Dave.

After my last post I thought I'd check the formatting again, and find that
even though the date is presented as d/m/yyyy, the formatting was showing
General. I used the custom formatting option to set it at d/m/yyyy and
exported it via the macro without a problem. Phew! It was really starting
to bother me!!

I still can't understand why I got the different result between macro and
non macro saving to text file, but I'm happy it's working.

Sorry if you wasted your time on this.

Rob