Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Selecting a Range with VBA

    I have an Access process that opens and manipulates spreadsheets.
    Because one of my users decided to periodically save the spreadsheet,
    I now have to clear it after opening it. I've added 2 lines of code to the
    part of the process that opens the code (in bold below), but I keep getting
    "Object variable or With block variable not set" errors on the line where I'm
    selecting the range. If I replace 'ActiveCell.SpecialCells(xlLastCell)' with "A4",
    it works just fine.

    Code:
    Sub Open_XLSheet(strFile_name)
      On Error GoTo ERR_OPENSH
      Set ObjXL_BOOK = GetObject(strFile_name, "excel.sheet")
      Set ObjXL_APP = ObjXL_BOOK.PARENT
      ObjXL_APP.Visible = True
      ObjXL_BOOK.Windows(1).Visible = True
      ObjXL_APP.Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select
      ObjXL_APP.Selection.Clear
      Exit Sub
    ERR_OPENSH:
      If ErrorLog("UpdateSpreadsheets.OpenXlSheet", Err) = True Then
        Resume
      Else
        MsgBox "An error occured opening workbook", vbOKOnly, "Error"
        Exit Sub
      End If
    End Sub
    Any ideas?
    Thanks
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like a reference issue to me?

    Personally I've always copied a blank file from a templates folder, but that's because I don't delve into all this wizardy in Excel
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    To make matters worse....
    It appears to be sporadic. Works today on my pc, but it didn't yesterday.
    Doesn't work on another pc today, but did yesterday.

    Sometimes I hate MS for their generic error messages.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Object Reference Required.

    Hi

    If I understand this correct, then this code runs in Acces?

    If so, and you are late binding, then pehaps you need a reference to the object that contains the active sheet ie

    this
    ObjXL_APP.Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select

    shouls be like this

    ObjXL_APP.Range("A2", ObjXL_BOOK.ActiveCell.SpecialCells(xlLastCell)).Se lect

    ??

    In these cases I tend to use With blocks for the Excel code and refer everthing that is excel object/code to that, ie. something like.
    Code:
    Set ObjXL_APP = ObjXL_BOOK.Parent
    With ObjXL_APP
        .Visible = True
        .Range("A2", .ActiveCell.SpecialCells(xlLastCell)).Select
        .Selection.Clear
    End With

    BTW you do not need to select everything
    just
    .Range("A2", .ActiveCell.SpecialCells(xlLastCell)).Clear
    should do it


    HTH

    MTB

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks MTB. I'll give that a try.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    MTB is spot on.
    This is something that catches you out because the default object in the office environments is "application". As such, when working within an instance you don't need to explicitly use this. DoCmd is short for Appliction.DoCmd - we just don't typically bother with the Application bit.

    As such, your code works great in Excel. Move it to Access and Access is trying to run Application.ActiveCell.... where application is an Access instance. ActiveCell is an invalid object for an Access application hence the error and the now logical and obvious solution.


  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks for the help. Seems to be working just fine now.

    So I guess theres a danger in writing code in Excel, and then just
    copying it into an Access module - I'll have to remember that next time.
    Inspiration Through Fermentation

Posting Permissions

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