ms access vba code to rename cell values in an excel worksheet

Need vba help to rename the values in the first row of an excel sheet columns N and O as such: (I am trying to loop through the first row and find a matching value and then rename it.  The code does not work at the line below stating "method worksheets of object application failed error".  Could really use expert help.  Thanks.

If xlObj.Worksheets(shtName).Columns(i).Value = "Annual Dep. Usage" Then



Sub Excel_Rename_Columns()


Dim xlObj As Excel.Application
Dim xlPath As String, shtName As String
Dim filArr(), j
Dim i As Integer
Dim iNumCols 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).Columns(i).Value = "Annual Dep. Usage" Then
            xlObj.Worksheets(shtName).Cell(1, i).Value = "Annual Dep Usage"
        ElseIf xlObj.Worksheets(shtName).Cell(1, i).Value = "Annual Indep. Usage" Then
             xlObj.Worksheets(shtName).Cell(1, i).Value = "Annual Indep Usage"
        End If
    Next
Next

xlObj.ActiveWorkbook.Save
xlObj.Workbooks.Close
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.

sxxguptaAuthor Commented:
figured it out.  It was a syntax error.  I had to use .Cells instead of .Cell
0
Saqib Husain, SyedEngineerCommented:
Try changing it to

If xlObj.Worksheets(shtName).Cells(1,i).Value = "Annual Dep. Usage" Then
0
hnasrCommented:
After setting myRange to the used range:

'.... code to declare and set workbook objects like   Set myRange = ws1.UsedRange
    For Each c In myRange.Cells
        If c.Value = "Name" Then
            c.Value = "Name2"
        Else
            'other code
    Next
'code to manage edited excel file, to save for example

Open in new window

For more help, upload a sample database and excel sheet.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

broro183Commented:
hi,

hi

Here is modified version of your code which makes use of the Replace method from the Excel Object Model. This version should hopefully be faster than looping through every cell in the first row two times.

Option Compare Database
Option Explicit

Sub Excel_Rename_Columns_v2()
Dim xlObj As Excel.Application
Dim xlPath As String
Dim filArr()
Dim j As Long

    DoCmd.SetWarnings False

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

    With xlObj
        For j = LBound(filArr) To UBound(filArr)
            .Workbooks.Open "C:\Users\Robert\Documents\Excel\cur v prvs week.xlsb" 'xlPath & "\" & filArr(j)
            'to prevent "value not found" popups, arising from use of the Replace method, if the value doesn't exist in the top row
            .DisplayAlerts = False
            With .ActiveWorkbook.Worksheets(1).UsedRange.Rows(1)
                .Replace What:="Annual Dep. Usage", Replacement:= _
                         "Annual Dep Usage", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:= _
                         False, SearchFormat:=False, ReplaceFormat:=False
                .Replace What:="Annual Indep. Usage", Replacement:= _
                         "Annual Indep Usage", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:= _
                         False, SearchFormat:=False, ReplaceFormat:=False
            End With
            .DisplayAlerts = True
            .ActiveWorkbook.Close savechanges:=True
        Next j
    End With

    xlObj.Quit
    Set xlObj = Nothing
    DoCmd.SetWarnings True

End Sub

Open in new window


hth
Rob
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:
Thx!!!!!
0
broro183Commented:
I'm pleased I could help. Thank you for the points :-)
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.