Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    8

    Unhappy Unanswered: WorksheetFunction not showing results

    Hi all

    I am trying to call an Excel Worksheet function through an access module, the function appears to run alright in the module but when I check the worksheet I am running it on no results are displayed.

    Can anyone suggest what I'm doing wrong??




    sh2.Range("a1").Select

    Range("a1") = Excel.WorksheetFunction.Left("e1", WorksheetFunction.Search(" ", "e1", 1))
    Range(Range("a1").End(xlDown)).FillDown

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

    Without seeing all the code that opens excel and the worksheet etc it is difficult to be certain what the problem, but I don’t think you need to use worksheet functions at all as you are oinly manipulating the data in code before asigning the result to a cell. Therefore you can use VBA functions

    If ‘Excel’ is the Excel application created in access then I suggest this may work

    With Excel
    .Range("a1") = Left(.Range("e1"), InStr(.Range("e1"), " "))
    .Range("A1").Select
    .Selection.End(xlDown).Select
    .Range(Range("a1").Address & ":" & .ActiveCell.Address).FillDown
    End With

    If it doesn’t work you will no doubt let us know!

    MTB

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

    I just noticed I missed a reference to the sheet (assuming sht2 is declared as a worksheet object?).

    [code]
    With Excel.sht2
    .Range("a1") = Left(.Range("e1"), InStr(.Range("e1"), " "))
    .Range("a1").Select
    .Selection.End(xlDown).Select
    .Range("a1" & .ActiveCell.Address).FillDown
    End With
    [code]

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    More speed less haste !!

    Code:
    With Excel.sht2
        .Range("a1") = Left(.Range("e1"), InStr(.Range("e1"), " "))
        .Range("A1").Select
        .Selection.End(xlDown).Select
        .Range("a1" & ":" & .ActiveCell.Address).FillDown
    End With
    MTB

Posting Permissions

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