Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rob
 
Posts: n/a
Default Text File creates incorrect Date format

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


  #2   Report Post  
Posted to microsoft.public.excel.misc
Rob
 
Posts: n/a
Default Text File creates incorrect Date format

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" wrote in message
...
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




  #3   Report Post  
Posted to microsoft.public.excel.misc
Rob
 
Posts: n/a
Default Text File creates incorrect Date format

AAAAaaahhhhh!!!

What's going on? It's now not working again and I don't know why. Could
someone please look at my first post and help me with this.

Rob

"Rob" wrote in message
...
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" wrote in message
...
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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text and dates into one cell without loosing date format Hazeltine Excel Discussion (Misc queries) 2 April 25th 06 07:05 PM
text file format to xls [email protected] Excel Discussion (Misc queries) 1 April 22nd 06 07:58 PM
Date Format Problems?? nastech Excel Discussion (Misc queries) 5 November 14th 05 02:53 AM
Excel keeps converting text to date format John T via OfficeKB.com Excel Discussion (Misc queries) 4 September 12th 05 06:48 PM
Date format within a text syntex? Brw Excel Worksheet Functions 2 January 26th 05 03:13 PM


All times are GMT +1. The time now is 03:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"