I have the following:

Range("Q1").Select
Selection.NumberFormat = "dd/mm/yyyy"

later I have this:
Range("Q1").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(R1C19,0)"

The date appears correctly in the cell as i.e. "30/04/2008"

If I manually save now as test.csv file, it saves it correctly and when I
open the cell is correct.
HOWEVER
Further in the macro I have this:

ActiveWorkbook.SaveAs
Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV

When I open this file: "ToCAV0408m.csv" the cell appears: "04/30/2008"

I just cant figure out why it changes the cell content. HELP please.

Lets see if the problem is with writing or reading the file. Try opening the
CSV file with Notepad and see what the data looks like. Then we will know
where the problem is located.

"Helmut" wrote:

> I have the following:
>
> Range("Q1").Select
> Selection.NumberFormat = "dd/mm/yyyy"
>
> later I have this:
> Range("Q1").Select
> ActiveCell.FormulaR1C1 = "=EOMONTH(R1C19,0)"
>
> The date appears correctly in the cell as i.e. "30/04/2008"
>
> If I manually save now as test.csv file, it saves it correctly and when I
> open the cell is correct.
> HOWEVER
> Further in the macro I have this:
>
> ActiveWorkbook.SaveAs
> Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
> Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
>
> When I open this file: "ToCAV0408m.csv" the cell appears: "04/30/2008"
>
> I just cant figure out why it changes the cell content. HELP please.

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:
ActiveWorkbook.SaveAs
Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &_
Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV

Can you help?
thanks
Helmut



"Joel" wrote:

> Lets see if the problem is with writing or reading the file. Try opening the
> CSV file with Notepad and see what the data looks like. Then we will know
> where the problem is located.
>
> "Helmut" wrote:
>
> > I have the following:
> >
> > Range("Q1").Select
> > Selection.NumberFormat = "dd/mm/yyyy"
> >
> > later I have this:
> > Range("Q1").Select
> > ActiveCell.FormulaR1C1 = "=EOMONTH(R1C19,0)"
> >
> > The date appears correctly in the cell as i.e. "30/04/2008"
> >
> > If I manually save now as test.csv file, it saves it correctly and when I
> > open the cell is correct.
> > HOWEVER
> > Further in the macro I have this:
> >
> > ActiveWorkbook.SaveAs
> > Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
> > Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> >
> > When I open this file: "ToCAV0408m.csv" the cell appears: "04/30/2008"
> >
> > I just cant figure out why it changes the cell content. HELP please.

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


"Helmut" wrote:

> 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:
> ActiveWorkbook.SaveAs
> Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &_
> Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
>
> Can you help?
> thanks
> Helmut
>
>
>
> "Joel" wrote:
>
> > Lets see if the problem is with writing or reading the file. Try opening the
> > CSV file with Notepad and see what the data looks like. Then we will know
> > where the problem is located.
> >
> > "Helmut" wrote:
> >
> > > I have the following:
> > >
> > > Range("Q1").Select
> > > Selection.NumberFormat = "dd/mm/yyyy"
> > >
> > > later I have this:
> > > Range("Q1").Select
> > > ActiveCell.FormulaR1C1 = "=EOMONTH(R1C19,0)"
> > >
> > > The date appears correctly in the cell as i.e. "30/04/2008"
> > >
> > > If I manually save now as test.csv file, it saves it correctly and when I
> > > open the cell is correct.
> > > HOWEVER
> > > Further in the macro I have this:
> > >
> > > ActiveWorkbook.SaveAs
> > > Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
> > > Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> > >
> > > When I open this file: "ToCAV0408m.csv" the cell appears: "04/30/2008"
> > >
> > > I just cant figure out why it changes the cell content. HELP please.

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 MACR
-----------------------------------------------------------------------------------------
'
' 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 Removed)"
.CC = "(E-Mail Removed)"
.BCC = "(E-Mail 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" wrote:

