VBA- Copying Date Issue

[dhubz]

I have a macro that grabs all of the data from a CSV file which is generated by another program.(Below is Macro I recorded, It opens the CSV file copies all of the cells, goes to the main workbook, pastes the info, then closes the CSV file.)
Column C in the CSV is a DATE in the format "dd/mm/yyyy". The macro works great except the DATE does not get copied over as a date. It looks the same, in the same order, it's left justified. If I select the cells and chose DATE from formatting, nothing happens. I've tried Paste as Values, and PasteSpecial. If I manually go into edit the cells contents and just hit enter, it automatically recognizes as a date and sets it to right justified and the formulas in the other sheets work fine. How do I get the pasted data to be recognized as a Date value?

    Workbooks.Open Filename:= _
        "C:\Raw Data Worksheet.csv"
    Cells.Select
    Selection.Copy
    Windows("Tally Worksheet.xlsm").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.WindowState = xlNormal
    Windows("Niagara Paint Worksheet.csv").Activate
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWindow.Close

 

[offthelip]

you could try converting the text dates to excel date number in vba by running this code (untested!!)

Sub test2()
Dim txt As String
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 3), Cells(lastrow, 3))
For i = 1 To lastrow
 txt = inarr(i, 1)
 inarr(i, 1) = CDate(txt)
Next i
Range(Cells(1, 3), Cells(lastrow, 3)) = inarr

End Sub

 

[GWteB]

Imho best way to open a CSV file is to import as text (Ribbon > Data tab). A wizzard appears on which you're able to define specific settings according to your CSV file, including the type of column delimiter, type of data for each column and date formatting in case a column contains dates.

 

[dhubz]

I've had a similar issue in the past, which I forgot about. If I select the column and find/replace "/" with "/" it works great, I've tried adding it to the macro, but for some reason it doesn't change when processed through a macro. I guess for now, I will make it a manual step in the process.