Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Linking Excel to Access

    Help.............. please can someone advise whether or not I can link a form in Excel to pick up information stored in Access.

    I want to insert an order number in a cell in the Excel form and have some of the info regarding that order automatically inserted into the Excel form.

    The reason for using the Excel form is for pricing information to be used which is stored in an Excel database and also there are a lot of calculations to be made to get a total price....... not sure if this would be possible using only Access !!

    I hope that all makes sense to someone out there.......

    Derek

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Derek1287
    Help.............. please can someone advise whether or not I can link a form in Excel to pick up information stored in Access.

    I want to insert an order number in a cell in the Excel form and have some of the info regarding that order automatically inserted into the Excel form.

    The reason for using the Excel form is for pricing information to be used which is stored in an Excel database and also there are a lot of calculations to be made to get a total price....... not sure if this would be possible using only Access !!

    I hope that all makes sense to someone out there.......

    Derek
    Hi Derek

    In Access go to: File.....Get External Data....then either Import or Link Tables.
    If you select Link Tables that will give you live access for the data to be used. Depends on just what calculations you are doing. Sounds like you're doing a massive amount of calcs to get a price, but I can't imagine what. Just hang on and others may be here to your rescue soon.

    have a nice one,
    BUD

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

    If you want to do the calcs in Excel, and the majority of the info and/or data manipiulation is in Excel, then my method of choice, almost excusively, is to write code in Excel using ADO connections and recordset(s) to place the data in Excel. If the Excel sheet(s) are set up correctly (range names etc.) you can place the data exactly where you want.

    This does assume a certain amount knowledge of coding in Excel/VBA and ADO (or DAO!) recordsets.

    HTH

    MTB

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I have attached a couple of pages from one of my Access 95 books, about DDE for Access to Excel. I made a few of these years ago just to play around. It sticks what you want in the nominated Excel cell.

    As to calculations in Access, I hardly use Excel but I have had plenty of exposure to it with Actuaries in insurance companies. I would say in general that Excel is easier and mainly from the point of view of lining up what data is to have the calculations applied. For exampe and at a very simple level in Excel it is very easy to add Cell A7 to Cell G23, which amounts to a diagonal situation. With Access you basically need to get the two values on the same record or in the same field and then apply the calculation. As a side note, you can do some amazing stuff with Access using forms and SetVale actions in combination with GoToRecord and GoToControl. With Echo = Yes it can make for a good night show

    I would imagine that if you can get Access to produce a value to stick into Excel then you should be able to it in Access
    Attached Files Attached Files
    Last edited by Mike375; 01-15-08 at 11:13.

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I gave you the wrong pages....they were Excel to Access. These two are Access to Excel
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I just checked it and it worked in Access 2003

    I put this in an unbound text box

    =DDESend("Excel","c:\Letters\ModePayment.xls","R2C 3",[TR])

    [TR] being a field on the Access form and R2C3 = Cell C2

    If the excel file is not open a box pops and asks if you want to open. Clcik Yes and the Excel file opens with the value form Access.

    Opening the Access form activates it.

    I guess for some fun you could send the Access value to an .xls file that was linked to Access and it would sort of go around in circles

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    These two work for Access to Word

    The first one is text to the book mark a3 and the second is Access field value to bookmark a4. It works with both To Excel and To Word at the same time.

    =DDESend("WinWord","c:\Letters\0AMPMikeDDESend.doc ","a3","The QuickBrown Fox")

    =DDESend("WinWord","c:\Letters\0AMPMikeDDESend.doc ","a4",[TRAction])

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Hardly suprising but in the interests of science and field testing using " " will rub out existing entries in Excel cells or Word bookmarks

  9. #9
    Join Date
    Jan 2008
    Posts
    2
    Wow..... many thanks for all the replies and advise. I will check out the suggestions.

    Derek

  10. #10
    Join Date
    Aug 2006
    Posts
    559
    Derek,

    Wiley also makes a book that you may want to look into. I saw it at a Barnes & Nobles by me, it's called "Microsoft Excel and Access Integration"

Posting Permissions

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