> 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
>
>
> "Helmut" wrote:
>
> > 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:
> > ActiveWorkbook.SaveAs
> > Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &_
> > Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> >
> > Can you help?
> > thanks
> > Helmut
> >
> >
> >
> > "Joel" wrote:
> >
> > > Lets see if the problem is with writing or reading the file. Try opening the
> > > CSV file with Notepad and see what the data looks like. Then we will know
> > > where the problem is located.
> > >
> > > "Helmut" wrote:
> > >
> > > > I have the following:
> > > >
> > > > Range("Q1").Select
> > > > Selection.NumberFormat = "dd/mm/yyyy"
> > > >
> > > > later I have this:
> > > > Range("Q1").Select
> > > > ActiveCell.FormulaR1C1 = "=EOMONTH(R1C19,0)"
> > > >
> > > > The date appears correctly in the cell as i.e. "30/04/2008"
> > > >
> > > > If I manually save now as test.csv file, it saves it correctly and when I
> > > > open the cell is correct.
> > > > HOWEVER
> > > > Further in the macro I have this:
> > > >
> > > > ActiveWorkbook.SaveAs
> > > > Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
> > > > Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> > > >
> > > > When I open this file: "ToCAV0408m.csv" the cell appears: "04/30/2008"
> > > >
> > > > I just cant figure out why it changes the cell content. HELP please.

I wrote my own macro to save the file as CSV. Pass the FNAME to the function
as shown below and see if you get the same results.


'from
'ActiveWorkbook.SaveAs Filename:="\\cav-new\files\ToCAV" & Left(Myvalue,
'2) & Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
'to

Dim FName As String
Call WriteCSV(ActiveWorkbook, FName)



Sub WriteCSV(book As Workbook, FName As String)

Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(FName, True)

With book.ActiveSheet

Lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To Lastrow

outputline = ""
Lastcol = .Cells(RowCount, Columns.Count). _
End(xlToLeft).Column

For Colcount = 1 To Lastcol
If Colcount = 1 Then
outputline = .Cells(RowCount, Colcount)
Else
outputline = outputline & "," & _
.Cells(RowCount, Colcount)
End If
Next Colcount
f.writeline outputline
Next RowCount
End With
f.Close
End Sub


"Helmut" wrote:

> 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 Removed)"
> .CC = "(E-Mail Removed)"
> .BCC = "(E-Mail 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" wrote:
>
> > 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
> >
> >
> > "Helmut" wrote:
> >
> > > 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:
> > > ActiveWorkbook.SaveAs
> > > Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &_
> > > Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> > >
> > > Can you help?
> > > thanks
> > > Helmut
> > >
> > >
> > >
> > > "Joel" wrote:
> > >
> > > > Lets see if the problem is with writing or reading the file. Try opening the
> > > > CSV file with Notepad and see what the data looks like. Then we will know
> > > > where the problem is located.
> > > >
> > > > "Helmut" wrote:
> > > >
> > > > > I have the following:
> > > > >
> > > > > Range("Q1").Select
> > > > > Selection.NumberFormat = "dd/mm/yyyy"
> > > > >
> > > > > later I have this:
> > > > > Range("Q1").Select
> > > > > ActiveCell.FormulaR1C1 = "=EOMONTH(R1C19,0)"
> > > > >
> > > > > The date appears correctly in the cell as i.e. "30/04/2008"
> > > > >
> > > > > If I manually save now as test.csv file, it saves it correctly and when I
> > > > > open the cell is correct.
> > > > > HOWEVER
> > > > > Further in the macro I have this:
> > > > >
> > > > > ActiveWorkbook.SaveAs
> > > > > Filename:="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
> > > > > Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> > > > >
> > > > > When I open this file: "ToCAV0408m.csv" the cell appears: "04/30/2008"
> > > > >
> > > > > I just cant figure out why it changes the cell content. HELP please.

Joe, sorry, what do you mean by "Pass the FNAME to the function"?
when I run your macro I get an error on:
Set f = fs.CreateTextFile(FName, True)

"Joel" wrote:

