Results 1 to 8 of 8
  1. #1
    medaccess is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4

    Link excel to access

    Hey everyone,
    I have an access database in which I have some fields that are calculated from 2-3 excel sheets for each individual record.
    I have fields in which I attach the excel file for each record.
    It is very difficult to calculate the values for each record using functions in excel and then manually entering the values for each record.
    I wonder if anyone can help me to figure it out by a code to program the access to automatically calculate those values from excel and put them on the specified field for each individual record.


    Greatly appreciate it!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure what "I have fields in which I attach the excel file for each record." means. You have set links to the Excel sheets? If so, can query the linked sheets just like tables. Can be included in query that joins on common ID, assuming the sheets have linking data. Can use DLookup. Can use as data source for INSERT and UPDATE actions into other tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    medaccess is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    Thank you, I have a field (attachment type) in the form. I attach the excel sheet for each record there to save the excel data. But those excel sheets are not linked to the table. There are fields in the form that I need to calculate from the excel sheet. Calculation is somehow simple (multiplication of column 1 by column 2 and dividing to sum of column 2). But, I'm looking for a code or macro to program the access to do that.
    I found these links but I'm not sure:
    http://office.microsoft.com/en-us/ac...#_Toc269881998

    http://www.ehow.com/how_6906059_impo...ess-macro.html

    Thanks,

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Saving Excel workbook in an attachment field is not linking or importing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    medaccess is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    Yes I know I just want to save the data in excel sheet as an attachment for each record.
    Probably, the only solution is to make a macro excel and get the results and enter them manually into the access form. Do you consider any other solution? I'd like to have access do that on the attached excel files. It seems that it's not possible. Thanks,

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Still not clear what you want. What do you mean by 'as an attachment'?

    Either you want data stored in normal text or number field or you want the Excel sheet saved into attachment field.

    It is possible to automate update of fields in table with data from a linked spreadsheet or by opening the sheet as an object in VBA and extracting data. I don't know your data structure so can't be specific.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    medaccess is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    Sorry it is not clear. That's a field named "Attachment" just to store the excel sheet and some figures (JPEG).
    So, probably I should make a linkage between excel spreadsheet and access. But, I don't know how.
    Do you have any webpage to help me in this case? Otherwise, that's fine and I will make a macro and calculate the values for each excel file and manually enter the values
    Alright, thank you for your time,

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You can name that field whatever you want, but is does appear by your description that it is an attachment type.

    Try the import/link wizard. Access Help has guidelines. The Excel data structure will need to be a simple table - a single line of headers and rows of data, no subheaders or 'totals' and 'subtotals' rows.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. excel link with access differs from simple export from access
    By M0RDANT in forum Import/Export Data
    Replies: 4
    Last Post: 03-25-2013, 02:43 PM
  2. One Way Link From Access to Excel
    By dargo72 in forum Import/Export Data
    Replies: 1
    Last Post: 12-06-2012, 01:31 PM
  3. Link Excel to Access problem
    By gg80 in forum Access
    Replies: 1
    Last Post: 02-22-2011, 09:35 AM
  4. link between excel and ms-access
    By Bala Preetha in forum Access
    Replies: 1
    Last Post: 11-28-2010, 08:23 PM
  5. Access link to excel file
    By delkath in forum Access
    Replies: 3
    Last Post: 09-13-2010, 12:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums