Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125

    Unanswered: Using excel functions

    is it possible to reference the excel functions in vba? if so, how would I go about doing it

  2. #2
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    Smile Excel functions in vba

    Dunno if this will help, but open a module in Access. Got to Tools then references. Scroll down and you should find some reference to MS Excel (10 or some such number). Click it, and you should be able to access some of excels class and methods.

    Good luck.

  3. #3
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    just not sure how to reference a particular function

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can Manipulate CELLS, Ranges, Sheets, Fonts ..... but functions I don't think so......

  5. #5
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    which function are you wanting to use? - most of the Excel functions can be replicated with VB.
    Access XP & WinXP Pro

  6. #6
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    it is to find the determinant of a matrix. have looked at code on the net, but the excel function exists, so I just wanted to try and use it

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I have one for Word in VBA attached to OnClick on a label

    docName.EditGoTo "RSA"
    docName.SelectCurIndent
    docName.EditCopy

    SelectCurIndent selects the text from the BookMark RSA to the end of the document

  8. #8
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    would that work for excel, or is it possible to open a hidden window, copy a range of values to the worksheet, and then manipulate them?

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I think you can reference the cell in Excel it will be Ok.

    I have several Word docs where I insert into about 30 Bookmarks form the VBA attachec to OnClick on labels

    The VBA open the Word doc and then among other things runs lots of macros as follows:

    DoCmd.RunMacro "Macro11Street", , ""
    docName.EditGoTo "a3"
    docName.EditPaste

    All the macros like Macro11Street are simply two action lines
    GoToControl
    and ten Copy from MenuItem.

    The "a3" is a Bookmark in Word.

    I run Office 95 so it might be different with later versions but I just checked my Excel and on Edit menu it has a GoTo which opens a box with a space asking for the reference.

    Perhaps if you post up on the Excel forum someone will know

    Mike

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Curiosity got the better of me and I went back to Excel and typed a cell umber in Reference. Just typed E17 and it went straight to E17. Before I did that I copied a word form a Word doc and when I went to Edit on Excel and clicked Paste the entry in the cell was replaced. I widened the cell etc and it was a full replace rather and an append.

    Mike

  11. #11
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    thanks for the feedback,

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Do you know how to do the countepart of this to open Excel at a specified file.

    Const MSTB_MSWORD = 300&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSWORD

    Dim docName As Object
    Set docName = CreateObject("Word.Basic")

    docName.FileOpen "c:\Letters\SoA.doc"

    The next part in my VBA is
    docName.EditGoTo "a2"
    docName.EditPaste

    I think maybe if I could do the Excel counterpart of getting to the Excel file and then "a2" would be the cell reference instead of a Word Bookmark, the I could have Access field values flying all over the place in Excel!!

    I wish you had not raised this issue

    Mike

  13. #13
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    not too sure, think you create a workbook object, and open it. then reference cells using sheet1.Cells(row,col)

  14. #14
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    In the Excel Forum...

    Here's a link to the Excel Forum which, I hope, answers your question.

    http://www.dbforums.com/t997197.html

    Enjoy!

  15. #15
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    thanks, that's exactly what I was looking for.

Posting Permissions

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