CODEWITHSUNDEEP
excelvbams-access
adidashawn6
adidashawn6

Reputation: 49

Access/Excel VBA Failing

I have an Access database that runs a macro that opens some Excel files and formats the sheets to prepare them for later use. This code has been running fine until my company applied the latest Office Updates, and now I am getting a compile error "Method or data member not found" and its happening on the line... 

wDate = Mid(XlSheet.Range("B4").Value, 13, Len(XlSheet.Range("B4").Value))

singling out the "Range". I cannot figure out why this started happening. Thanks for any assistance. Full code below...

Function ExcelProcess()
'Variables to refer to Excel and Objects
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim MyFile As Variant
Dim MySheet As Variant
Dim wBook As Variant
Dim wSheet As Variant
Dim wDate As Variant

Dim rng As Range
Dim cel As Range


MyFile = Array("w1.xlsx", "w2.xlsx", "w3.xlsx")
MySheet = Array("T2_IND", "APPR_IND", "SLG_APPR_IND", "SLG_IND", "C2A_IND", "C3_IND", "C4_IND", "T3_IND", "T4_IND", "C2B_IND")

For Each wBook In MyFile
    ' Tell it location of actual Excel file
    MySheetPath = "\\fs1\Training\CSC_Training_Ops\Training Only\Buzzard\Pulled Data\" & wBook

    'Open Excel and the workbook
    Set XlBook = GetObject(MySheetPath)

    'Make sure excel is visible on the screen
    XlBook.Windows(1).Visible = True

    For Each wSheet In MySheet
        'Define the sheet in the Workbook as XlSheet
        Set XlSheet = XlBook.Worksheets(wSheet)
        wDate = Mid(XlSheet.Range("B4").Value, 13, Len(XlSheet.Range("B4").Value))

        XlSheet.Range("A15").FormulaR1C1 = "WE_Date"
        If XlSheet.Range("A16").Value <> "No data found" Then
            Set rng = XlSheet.Range(XlSheet.Range("A16"), XlSheet.Range("A16").End(xlDown).Offset(-1))

            For Each cel In rng.Cells
                With cel
                    .FormulaR1C1 = wDate
                    .NumberFormat = "m/d/yyyy"
                End With
            Next cel
        End If
        XlSheet.Rows("1:14").Delete Shift:=xlUp
        XlSheet.Range("A1").End(xlDown).EntireRow.Delete Shift:=xlUp

    Next
    XlBook.Close SaveChanges:=True
Next

'Clean up and end with worksheet visible on the screen
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Function

Upvotes: 1

Views: 134

Answers (2)

ASH
ASH

Reputation: 20302

Is it possible you got upgraded from Office 14.0 to 15.0 or 16.0? Hit Alt+F11 > Tools References and look for errors in the window that opens. Search for the correct reference and click it. As others have suggested, consider rewriting the code using some late binding methodologies.

Upvotes: 0

Adam Vincent
Adam Vincent

Reputation: 3811

There isn't any apparent problems with the code itself.

Since this broke with when you updated the office, I would venture a guess that it is an issue with the reference.

  1. Go to Tools->References->
  2. Remove all references to Excel Object Library
  3. Save & Close the Macro Worksheet (Shouldn't be necessary, but only takes a sec)
  4. Re-Open
  5. Add in reference to only the latest version of Microsoft Excel 1X.0 Object Library

If this does not solve the issue, you may have to run a repair on office

  1. Control Panel -> Add Remove Programs
  2. Locate Microsoft Excel (Or office suite)
  3. Run Repair

Finally, it was suggested to try late binding. Remove the references to the Microsoft Excel Object Library and update your declarations to:

Dim Xl As Object
Dim XlBook As Object
Dim XlSheet As Object
Set Xl = CreateObject("Excel.Application")

Hope this helps!

Upvotes: 3

Related Questions