Joe,
I'll paste the whole MACRO here for you. I rechecked the whole MACRO and at
the point before SAVING the CSV file, I manually SAVEAS test.csv and then run
the MACRO to let it save the ToCAV0408m.csv -- then I open the two files with
NOTEPAD and in the test.csv the date is 30/04/2008 and in the ToCAV0408m.csv
the date is 4/30/2008 even though on the still open file it displays
30/04/2008.
Would it help you if I send the respective files?
Also to note is that in order to get the correct month, on the INPUT I have
to input 04/01/2008 (mm/dd/yyyy) in order to get the EOMONTH to work. Maybe
there is a connection there?????
If you have other suggestions to streamline this MACRO....thanks
Here is the whole MACRO
-----------------------------------------------------------------------------------------
'
' Meshukamim Macro
' Meshukamim monthly payroll
'
' Keyboard Shortcut: Ctrl+m
' Step One: Open
\\shekel-srv\public\Personel\2008\MonthlyMeshukamimWorkBook.xls
' Step Two: Press Ctrl+m
' Step Three: Open Cav and prepare Journal
' Don't show what's happening
Application.ScreenUpdating = False
'
Dim Message, Title, Default, Myvalue
Message = "Enter Overhead Value i.e. 15.07" ' Set prompt.
Title = "Input Box" ' Set title.
Default = "15.07" ' Set default.
' Display message, title, and default value.
Myvalue = InputBox(Message, Title, Default)
' Input Formular in Column O - (=F1+Myvalue input i.e. 15.07)
Range("O1").Select
ActiveCell.FormulaR1C1 = "=RC[-9]+" & Myvalue
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Change Sheet Name to "payroll"
ActiveSheet.Select
ActiveSheet.Name = "Payroll"
' Format Column Q for date
Range("Q1").Select
Selection.NumberFormat = "dd/mm/yyyy"
' input payroll month
Range("S1").Select
' Dim Message, Title, Default, Myvalue
Message = "Enter Payroll Month date i.e. 05/01/2008 for May 2008" '
Set prompt.
Title = "Input Box" '
Set title.
Default = "05/01/2008" '
Set default.
' Display message, title, and default value.
Myvalue = InputBox(Message, Title, Default)
ActiveCell.FormulaR1C1 = Myvalue
' Enter EndOfMonth formular and copy down
Range("Q1").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(R1C19,0)"
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' Column Autofit and delete not-needed Cell
Columns("Q:Q").EntireColumn.AutoFit
Range("S1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
' Save workbook as "payroll.xlsx"
ActiveWorkbook.SaveAs
Filename:="\\shekel-srv\public\Personel\payroll.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
' Close active workbook
ActiveWorkbook.Close
' Open WorkBook
Workbooks.Open ("\\shekel-srv\public\personel\ToCAV.xlsx")
' Input common Account number in column E
Range("E1").Select
ActiveCell.FormulaR1C1 = "5014002"
Selection.Copy
Range("E1:E2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Delete rows with 0 value in column F
Dim i, j As Integer
Set starta = ActiveSheet.Range("F1")
lr = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = lr To 0 Step -1
If starta.Offset(i, 0).Value = 0 Then starta.Offset(i,
0).EntireRow.Delete
Next i
' Save as CSV report / using mmyy of MyValue and saving directly to
CAV/files
ActiveWorkbook.SaveAs
Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
ActiveWorkbook.SaveAs Filename:="\\cav-new\files\ToCAV" & Left(Myvalue,
2) & Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
'Send Email to Corin that file has been transferred
Dim Filename As String
Filename = ("\\cav-new\files\ToCAV" & Left(Myvalue, 2) & Right(Myvalue,
2) & "m.csv")
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.BCC = "(e-mail address removed)"
.Subject = Filename & "_" & " òëùéå á "
.Body = "ëåøéï, à ú éëåì òëùéå ìéöåø à ú äéåîï á÷å"
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
' Show again
Application.ScreenUpdating = True
' Set Workook property to saved so it does not ask and just closes
ActiveWorkbook.Saved = True
' Close active workbook
ActiveWorkbook.Close
' Quit Excel
Application.Quit
End Sub
-----------------------------------------------------------------------------------------
Joel said:
I assume the data inside the file is incorrect and note the filename itself.
I just want to make sure that when you are using MyValue you are not creating
the date and this is your problem
First, there are lots of problems with the CSV read and write functions in
excel. there do all sorts of data translations that people do not want. In
your case, it is taking a date format and converting it to some unwanted
international standard. Excel releases in different countries to all sorts
of unexpected modifications. You may want to try going into Tool - Options
and change some of the internation options or some other option to see if
this fixes the problem.
I'm working in New Jersey (USA) tonight from 11:00 PM - 7:00 AM (I gues that
would be 8 hours difference in Israel 7:00AM - 3:00PM). If you need
additional help let me know. The other option is to save your file manually.
Here is an example of saving CSV format manually.
Sub putcsv()
Const myFileName = "c:\temp\myfile.csv"
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile _
(myFileName, True)
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To Lastrow
outputline = ""
Lastcol = Cells(RowCount, Columns.Count).End(xlToLeft).Columns
If Lastcol > 0 Then
Do While (Lastcol >= 1) And _
IsEmpty(Cells(RowCount, Lastcol))
Lastcol = Lastcol - 1
Loop
For Colcount = 1 To Lastcol
If Colcount = 1 Then
outputline = Cells(RowCount, Colcount)
Else
outputline = outputline & "," & _
Cells(RowCount, Colcount)
End If
Next Colcount
End If
f.writeline outputline
Next RowCount
f.Close
End Sub
:
Joel,
I opened the newly create csv file in NOTEPAD and format was 4,30,2008 which
is incorrect. ON SCREEN before FILESAVEAS the correct format: 30/04/2008
appears.
So it seems there is a problem with this:
