Sanjay
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.Applic ation")
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.Co unt
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
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
xlPath = "C:\Users\sanjayg\Desktop"
Set xlObj = CreateObject("Excel.Applic
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).
If xlObj.Worksheets(shtName).
xlObj.Worksheets(shtName).
ElseIf xlObj.Worksheets(shtName).
xlObj.Worksheets(shtName).
ElseIf xlObj.Worksheets(shtName).
xlObj.Worksheets(shtName).
End If
Next
Next
xlObj.ActiveWorkbook.Save
Set xlObj = Nothing
DoCmd.SetWarnings True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Lucky guess on my part...
Glad it helped
Enjoy the weekend!
;-)
JeffCoachman
ASKER