> I wrote my own macro to save the file as CSV. Pass the FNAME to the function
> as shown below and see if you get the same results.
>
>
> 'from
> 'ActiveWorkbook.SaveAs Filename:="\\cav-new\files\ToCAV" & Left(Myvalue,
> '2) & Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> 'to
>
> Dim FName As String
> Call WriteCSV(ActiveWorkbook, FName)
>
>
>
> Sub WriteCSV(book As Workbook, FName As String)
>
> Const ForReading = 1, ForWriting = 2, _
> ForAppending = 3
>
> Set fs = CreateObject("Scripting.FileSystemObject")
> Set f = fs.CreateTextFile(FName, True)
>
> With book.ActiveSheet
>
> Lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
>
> For RowCount = 1 To Lastrow
>
> outputline = ""
> Lastcol = .Cells(RowCount, Columns.Count). _
> End(xlToLeft).Column
>
> For Colcount = 1 To Lastcol
> If Colcount = 1 Then
> outputline = .Cells(RowCount, Colcount)
> Else
> outputline = outputline & "," & _
> .Cells(RowCount, Colcount)
> End If
> Next Colcount
> f.writeline outputline
> Next RowCount
> End With
> f.Close
> End Sub
>
>
> "Helmut" wrote:
>
> > 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 Removed)"
> > .CC = "(E-Mail Removed)"
> > .BCC = "(E-Mail 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" wrote:
> >
> > > 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
> > >
> > >
> > > "Helmut" wrote:
> > >
> > > > 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:

for example

Dim FName As String

FName = "c:\temp\abc.xls"
Call WriteCSV(ActiveWorkbook, FName)

You set Filename to two diffferent values so I wasn't sure which one you
really needed.

"Helmut" wrote:

> Joe, sorry, what do you mean by "Pass the FNAME to the function"?
> when I run your macro I get an error on:
> Set f = fs.CreateTextFile(FName, True)
>
> "Joel" wrote:
>
> > I wrote my own macro to save the file as CSV. Pass the FNAME to the function
> > as shown below and see if you get the same results.
> >
> >
> > 'from
> > 'ActiveWorkbook.SaveAs Filename:="\\cav-new\files\ToCAV" & Left(Myvalue,
> > '2) & Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> > 'to
> >
> > Dim FName As String
> > Call WriteCSV(ActiveWorkbook, FName)
> >
> >
> >
> > Sub WriteCSV(book As Workbook, FName As String)
> >
> > Const ForReading = 1, ForWriting = 2, _
> > ForAppending = 3
> >
> > Set fs = CreateObject("Scripting.FileSystemObject")
> > Set f = fs.CreateTextFile(FName, True)
> >
> > With book.ActiveSheet
> >
> > Lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
> >
> > For RowCount = 1 To Lastrow
> >
> > outputline = ""
> > Lastcol = .Cells(RowCount, Columns.Count). _
> > End(xlToLeft).Column
> >
> > For Colcount = 1 To Lastcol
> > If Colcount = 1 Then
> > outputline = .Cells(RowCount, Colcount)
> > Else
> > outputline = outputline & "," & _
> > .Cells(RowCount, Colcount)
> > End If
> > Next Colcount
> > f.writeline outputline
> > Next RowCount
> > End With
> > f.Close
> > End Sub
> >
> >
> > "Helmut" wrote:
> >
> > > 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 Removed)"
> > > .CC = "(E-Mail Removed)"
> > > .BCC = "(E-Mail 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" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Helmut" wrote:

Joel, I actually want to save to two locations. I tried to save to one
location to test yor macro, but got an error here: at the "," after "m.csv"

Fname="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV

I DO NEED TO SAVE TO TWO LOCATIONS AS IN MY OLD MACRO

"Joel" wrote:

