CODEWITHSUNDEEP
excelvba
wang
wang

Reputation: 1

Overcome Excel VBA Americanisation Issues?

I have written a Excel VB Code to enable the user to choose a folder which contains CSVs file, and the macro will insert all the CSV files into the workbook with each worksheet named as the filename of the CSV files. However, there is a date americanisation issues on the date in the worksheet.Some websites suggest that Local:= True can help to correct this issue, but as I am new to Excel VBA, i don't know where to add in this code.Or anyone here have a better suggestion which can help me on this matter? Thank You

Set wb = ActiveWorkbook
With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show Then myDir = .SelectedItems(1) & "\"
End With
If myDir = "" Then Exit Sub
Application.ScreenUpdating = False
fn = Dir(myDir & "*.csv")
Do While fn <> ""
    With Workbooks.Open(myDir & fn)
        .Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count)
        .Close False
    End With
    fn = Dir
Loop
MsgBox ("All data from your selected source file are completed extracted.")

Upvotes: 0

Views: 55

Answers (2)

ashleedawg
ashleedawg

Reputation: 21639

To display a date different on the worksheet, you can change the number format by selecting the cell or cells, and changing the cell format by pressing Ctrl+1. Select a Date format that works for you or else choose Custom to enter a format like M/D/YYYY or D/M/YYYY as required. See: Format a date the way you want

To convert the date to formatted text on a worksheet, you can use the TEXT function. For example (using cell A1):

=TEXT(A1, "M/D/YYYY")

To use the date as a formatted string in VBA, you can use the FORMAT function. For example (using cell A1):

Format(Range("A1"), "M/D/YYYY")

If you need to programmatically change the cell's formatting, you can use the NumberFormat property:

.NumberFormat = "M/D/YYYY"

If your worksheet will be used in multiple locales, the NumberFormatLocal property might be better:

.NumberFormatLocal

Upvotes: 1

Chris Meurer
Chris Meurer

Reputation: 468

You need to set the Range.NumberFormat property to display the date how you want.

For example, if your dates are in column "D" then something like:

wb.Sheets(i).Range("D:D").NumberFormat = "yyyy-mm-dd"

or you may also use Range.NumberFormatLocal if that gives the desired result.

Upvotes: 0

Related Questions