Override regional settings

[Runejors]

i lkike t create a template that override regional setting. it's from Eu to US format
Dateformat to be mm/dd/YYYY in column B
comma seperator "." in column C
users are novices in excel usage. An I have no VBA knowledge

 

[jkpieterse]

1. You can set the date format of a cell in any way you prefer (Right-click, Format cells)
2. You did not mention why you want to do this?

 

[Runejors]

1. You can set the date format of a cell in any way you prefer (Right-click, Format cells)
2. You did not mention why you want to do this?

Hi
this is a timesheet where users ad date and number as regional settings. these data shal be copied and importet to a system where date format is MM/DD/YYYY. we are using DD.MM.YYYY in regional setting. So i beleive we have 2 option
1. Predefine settings in workbook
2. Change format prior to copy data to a .CSV file or maybe change format in CS V file

 

[jkpieterse]

If you use a small VBA macro to save-as the file to CSV, Excel automatically uses US formats:

Sub SaveAsCSV()
    ActiveWorkbook.SaveAs "c:\SomePath\SomeFIleName.csv", xlCSV
End Sub

 

[Runejors]

If you use a small VBA macro to save-as the file to CSV, Excel automatically uses US formats:

Sub SaveAsCSV()
    ActiveWorkbook.SaveAs "c:\SomePath\SomeFIleName.csv", xlCSV
End Sub

I dont have VBA knowledge. so io dont know how to ad this in the excelfile
is it possible to ask for a path instead of have one predefined?

 

[jkpieterse]

1. Open your workbook, press alt+F11
2. Click Insert, Module
3. Paste the code I show below
4. Press Alt+F11 again to return to Excel
5. Press File, Save as. Make sure you select the macro enabled file type (.xlsm).

Here's the code:

Sub SaveFileAsCSV()
    Dim vFilename As Variant
    Dim sPath As String
    sPath = Environ("USERPROFILE") & "\Documents\"
    vFilename = Application.GetSaveAsFilename(sPath, "Text Files, *.csv", , "Please enter a filename")
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    If vFilename = "" Then Exit Sub
    ThisWorkbook.SaveAs vFilename, xlCSV
End Sub