Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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 = " .CC = " .BCC = " .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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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 = " .CC = " .BCC = " .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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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 = " .CC = " .BCC = " .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: |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
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 = " .CC = " .BCC = " .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: |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
Joel,
The MESSAGEBox is EMPTY and I still get an error on: Set f = fs.CreateTextFile(FName, True) Also - remember that I have to save the same file to two different locations. thanks Helmut "Joel" wrote: there is something wrong with the files name. It could be you don't have permission to write to the location. Add a msgbox to debug the code. try creating a new file in the directory manualy to see if you have the correct permissions. Make sure you have the createobject line. Set fs = CreateObject("Scripting.FileSystemObject") msgbox(FName) Set f = fs.CreateTextFile(FName, True) "Helmut" wrote: Joel, I deleted the "," and everything behind it and it ran now up to the following in the Subroutine: GOT an error on this Set f = fs.CreateTextFile(FName, True) thanks "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 = " .CC = " .BCC = " .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") |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format in CSV
I added the message box to see if you are passing the filename correctly to
the WriteCSV() macro. Apperently you are not. the problem is with the CALL statement below. The 2nd parameter is not being set to a filenname correctly. You need to set FName to a valid filename. FName = "c:\temp\abc.xls" Call WriteCSV(ActiveWorkbook, FName) "Helmut" wrote: Joel, The MESSAGEBox is EMPTY and I still get an error on: Set f = fs.CreateTextFile(FName, True) Also - remember that I have to save the same file to two different locations. thanks Helmut "Joel" wrote: there is something wrong with the files name. It could be you don't have permission to write to the location. Add a msgbox to debug the code. try creating a new file in the directory manualy to see if you have the correct permissions. Make sure you have the createobject line. Set fs = CreateObject("Scripting.FileSystemObject") msgbox(FName) Set f = fs.CreateTextFile(FName, True) "Helmut" wrote: Joel, I deleted the "," and everything behind it and it ran now up to the following in the Subroutine: GOT an error on this Set f = fs.CreateTextFile(FName, True) thanks "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 = " .CC = " .BCC = " .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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
Convert european foreign date format to US date format | Excel Worksheet Functions | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |