ms access vba code to change data type in a column in an excel file

I am using MS Access VBA and want to use vba coding to change the data type value in column H  in an Excel file (called "Next Delivery Date") to date.  Not sure how to do this.  Could use expert help.  I can rename the certain columns as seen below.  But do not know how to change the datatype (format cells) in column H to date type.


Sub Excel_Rename_Columns()

Dim xlObj As Excel.Application
Dim xlPath As String, shtName As String
Dim filArr(), j
Dim i As Integer

DoCmd.SetWarnings False

filArr = Array("Stock_Status_Report.xls")
xlPath = "C:\Users\sanjayg\Desktop"
Set xlObj = CreateObject("Excel.Application")

For j = LBound(filArr) To UBound(filArr)
    xlObj.Workbooks.Open xlPath & "\" & filArr(j)
    shtName = xlObj.Worksheets(1).Name
    For i = 1 To xlObj.Worksheets(shtName).UsedRange.Columns.Count
        If xlObj.Worksheets(shtName).Cells(1, i).Value = "Description" Then
            xlObj.Worksheets(shtName).Cells(1, i).Value = "Desc"
        ElseIf xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Dep. Usage" Then
            xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Dep Usage"
        ElseIf xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Indep. Usage" Then
             xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Indep Usage"
        End If
    Next
Next

xlObj.ActiveWorkbook.Save
Set xlObj = Nothing

DoCmd.SetWarnings True

End Sub
sxxguptaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
I think...?:

    xlObj.Worksheets(shtName).Columns("H:H").Select
    xlObj.Worksheets(shtName).Selection.NumberFormat = "m/d/yyyy"


or:

    xlObj.Worksheets(shtName).Columns("H:H").NumberFormat = "m/d/yyyy"

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sxxguptaAuthor Commented:
The second part worked.  Thanks Jeff
0
sxxguptaAuthor Commented:
The second part worked.  First part gave an object does not support this method error.
0
Jeffrey CoachmanMIS LiasonCommented:
LOL

Lucky guess on my part...

Glad it helped

Enjoy the weekend!

;-)

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.