VBA SAVEAS TXT DATA PROBLEMS

[podskubka]

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

 

[Guest]

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:

 

[podskubka]

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.

 

[Guest]

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:

 

[Dave Peterson]

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.