Access VBA to format Excel - Date Format

[Vishnu R]

Dear All,

I am trying to format the data to DMY in the excel file which is exported to excel using the transfer spreadsheet command. I am trying to format using text to columns as udner.

  .Range("AA2", xlapp.Range("AA65536").End(xlUp)).Select
            .Selection.TextToColumns Destination:=xlapp.Range("AA2"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 4), TrailingMinusNumbers:=True
 

The concern here is that the date format does to change to dmy for example 01/07/2011 post the code above changes to 07/01/2011.

request help ... Thank You in advance.

[NeoPa]

You need to decide first of all whether you are dealing with dates (in which case only the formatting of the cell will determine how they are displayed) or with strings that look like dates (in which case a formula would be required ="01/07/2011" or a single-quote (') would need to be used as the first character of the value '07/01/2011).

[Vishnu R]

The field contains dates stored as strings/text. Also, i tried the text to columns after exporting the raw file where all dates are stored as text within excel. The conversion was as desired in dmy format. Hence am a little confused as to why the text to columns in excel does not work with the same results through access.

Hence as an alternative, can we include an excel module in the file eported through access whcih can be run on opeing the excel file.

[NeoPa]

Vishnu, when dealing in technical matters your answers need to be clearer. "The field" can mean so many dferent things in this context that it tells me nothing. The rest of the wording tells me nothing either as it doesn't even indicate that you understand the difference between the two possibilities (I'm happy if you do of course, but I need to know it before we can proceed).

The second question is premature at this stage.

Please answer these questions clearly :

1. Is the table field where the date data is stored defined as Text?
2. When the data is exported into Excel is the data there a string or a date value? You can determine this by selecting the cell and changing the format to another numeric/date format. String values will appear the same as before, whereas numeric/date values will change to match the new formatting.

[Vishnu R]

NeoPa, I am sorry, I was not clear...

1) This is a text field. The field contains the spouse date of birth for employees. It also holds blank fields.

2)The exported data is a string.

Thank You

[NeoPa]

That's a good clear answer.

Now what confuses me is exactly what you are doing to reformat the data. Text to Columns should work with textual data (as you have) but I cannot see how the code you've posted would be expected to change the format to DMY. Is that what you want to do? If not then please explain exactly what it is you are trying to do with the data.

[Vishnu R]

I was able to fix the concern by adding the following code before executing text to columns.

.Selection.NumberFormat = "dd/mm/yyyy;@"
 

It is working for now..
Thank you NeoPa for your guidance...I will need more of it in the future.

[NeoPa]

Interesting. I'm not sure I follow exactly why that would result in a recognisable string value, but if it does then that takes us back to my suggestion in post #2. Perhaps you should have explored that a little more carefully before your post #3.

Not important though. You have a solution now and that's the most important thing. It's often hard to notice the answer when you're confused yourself, even when you see it.