Controlling MS Excel from MS Access using VBA

[mims]

I am controlling an MS Excel spreadsheet from MS Access using VBA. I want to change a column from text format to Date format. To change the format is simple, but it does not activate the fields until they are double clicked on. When I try to programme this it does not work. I have tried PasteSpecial and TextToColumns, but neither are working.
Please can someone help?
Thanks

 

[ByteMyzer]

In Excel VBA, you can accomplish this with something like the following example. You should be able to adapt the code for Access.

[COLOR="Navy"]Const[/COLOR] xlCellTypeLastCell = 11

[COLOR="navy"]Dim[/COLOR] R [COLOR="navy"]As[/COLOR] Range
[COLOR="navy"]Dim[/COLOR] C [COLOR="navy"]As[/COLOR] Range

[COLOR="navy"]With[/COLOR] ActiveSheet

    [COLOR="navy"]Set[/COLOR] C = .Range("A:A")
    C.NumberFormat = "m/d/yyyy"
    [COLOR="navy"]For Each[/COLOR] R [COLOR="navy"]In[/COLOR] .Range( _
        .Cells(1, C.Column), _
        .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row, _
            C.Column))
        R.Value = R.Value
    [COLOR="navy"]Next[/COLOR] R

[COLOR="navy"]End With[/COLOR]

 

[mims]

Thank you so much - this has really helped me.

Its working really well for all the cells which contain a date, but when it finds a cell which is blank, the code fails and shows warning of 'Type Mismatch'. How can I work around this?

Thanks again

 

[ByteMyzer]

That's interesting. I do not encounter the same error in that scenario. What version of Access and Excel are you running? Can you post the code that you used?