Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28

    Unanswered: Access with Excel

    Hi Guys,

    I want to apologize in advance for the very long post. I was given a task and I'm losing sleep over its complexity. . Would really appreciate your insight!

    I have summarized my issue below:

    A. Task: I need to create a technical proposal tool. The tool will request inputs from the users, make a fairly complicated amount of calculations to finally come up with a budgetary proposal in word format.

    B. Currently:What they have now is an excel tool with hundreds of fields and around 9 sheets (including 3 look-up tables). They then take the results and enter them manually into a word document

    C. Requirement:
    - Create a user friendly format that makes it mandatory to enter some fields
    - Prevent the users from seeing the pricing tables and calculations behind the tool
    - See trends in pricings, requirements, etc... by querying all the proposals. (Currently, the excel tool has data for one proposal only - hence access is needed).

    D. Problem faced: Access is needed because it's easier to control data input and have a user friendly interface. Plus, several users can access it at the same time, and even with a large amount of data, it's split into an FE and a BE so the tool won't be slow. We will put it on a Citrix server and make sure users cannot modify it. HOWEVER, the calculations are really really complicated and there are many large lookup tables. This means Excel would be much preferable here.

    E. My idea: Tie the access inputs into the excel button at a click of a button, and then automatically extract the results and store them in Access without incorporating all the calculations in Access itself. More importantly, the tool shouldn't allow the users to view the excel tool themselves. Finally, many users globally will be using this at the same time. When the program is accessing the excel tool, will this cause problems?

    IS THIS POSSIBLE? If yes, do you have any suggestions of links where this has been done before?

    Thanks!

    Randa

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    We do this somewhat...
    The end user has an excel sheet , sends it via email.

    The collector, opens the sheet then runs an Excel macro (personal) that saves the wb to a specific place & name...like File2Import.xls

    Then they go to the Access program, run a macro that imports File2Import.xls
    data to a table.

    Access Reports can then be run.

  3. #3
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28
    Thanks ranman.. I decided to code all the formulas in Access.. It's much easier .

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or use forms WITHIN excel
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2014
    Location
    Pittsburgh, USA
    Posts
    28
    I have no clue what you just said..
    Beginner here

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You can design forms within excel so its posiible to give a structured data entry form within excel


    http://support.microsoft.com/kb/291073

    https://www.google.co.uk/webhp?sourc...s%20in%20excel
    Last edited by healdem; 09-03-14 at 05:49.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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