> for example
>
> Dim FName As String
>
> FName = "c:\temp\abc.xls"
> Call WriteCSV(ActiveWorkbook, FName)
>
> You set Filename to two diffferent values so I wasn't sure which one you
> really needed.
>
> "Helmut" wrote:
>
> > Joe, sorry, what do you mean by "Pass the FNAME to the function"?
> > when I run your macro I get an error on:
> > Set f = fs.CreateTextFile(FName, True)
> >
> > "Joel" wrote:
> >
> > > I wrote my own macro to save the file as CSV. Pass the FNAME to the function
> > > as shown below and see if you get the same results.
> > >
> > >
> > > 'from
> > > 'ActiveWorkbook.SaveAs Filename:="\\cav-new\files\ToCAV" & Left(Myvalue,
> > > '2) & Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> > > 'to
> > >
> > > Dim FName As String
> > > Call WriteCSV(ActiveWorkbook, FName)
> > >
> > >
> > >
> > > Sub WriteCSV(book As Workbook, FName As String)
> > >
> > > Const ForReading = 1, ForWriting = 2, _
> > > ForAppending = 3
> > >
> > > Set fs = CreateObject("Scripting.FileSystemObject")
> > > Set f = fs.CreateTextFile(FName, True)
> > >
> > > With book.ActiveSheet
> > >
> > > Lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
> > >
> > > For RowCount = 1 To Lastrow
> > >
> > > outputline = ""
> > > Lastcol = .Cells(RowCount, Columns.Count). _
> > > End(xlToLeft).Column
> > >
> > > For Colcount = 1 To Lastcol
> > > If Colcount = 1 Then
> > > outputline = .Cells(RowCount, Colcount)
> > > Else
> > > outputline = outputline & "," & _
> > > .Cells(RowCount, Colcount)
> > > End If
> > > Next Colcount
> > > f.writeline outputline
> > > Next RowCount
> > > End With
> > > f.Close
> > > End Sub
> > >
> > >
> > > "Helmut" wrote:
> > >
> > > > 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 Removed)"
> > > > .CC = "(E-Mail Removed)"
> > > > .BCC = "(E-Mail 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" wrote:
> > > >
> > > > > 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

You don't need the Fileformat in the Filename
Fname="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
Right(Myvalue, 2) & "m.csv"

"Helmut" wrote:

> Joel, I actually want to save to two locations. I tried to save to one
> location to test yor macro, but got an error here: at the "," after "m.csv"
>
> Fname="\\shekel-srv\public\Personel\2008\ToCAV" & Left(Myvalue, 2) &
> Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
>
> I DO NEED TO SAVE TO TWO LOCATIONS AS IN MY OLD MACRO
>
> "Joel" wrote:
>
> > for example
> >
> > Dim FName As String
> >
> > FName = "c:\temp\abc.xls"
> > Call WriteCSV(ActiveWorkbook, FName)
> >
> > You set Filename to two diffferent values so I wasn't sure which one you
> > really needed.
> >
> > "Helmut" wrote:
> >
> > > Joe, sorry, what do you mean by "Pass the FNAME to the function"?
> > > when I run your macro I get an error on:
> > > Set f = fs.CreateTextFile(FName, True)
> > >
> > > "Joel" wrote:
> > >
> > > > I wrote my own macro to save the file as CSV. Pass the FNAME to the function
> > > > as shown below and see if you get the same results.
> > > >
> > > >
> > > > 'from
> > > > 'ActiveWorkbook.SaveAs Filename:="\\cav-new\files\ToCAV" & Left(Myvalue,
> > > > '2) & Right(Myvalue, 2) & "m.csv", FileFormat:=xlCSV
> > > > 'to
> > > >
> > > > Dim FName As String
> > > > Call WriteCSV(ActiveWorkbook, FName)
> > > >
> > > >
> > > >
> > > > Sub WriteCSV(book As Workbook, FName As String)
> > > >
> > > > Const ForReading = 1, ForWriting = 2, _
> > > > ForAppending = 3
> > > >
> > > > Set fs = CreateObject("Scripting.FileSystemObject")
> > > > Set f = fs.CreateTextFile(FName, True)
> > > >
> > > > With book.ActiveSheet
> > > >
> > > > Lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
> > > >
> > > > For RowCount = 1 To Lastrow
> > > >
> > > > outputline = ""
> > > > Lastcol = .Cells(RowCount, Columns.Count). _
> > > > End(xlToLeft).Column
> > > >
> > > > For Colcount = 1 To Lastcol
> > > > If Colcount = 1 Then
> > > > outputline = .Cells(RowCount, Colcount)
> > > > Else
> > > > outputline = outputline & "," & _
> > > > .Cells(RowCount, Colcount)
> > > > End If
> > > > Next Colcount
> > > > f.writeline outputline
> > > > Next RowCount
> > > > End With
> > > > f.Close
> > > > End Sub
> > > >
> > > >
> > > > "Helmut" wrote:
> > > >
> > > > > 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 Removed)"
> > > > > .CC = "(E-Mail Removed)"
> > > > > .BCC = "(E-Mail 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" wrote:
> > > > >
> > > > > > 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