Conversion to Text file format error

[Rob]

Hi,

I've had to repost this as I may have given the impression the problem was
fixed.

I have a spreadsheet which I convert to a text file without any problems,
by either saving the workbook (with the approriate sheet active) to a text
file, or by making a copy of that sheet to a new workbook and then saving as
a text file.

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

 

[Rob]

Doesn't seem anyone wants to have a go at resolving this so I had another
go.

For some strange reason the problem goes away if I include in the affected
cells the formula =Text(A1,"dd/mm/yyyy"). This is really strange because
the cells are already formated with that date format. (And they appear
correct on the spreadsheet before converting to a TXT file.)

It seems changing the cells to text is a requirement if a cell gets it's
value via a formula when converting to a text file.

If anyone can tell me why..............???

Rob