Text to Columns in Visual Basic with English Dates

[jenna_may17]

Hi All!

I'm new to VBA so apologies if this is more straightforward than it seems. I have a spreadsheet which is used to automate some report formatting and create graphs for an end user. The end user exports the required reports from our database, pastes them into my workbook and my macro then performs basic minor formatting changes to allow excel to use the data as required and updates existing pivottables & graphs accordingly.

One aspect of the formatting is that our DB exports dates with a space on the end which means Excel won't recognise these as dates. I've been using text to columns with space as the delimiter and simply deleting the second column afterwards-all good and works fine, but only the first time with that data. The first time you run the macro on a data range the dates are left as UK and are fine. If my end user then adds additional data to the end of the existing range, when the macro runs again the text to columns will turn the dates that have already been amended by the macro the first time to American dates (but of course only the ones which work as American dates). I'd prefer the user to be able to simply export the next month's data from the database and add it to the end of the existing range, however if the macro is going to wreck the dates every time you run it more than once on the same data they will have to export the whole year every time. Does anyone have a solution on how I can get VBA to not turn the dates to American? I'm not sure why it's even doing it as it's not actually directly "handling" the dates as such-it's simply splitting the cell contents after the space and excel is set to UK dates.

Below is the code I'm using to split the columns with the date, essentially simply inserting a blank column after the date column, splitting the date using the "space" delimiter, then deleting the column with the spaces removed.

Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
        :="/", FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft

Any help appreciated! Thanks

 

[AlphaFrog]

What is your date format setting as seen in your Windows Regional-Language control panel; UK or USA?

 

[jenna_may17]

Hi AlphaFrog, my computer regional settings are for English (Australia) which is correctly showing UK dates, however I believe from what I've read on other forums that VBA actually goes off your network regional settings? If this is the case I believe our corporate regional settings might still be US, as VBA exports dates in US. I've managed a workaround for when I'm using dates from userforms (for another workbook) by separating the date into individual strings and then putting it back together, but I'm not sure I could get that to work in this context as the dates are already there so I'm guessing as soon as VBA starts handling them they'd be messed up before the code to fix them ran. The thing that's really throwing me is that it works perfectly the first time, it's only if you run it again that it starts converting them. Thanks for your response!

 

[Peter_SSs]

One fairly easy way would be to just run the macro on the range that contains the spaces. So the second run would only operate on the newly added data.

Also, you don't need to add the extra column and later delete it as Text to Columns has an option to not include certain columns. In my code below, the Field info Array(2, 9) says for column 2 to not parse it, so the extra column is not needed.

Try this in a copy of your workbook. I've assumed a heading in row 1.

Sub Fix_Dates()
  Dim rStart As Range, rEnd As Range
  
  With Columns("C")
    Set rStart = .Find(What:=" ", After:=.Cells(1), LookIn:=xlFormulas, LookAt:=xlPart, SearchDirection:=xlNext, SearchFormat:=False)
    If Not rStart Is Nothing Then
      Set rEnd = .Find(What:=" ", After:=rStart, LookIn:=xlFormulas, LookAt:=xlPart, SearchDirection:=xlPrevious, SearchFormat:=False)
      With Range(rStart, rEnd)
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
          Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
          FieldInfo:=Array(Array(1, 4), Array(2, 9)), TrailingMinusNumbers:=True
      End With
    End If
  End With
End Sub

 

[jenna_may17]

Thanks heaps for your help Peter! The code seems to run fine but it doesn't have the desired effect. I'm thinking perhaps it's not picking up the spaces, that's the only thing I can put it down to. I like your idea of only running it on the new code though, so I'm thinking I'll maybe change out the "search for spaces" bit and maybe record the last row on save so that it can reference that and only do text to columns on the rows below the last row from the previous save. (That was convoluted-hope it makes sense). Unfortunately I can't post with attachments so I can't supply the report we use to see if anyone can figure out why it's not working on that data. With the data itself, if you press F2 in a cell and then enter (without altering the cell contents) it changes to date, and text to columns deliniated by space works (in application), but when you hit F2 your cursor is right next to the last digit, not one out as it would be with a space. I know excel removes spaces on the end of your data if it recognises it as a particular number format, so I'm assuming this is what's happening, it's just strange that it's not picking them up with the sub above. I'll let you know how I go with picking up the last row & running it that way.

 

[AlphaFrog]

Here's another technique which may or may not work. It replaces the space character with null and can cause Excel to convert the text-dates to serial-dates. It's worth a try.

[COLOR=darkblue]Sub[/COLOR] Fix_Dates()
  Columns("C").Replace What:=" ", Replacement:=""
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Norie]

Are you running the code on all the data, including the data that it's already been run on?

If you are and it's causing problems try just running it on the new data.

In fact you could put the new data in a separate sheet, run the code on it and then, after it's been converted, paste the new data below the new data.

 

[Peter_SSs]

Directed at one of your 'exported' date cells (ie before any of these codes have been run on it) what does this return?

=CODE(RIGHT(C5,1))

Unfortunately I can't post with attachments ....

But you can post small screen shots like this which might be helpful with a few rows of your raw exported data.
My signature block below has more info on this.

Excel Workbook
	C
1	Date
2	7/04/14
3	21/12/15
4	7/04/14
5	3/02/12
Dates

 

[jenna_may17]

Hi All! Thanks heaps for your responses. Alpafrog-tried replacing for spaces and no luck again . I ended up writing code to record the last row for the data at the end of the macro, so next time the code runs from the row underneath that to the last row of the new data. Peter-the code formula returns "52" on my date column. I didn't have any luck with the screenshot-the MrExcel link cannot be found and the excel jeanie I can't DL with our corporate settings.

[TABLE="width: 107"]
<TBODY>[TR]
[TD]Inspection Date</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]1/08/2014</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[Peter_SSs]

Peter-the code formula returns "52" on my date column.

That indicates that the last character of your date is a "4", not a space. This perhaps goes some way to explaining why the various suggestions looking for spaces are not working. What makes you think "our DB exports dates with a space on the end"?


Anyway, it sounds like you have a method that will serve your purpose.

the MrExcel link cannot be found

I don't understand that as I have just checked all the links in my signature and they are working fine. Perhaps it is your corporate settings blocking it. In any case, if they prevent the jeanie download they will no doubt also prevent the MrExcel HTML Maker download.