Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SAVEAS TXT DATA PROBLEMS
Hi
I have a macro which saves my excel sheet as a text file. There are some cells which include date in dd/mm/yyyy format. When I run thi macro VBA changes the date format into mm/dd/yyyy. This means it reads day as month and vice versa. Any ideas? Thank you very much. Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SAVEAS TXT DATA PROBLEMS
If you can do it, try to change your Windows Regional/Language settings to UK
English (dd/mm/yyyy). If this is not possible, convert date cells to text with =TEXT(A1,"dd/mm/yyyy") before SaveAs! Regards, Stefi €˛podskubka€¯ ezt Ć*rta: Hi I have a macro which saves my excel sheet as a text file. There are some cells which include date in dd/mm/yyyy format. When I run thi macro VBA changes the date format into mm/dd/yyyy. This means it reads day as month and vice versa. Any ideas? Thank you very much. Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SAVEAS TXT DATA PROBLEMS
Thanks
As I want other people use this macro I would like to modify my code rather the windows settings. As I am quite new into VBA could you please show me more detailed code how to change format? Cheers. P. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SAVEAS TXT DATA PROBLEMS
This Sub will do the job:
Sub ColConv(ColToConv, RngStart, RngEnd, WorkCol, FormCode) ColDist = Range(WorkCol & 1).Column - Range(ColToConv & 1).Column Range(ColToConv & RngStart & ":" & ColToConv & RngEnd).NumberFormat = "@" Range(WorkCol & RngStart).FormulaR1C1 = "=TEXT(RC[-" & ColDist & "]," & " """ & FormCode & """) " Range(WorkCol & RngStart & ":" & WorkCol & RngEnd).Select Selection.FillDown Selection.Copy Range(ColToConv & RngStart & ":" & ColToConv & RngEnd).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Range(WorkCol & RngStart & ":" & WorkCol & RngEnd).ClearContents End Sub Usage: Call ColConv("A", 1, 6, "J", "dd/mm/yyyy") Parameters: 1. Column to be converted ("A") 2. first row of range to be converted (1) 3. last row of range to be converted (6) 4. any unused helper column("J") 5. format code ("dd/mm/yyyy") Regards, Stefi €˛podskubka€¯ ezt Ć*rta: Thanks As I want other people use this macro I would like to modify my code rather the windows settings. As I am quite new into VBA could you please show me more detailed code how to change format? Cheers. P. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SAVEAS TXT DATA PROBLEMS
Name your output file .txt (not .csv).
Then provide a macro to the end user that opens the text file and parses the data the way you want. You can record a macro when you do it manually -- specify Date and dmy for that field. Save your macro workbook as a different file and share that with the end users. If you add formatting/headers/footers/print layout/filters/subtotals, they may even like the importer better than just the raw data. podskubka wrote: Hi I have a macro which saves my excel sheet as a text file. There are some cells which include date in dd/mm/yyyy format. When I run thi macro VBA changes the date format into mm/dd/yyyy. This means it reads day as month and vice versa. Any ideas? Thank you very much. Pete -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation causing problems when using a data form in Excel 2 | Excel Worksheet Functions | |||
Problems Obtaining Data | Excel Discussion (Misc queries) | |||
Problems using SaveAs and SaveCopyAs | Excel Programming | |||
SaveAs problems | Excel Programming | |||
Problems with active cell after saveas | Excel Programming |