Well shoot me!

It must have been a gliche as it's now working fine after closing and
opening Excel a few times. Sorry to trouble anyone if you were working on a
reply.

Rob

"Rob" <randwnobel@hotmail.com> wrote in message
news:eLF%23MgolGHA.4164@TK2MSFTNGP03.phx.gbl...
> 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
>
>