CODEWITHSUNDEEP
excelvbadatenumber-formatting
Tom
Tom

Reputation: 11

Copying dates from CSV into Excel while retaining date formatting

I copy information from various CSV files into an Excel sheet. The files are reports from Symantec that include last scan dates, definitions dates etc.

When I look at the CSV in Excel, the dates are formatted as Custom dd/mm/yyyy hh:mm.

I'm trying, with a VBA script, to run through these files and list the information for systems that haven't scanned in 30 days.

When the information is copied some of the date formats become General, and show the correct format dd/mm/yyyy.
Others become Custom, and switch the values of the dates to mm/dd/yyyy but keep the display the same. So instead of reading 01/12/2020 as 1st of December, it reads it as 12th January. Any date that stars with a number 1-12 seems to be read as mm/dd/yyyy and not the dd/mm/yyyy format that the original is configured as.

Is there VBA code to convert date values on the CSV to Long number before copying them into the Excel sheet and them converting that number back to a date format?

I tried using the .numberformat "dd/mm/yyyy" options and .numberformat "General".

To open each CSV file I do the following:

strFile = Dir(strSourcePath & "*.csv")
Cnt = Cnt + 1
    If Cnt = 1 Then
        r=2
    Else
        r = Cells(rows.count, "A").End(xlUp) + 1
    End If

Open strSourcepath & strFile For Input as #1
    inputRow = 1
        Line Input #1, strData
        If inputRow > 1 Then
            inputRow - inputRow +1
                X = Split(strData, ",")
                For c = 0 To UBound(X)
                    Cells(r, c + 1).Value = Trim(X(c))
                Next c
                r = r + 1
            Else
                inputRow = inputRow + 1
            End If
        Loop
    Close #1
    strFile = Dir
    Loop

Upvotes: 1

Views: 651

Answers (2)

Tom
Tom

Reputation: 11

So, the issue has been resolved with a slightly more in-depth way of doing it.

I now use the workbook.open(filename, local:=True) option to open the CSV, this keeps the date formatting correct.

Then I simply use the TargetCell("range").value = SourceCell("Range").value command to copy the data across.

Thanks for trying guys. I stumbled across the same issue at Date formats/values change when file is opened programmatically

This got my file open correctly, then I could adjust my code accordingly.

Upvotes: 0

LletDeCabra
LletDeCabra

Reputation: 111

I store dates as Long data type.

Imagine in cell A1 there's :

1/12/20

This is [december 1st, 2020], not 12th January. So my code would be:

dim MyDate as long
MyDate = Clng(CDate(Cells(1,1)))

So, now I can work with the Date as a long data type and do whatever in VBA. And when I need to print out, Excel for sure will know which date is.

Upvotes: 0

Related Questions