R
Rob
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
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