1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access Linking to Excel

Discussion in 'Business Applications' started by dfriend846, Jan 18, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. dfriend846

    dfriend846 Thread Starter

    Joined:
    Aug 11, 2005
    Messages:
    209
    I am linking an Excel table to Access and one of the columns contains both numbers (e.g. 93500) and text data (e.g. P934a30). See images. My problem is that the link is set up successfully but Access considers the problem column to be numeric and displays $Num! for the text values. I cannot change the field type in Access. I have changed the Excel column format to Text but that does not help. I have seen this before so I suspect it is a common issue that I just do not know the answer for. My reason for doing a link is that the Excel tables will be updated periodically and this seems the easiest way. Can anyone offer any suggestions?
    Dennis
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,408
    First Name:
    Wayne
    when you link the excel file - you have an option in the wizard to set the field type for that column - if memory serves me correctly - not on a PC with access currently
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Dennis, you need to "Cheat" here, delete the link to the current Excel Sheet.
    In the Problem Coulmn(s) enter a "'" in front of the the number in the first rows).
    This makes the Number entry store as "text" and you will get a "warning" in the cell if you have them turned on.
    Save the Workbook.
    Now link to the Workbook and Access will set it up as a Text Field.
    If anybody deletes the "'" or overwrites it with a number you will get the original error come back.
    Personally at Access startup I would Import the Excel data in to a temporary table, Update a proper Access table with a query and then delete the temp table.
    The only problem is the access table won't reflect changes to the Excel sheet until you close and re-open it.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    An easier answer - in the Excel file, highlight the column and go to Format Cells for the Local ERP #. Go to Custom and type in & as the format. Link it to Access - it works fine.
     
  5. Drako

    Drako

    Joined:
    May 7, 2005
    Messages:
    74
    i am pretty much a noob in access but cant you go into design view and change the column to text?
     
  6. dfriend846

    dfriend846 Thread Starter

    Joined:
    Aug 11, 2005
    Messages:
    209
    Hi OBP, I have tried your second solution and it works but the first seems not to: If you note (green image) the first row of the import is a text value. Should I still need to put a ' before it?
    dennis
     
  7. dfriend846

    dfriend846 Thread Starter

    Joined:
    Aug 11, 2005
    Messages:
    209
    Unfortunately not for a linked Excel worksheet. You can change the format on an imported table but not a linked one.
     
  8. dfriend846

    dfriend846 Thread Starter

    Joined:
    Aug 11, 2005
    Messages:
    209
    Interesting, I used an & in the custom format and it replaced all the number values with &. I will check further after work to see if they come out as the proper answer in the Access table.
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    weird - I got it to work using the "&" earlier, but now it doesn't want to....
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/536243

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice