Pasting an array of dates in a particular number format

[jonser]

Hi everyone,

I am extracting data from an external program which is extracted as the string below.

31/07/2010|01/08/2010|05/08/2010|10/08/2010|15/08/2010|20/08/2010|25/08/2010|30/08/2010|04/09/2010|09/09/2010|14/09/2010|19/09/2010|24/09/2010|29/09/2010|04/10/2010|09/10/2010|14/10/2010|19/10/2010|24/10/2010|29/10/2010|03/11/2010|08/11/2010|13/11/2010|18/11/2010|23/11/2010|28/11/2010|03/12/2010|08/12/2010|13/12/2010|18/12/2010|23/12/2010|28/12/2010|02/01/2011|07/01/2011|12/01/2011|17/01/2011|22/01/2011|27/01/2011|01/02/2011|06/02/2011|11/02/2011|16/02/2011|17/02/2011|21/02/2011|26/02/2011|03/03/2011|08/03/2011|13/03/2011|18/03/2011|23/03/2011|28/03/2011|02/04/2011|07/04/2011|12/04/2011|17/04/2011|22/04/2011|27/04/2011|02/05/2011|07/05/2011|12/05/2011|17/05/2011|22/05/2011|27/05/2011|01/06/2011|06/06/2011|11/06/2011|16/06/2011|19/06/2011|21/06/2011|26/06/2011|01/07/2011|06/07/2011|11/07/2011|16/07/2011|20/07/2011|21/07/2011|26/07/2011|31/07/2011|05/08/2011|10/08/2011|15/08/2011|19/08/2011|20/08/2011|25/08/2011|30/08/2011|04/09/2011|09/09/2011|14/09/2011|19/09/2011|24/09/2011|29/09/2011|04/10/2011|09/10/2011|14/10/2011|19/10/2011|24/10/2011|29/10/2011|03/11/2011|08/11/2011|13/11/2011|18/11/2011|23/11/2011|28/11/2011|03/12/2011|08/12/2011|13/12/2011|18/12/2011|23/12/2011|28/12/2011|02/01/2012|07/01/2012|12/01/2012|17/01/2012|22/01/2012|27/01/2012|01/02/2012|06/02/2012|11/02/2012|16/02/2012|21/02/2012|26/02/2012|02/03/2012|07/03/2012|12/03/2012|17/03/2012|22/03/2012|27/03/2012|01/04/2012|06/04/2012|11/04/2012|16/04/2012|21/04/2012|26/04/2012|01/05/2012|06/05/2012|11/05/2012|16/05/2012|21/05/2012|26/05/2012|31/05/2012|05/06/2012|10/06/2012|15/06/2012|20/06/2012|25/06/2012|30/06/2012|05/07/2012|10/07/2012|14/07/2012|

I then use the split function to turn it into an array and then use the range function to paste the transpose into the cells. Unfortunately when i paste the cells some of the dates come out in american format (when the dd is elss than or equal to 12 it thinks it is the month). So where the first two values should be 31/07/2010 then 01/08/2010 instead it goes 31/07/2010 then 08/01/2010. Is there a way around this? My code is below.

Thanks!

Sub GrabData()
Connect
Dim time As String
Dim timeA As String

'Extracting Strings
time = DoGet("GAP.MOD[{PROD}].SEP[{Sep1}].PREDRES.DATES[$].DATESTR")

timeA = Split(time, "|")
Range("B15:B" & UBound(timeA) + 15) = WorksheetFunction.Transpose(timeA)
Disconnect

End Sub

 

[Jon von der Heyden]

Perhaps overkill but...

You could split the string into an array; then write the array out to freefile as a single column, save to temporary directory as CSV, and then import specifying data column as DMY...

I have to pop next door for a bit so I will have a crack at it when I get back unless someone else has a better way.

 

[jonser]

I'm trying to automate the process into a macro as it's something that'll have to be done quite often with quite large amounts of data I was thinking more if there was a way to paste the array with a certain number format defined. Or alter the number format of the contents of an array...

 

[jonser]

no ideas anyone?

 

[Coryj5]

I have a similar problem when dropping in times that are over 24 hours turning into dte/time formats that road block me. I still have yet to find an answer, so im going to keep an eye on this post! sorry =\

 

[jonser]

The problem was solved by using the following instead of pasting with the Range function

Dim i As Integer
Dim r As Integer
Dim c As Integer
Dim rTarget As Range
Set rTarget = Sheets("Main").Range(startAddress)
r = rTarget.Row
c = rTarget.Column

For i = 0 To UBound(strDate) - 1
Set rTarget = Sheets("Main").Cells(r + i, c)
rTarget.Select
rTarget.Value = CDate(strDate(i))
Next