Excel VBA - Saving file changes date format

Question

I am trying to automate a process we have to be as smooth as possible by using a macro in Excel to drag formulae down as far as my master data needs, then saving each sheet as a prn file.

The issue is that where there are dates, it is saving the format as American dates so when we upload the data into our system it is either wrong or causes an error (e.g. today I tried to upload 30/04/2015 but my system said there is no such date.

When I use MONTH() on the cell before AND after running the macro it gives the correct month, but the prn files it has saved down have the date incorrectly stored (the above appears as 4/30/2015 whereas my Excel sheet still says 30/4/2015.

I've tried storing the date in US format but it then just retains the format as US format

Below is a MWE of what I have written so far, if anyone could help with how to ensure that dates are stored in the UK format this would be much appreciated.

Sub Uploads()

Dim wb As Workbook, wk As Worksheet, rng As Range, num As Long, i As Long, str As String
Set wb = ThisWorkbook
i = 0
num = wb.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row - 7

For Each wk In wb.Worksheets
    wk.Range("A2:Z2").AutoFill Destination:=wk.Range("A2:Z" & num), Type:=xlFillDefault
    wk.Columns.AutoFit
    wk.SaveAs Filename:="H:\Finance\Uploads\CBCS\Upload Master\CBCS (" & i & ") " & Format(Now, "dd-mm-yyyy") & ".prn", FileFormat:=xlTextPrinter
    i = i + 1
Next wk

End Sub

dePatinkin · Accepted Answer · 2015-05-05 09:15:50Z

1

If you manually set the number format of the cells before the export it should work.

wk.Range("A2:Z" & num).NumberFormat = "d/M/yyyy"

answered May 5, 2015 at 9:15

dePatinkin

2,2991 gold badge18 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dePatinkin Over a year ago

Manual SaveAs works even without this setting, but for some reason (bug?) in VBA unless you set the number format it takes the default US format, no matter what your locale is.

TheFullResolution · Accepted Answer · 2015-05-05 09:11:38Z

0

Try to adjust your system settings first... I had experience in the past that if different PCs have different settings it could affect how they see the dates and how macro reacts...

enter image description here

answered May 5, 2015 at 9:11

TheFullResolution

1,3111 gold badge15 silver badges26 bronze badges

1 Comment

dePatinkin Over a year ago

I don't think the locale helps in this case.

Collectives™ on Stack Overflow

Excel VBA - Saving file changes date format

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related