Text File creates incorrect Date format

Giganews Newsgroups
Subject: Text File creates incorrect Date format
Posted by:  Rob (randwnob…@hotmail.com)
Date: Fri, 23 Jun 2006

Hi,

I have a spreadsheet which I convert to a text file without any problems,
however......

When I create some code to do the conversion from a click of a button, for
some reason, the resulting text file changes the date format from 23/06/2006
to 6/23/2006.  My Regional settings are OK as well as the format of the
cells. Can anyone tell me what could be the problem?

Below is the code as there may be something in there causing the problem?

Rob

Sub SaveToTextFile()
'On Error GoTo Oops
Dim WB As Workbook
Dim WB2 As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim newFileName As String
    Set WB = Workbooks("Invoice-Order Maker.xls")
    Set SH = WB.Sheets(2) 'Worksheet for Text file
    Set Rng = SH.Range("BK1") 'to give an appropriate name to text file
    'verify data entered in all necessary cells
        If Range("B4") = "" Then
            MsgBox "Please select an item from list."
            Range("B4").Select
            Exit Sub
        End If
        If Range("B5") = "" Then
            MsgBox "Please select an item from list."
            Range("B5").Select
            Exit Sub
        End If
        If Range("B8") = "" Then
            MsgBox "Please enter details for the Journal Memo."
            Range("B8").Select
            Exit Sub
        End If
        If Range("C8") = "" Or Range("D8") = "" Then
            MsgBox "Please enter the appropriate name. (Must be exactly as
entered in MYOB!!)"
            Range("C8").Select
            Exit Sub
        End If
        If Range("F8") = "" Then
            MsgBox "Please enter a date for these transactions."
            Range("F8").Select
            Exit Sub
        End If
    'Prepare data for text file
    Sheet2.Select
        Columns("A:BJ").Select
        Selection.ClearContents
        Range("A1").Select
    'To hide rows not to be transferred to text file
    Sheet1.Select
        Sheet1.Unprotect
        Selection.AutoFilter Field:=1, Criteria1:="1"
        Range("A11:BE1000").Select
        Selection.Copy
    Sheet2.Select 'Worksheet for Text file
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
            True, Transpose:=False
        Range("A1").Select
    Sheet1.Select
        Selection.AutoFilter Field:=1
        Sheet1.Protect
    Sheet1.Select

  'Save data to text file and close
    newFileName = Rng.Text
    SH.Copy
    Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveAs Filename:=newFileName, _
                        FileFormat:=xlText
        .Close
    End With

    MsgBox "You can now import this data to MYOB." _
    & vbLf _
    & vbLf & "The text file is called:  " _
    & vbLf _
    & vbLf & "              " & Sheet2.Range("BK1") _
    & vbLf _
    & vbLf & "and can be found in the same folder where this Workbook
resides."

    Application.DisplayAlerts = True

    'Delete some cells & Save XLS workbook
    Range("A1").Select
    ActiveWorkbook.Save
    Exit Sub
Oops:
    MsgBox "An Error has occured. Please check the procedure before
proceeding."
    Application.DisplayAlerts = True
End Sub

Replies