Urgent Help on Australian dates on spreadsheet saving as American dates on a text file.

[Dixie]

I have a spreadsheet that uses VBA code in Access to save itself as a text
file. The line is as follows.

xl.ActiveSheet.SaveAs CurrentProject.Path & "\Reports\" &
DLookup("[LetterLabel]", "tblButtons", "ControlID=141") & ".txt", 20 ' 20 =
xlTextWindows

The code is using a DLookup to get the filename because it is different on
different sites.

The major problem, that has just been noticed is that the program is used in
Australia, but the dates saving in the text file are in American format.
They are in Australian format in the spreadsheet, but American in the text
file. This is unacceptable and I need to be able to force them into
Australian date format when the spreadsheet is saved as a text file The
format I need is dd/mm/yyyy.

Can anyone help - please?

dixie

[TC]

This is an Excel issue, right? Not an Access one? (You're using an
Excel method to save the worksheet, not an Access one.) So I'd try
asking on an Excel group, they would be more likely to know.

HTH,
TC

[david.brydon]

>Dixie wrote: ".... - This is unacceptable and I need to be able to force them into

Australian date format when the spreadsheet is saved as a text file The
format I need is dd/mm/yyyy."

Hi Dixie,

I'm sure this is not what your after; however, I'm curious; once
your data has been exported to Excel, can you not re-format your date
cells/column to the desired format, using the custom format cells
option, i.e. dd-mmm-yyyy ?

Cheers,

Dave

[Bernard Peek]

In message <43********@duster.adelaide.on.net>, Dixie
<di***@dogmail.com> writes

I have a spreadsheet that uses VBA code in Access to save itself as a text
file. The line is as follows.

xl.ActiveSheet.SaveAs CurrentProject.Path & "\Reports\" &
DLookup("[LetterLabel]", "tblButtons", "ControlID=141") & ".txt", 20 ' 20 =
xlTextWindows

The code is using a DLookup to get the filename because it is different on
different sites.

The major problem, that has just been noticed is that the program is used in
Australia, but the dates saving in the text file are in American format.
They are in Australian format in the spreadsheet, but American in the text
file. This is unacceptable and I need to be able to force them into
Australian date format when the spreadsheet is saved as a text file The
format I need is dd/mm/yyyy.

Try altering the spreadsheet. Reformat the cells as text instead of
date.

You are more likely to get useful help in an Excel newsgroup.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[Dixie]

Thanks for the help everyone who offered it. I ended up taking the
following approach.

1, Creating a make table query that puts the recordset I want to export into
a temporary table.
2. Creating an export specification for it.
3. Using the DoCmd.TransferText command to send the data.

It seems to work OK now. The problems I was having are no longer apparent.

dixie

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

I have a spreadsheet that uses VBA code in Access to save itself as a text
file. The line is as follows.

xl.ActiveSheet.SaveAs CurrentProject.Path & "\Reports\" &
DLookup("[LetterLabel]", "tblButtons", "ControlID=141") & ".txt", 20 ' 20
= xlTextWindows

The code is using a DLookup to get the filename because it is different on
different sites.

The major problem, that has just been noticed is that the program is used
in Australia, but the dates saving in the text file are in American
format. They are in Australian format in the spreadsheet, but American in
the text file. This is unacceptable and I need to be able to force them
into Australian date format when the spreadsheet is saved as a text file
The format I need is dd/mm/yyyy.

Can anyone help - please?

dixie