Link to home
Start Free TrialLog in
Avatar of Sanjay
SanjayFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sanjay

ASKER

The second part worked.  Thanks Jeff
Avatar of Sanjay

ASKER

The second part worked.  First part gave an object does not support this method error.
LOL

Lucky guess on my part...

Glad it helped

Enjoy the weekend!

;-)

JeffCoachman