Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Controlling Excel from Access (2002)

    I've been trying this for 2 really frustrating days. Can someone please tell me the best way to select the current cell and 10 cells to the write programmatically. I've been attempting to use the "Selection.Resize(1,10).Select" approach but I keep getting the error: Object variable or With block variable not set .

    Thanks
    Lance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling Excel from Access (2002)

    When using Automation to control one application from another (in this case to control Excel from Access), you must ALWAYS qualify objects belonging to the controlled application. Since Selection is an Excel object, you shouldn't use it by itself. Example:

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet

    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open("MyWorkbook.xls")
    Set xlWsh = xlWbk.Worksheets("MySheet")
    xlWsh.ActiveCell.Resize(1, 11).Select

    By the way, in many cases it is not necessary to select cells in VBA, and it is generally more efficient if you don't. For example, the two lines

    xlWsh.ActiveCell.Resize(1, 11).Select
    xlApp.Selection.ClearContents

    can be replaced with the shorter and more efficient

    xlWsh.ActiveCell.Resize(1, 11).ClearContents

    or if you know that B3 is the active cell:

    xlWsh.Range("B3").Resize(1, 11).ClearContents

  3. #3
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Excel from Access (2002)

    Hans;
    Here is the code I am testing with.
    Sub test()

    Dim xlApp As Excel.Application
    Dim xlWkb As Excel.Workbook
    Dim xlSht As Excel.Worksheet

    Set xlApp = GetObject(, "Excel.Application")
    Set xlWkb = xlApp.Workbooks.Add
    Set xlSht = xlWkb.Sheets(1)
    xlSht.Activate

    xlSht.ActiveCell.Resize(1, 11).Select

    End Sub
    When attempting to execute this I get the error : Method or data Member not found
    The ActiveCell property of the xlSht object on the last line is highlighted.

    Any ideas?

    Lance

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling Excel from Access (2002)

    Sorry, my mistake. ActiveCell is not a property of a worksheet but of the Excel application or active window.

    Since you're creating a new workbook, the active cell will be A1. So you can use

    xlSht.Range("A1").Resize(1, 11).Select

    or

    xlSht.Range("A1:K1").Select

  5. #5
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Excel from Access (2002)

    Hans;

    The code I sent you was just a snippet of the whole program.
    The problem is that I don't know which row I'll be on. The process is filling up rows with data until it gets a control break. I then have to print a "Heading" row, format it with color, font, and size, then continue adding more data/rows until I get another control break. That's why I was attempting to use the ActiveCell property.

    Lance

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling Excel from Access (2002)

    You can use xlApp.ActiveCell where xlApp is the Excel.Application object variable.

  7. #7
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Excel from Access (2002)

    That worked.

    Thank you so very much. You're a life saver.

    Lance

Posting Permissions

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