Copied Date Data is NOT a Number

[Maze123]

Hi,

How do I copy date data retaining its number format?

Dim Sourceworkbook1 As Workbook
Set Sourceworkbook1 = Workbooks.Open("[File path & name]")
Application.ScreenUpdating = False
Sourceworkbook1.Sheets("[tab name]").Copy After:=ThisWorkbook.Sheets("[tabname]")
​

I have written a matching algorithm which sorts by both an unique identifier and date. If a record falls outside the defined window it is deleted. For a time, the code performed as expected, copying all data from source documents to a Worksheet in another Workbook and then executing. However, one day it just stopped working and began deleting all records. Debugging ensued. Eventually, I found that the date data copied from source documents was not a number (probably a string but unclear) despite originally being of the usual Gregorian kind. I suspect the Date function is not recognising these string dates because they are not numbers.

Please help clarify what is happening and how to fix it.​

 

[Dan_W]

It seems like you've worked out what has happened - whereas your algorithm is expecting the data in one format, it is receiving it in another format. As you suggest, probably as a string. Can you show us the code? I imagine the solution will be to work out in what format the data is being submitted to your algorithm, and then work out how to 'clean' it.

 

[Maze123]

Hi Dan_W,

Please see attached snippets and code below.

If Worksheets("Rec_9").Cells(r, "B") < DateAdd("h", 17, Date - 3) Then
      Worksheets("Rec_9").Rows(r).EntireRow.Delete
End If

Maybe it would be simpler to xlPasteValuesAndNumberFormats rather than clean after copying. However, my attempts to incorporate PasteSpecial syntax into existing code are messy and I am convinced there is a simple solution in this context. Am I on the right track?

 

[Maze123]

Hi Dan_W,

Figured out the xlPasteValuesAndNumberFormat syntax and it lead one step further back, to here:

Set Sourceworkbook1 = Workbooks.Open("[File path & name]")

​

Upon comparing the same book opened manually to that of the VBA initiated, I found the date data had been VBA opened as string. I have never encountered this problem before and am genuinely lost. Any insight would be appreciated.

 

[Maze123]

Found it! Localised the import to parse data with Local:=True appended at end of parenthesis.