none
Code generates error 1004 Method Range of object failed RRS feed

  • Question

  • This code was generated from an Excel macro. I pasted into Access and it runs fine on the first pass but subsequent runs fail.

    I am not clear how to qualify the code to get it to run thru several executions.

    Public Function runExcelMacro(wkbookPath, wsname)

    Dim xl As Excel.Application

    Dim wb As Excel.Workbook

    Dim ws As Excel.Worksheet

    Set xl = CreateObject("Excel.Application")

    Set wb = xl.Workbooks.Open(wkbookPath)

    Set ws = wb.Worksheets(wsname)

    wb.CheckCompatibility = False

        Range("A1:O1").Select

        With Selection.Interior

            .Pattern = xlSolid

            .PatternColorIndex = xlAutomatic

            .Color = 65535

            .TintAndShade = 0

            .PatternTintAndShade = 0

        End With

        ActiveWorkbook.Save

    wb.Saved = True

    wb.CheckCompatibility = True

    xl.Workbooks.Close

    xl.Application.Quit

    Set wb = Nothing

    Set xl = NothingEnd Function

    Monday, April 23, 2018 9:40 PM

All replies

  • Access has no concept of Selections, Ranges, Sheets, ... So you need to prefix everything with objects.

    Range("A1:O1").Select

    Should be

    ws.Range("A1:O1").Select


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, April 23, 2018 9:51 PM
  • Hi HaroldPa,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 3, 2018 9:32 AM
    Moderator