Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I have not been able to figure out why I'm getting a 1004: "Select method of Range class failed" error in an Access application which is attempting to loop through a record set and build a series of xls workbooks from a template.



    I successfully open the template and set a reference to the sheet which will be operated on with the following:



    Set wkb = xlApp.Workbooks.Open(FileName:=stXLSTmplt)

    Set sht = xlApp.ActiveWorkbook.Sheets("Expenses")



    The sht reference is passed to several functions as an object parameter and I'm able to successfully load the record set into the spread sheet with



    psht.Range("Data_Start").CopyFromRecordset rst, , 11 where psht is the passed object.



    However, when the code comes to the line

    psht.Range("Yr0d").Select

    the 1004 error is raised.



    In debug mode if I enter in the immediate window

    ?psht.Range("Yr0d").name

    the correct cell reference is returned (=Expenses!$I$9).

    Appreciate any insights,
    Marty


  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I suspect that the Expenses sheet is not the active sheet when you try to execute this line

    psht.Range("Yr0d").Select

    You cannot use this to select a cell on the sheet psht unless it is active

    To overcome this you can use

    xlApp.Goto Reference:="Yr0d"

    Or Probably better in this instance

    pSht.Select
    psht.Range("Yr0d").Select


    Assuming pSht points to the Expenses Sheet
    Andrew

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Andrew,

    Somehow I just didn't see that.
    Thanks much.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I received the same error number when one of my macros tried to paste into a locked cell on a protected sheet.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •