Results 1 to 6 of 6
  1. #1
    Rick West is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    90

    #Num, Linked Excel File

    When linking to a file in excel (data downloaded form our mainframe), and all fields formatted "General", all data is visible in Access unless we manually add a new record to the Excel file.

    In one of the fields, which is numeric-looking, like 324563, it comes across into Access as #Num and all the other records (from mainframe) look like they are supposed to look.

    Tried clearing all Excel formatting and resetting to General format, nada. Clearing all, resetting to text format, nada.



    I'm on Excel and Acess 2003.

  2. #2
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    Hello Rick,
    Have you considered importing the Excel file into a new Table instead? I am not a big fan of linking Excel files to Access.
    Can you provide some additional info on your purpose with linking the Excel file versus importing to a new table?
    Thanks.

    -RC
    Last edited by MAF4Fam6; 02-03-2010 at 11:42 PM. Reason: Asked an additional question.

  3. #3
    Rick West is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    90
    I agree, importing is best over linking BUT this spreadsheet is only used by 1 employee who insists on using Excel. I have suggested we import, set up some user-friendly forms/reports/etc but he refuses to use Access.

  4. #4
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    What I recommend then, is to import the spreadsheet into a temp Table:
    a) Validate data types for ALL fields in your new Table
    b) Export your Table to a new Excel file
    c) Link the new Excel file to Access

    Note: If this is a recurring process from your mainframe to Access, then I would recommend performing batch updating/syncing of spreadsheet data into Access tables from your recurring file.

    -RC

  5. #5
    Rick West is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    90
    I'll give that a try.

    However, we're still on Excel/Access 2003 and I've seen exports to Excel from Access actually change a number field. This was fixed in the 2007 export but I have to work with what I've got.

    I'll close this as solved and try the fix. If it doesn't work it's not fair to leave this open.

    Thanks.

  6. #6
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    No problem. Good luck.
    Thanks.

    -RC

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

Similar Threads

  1. Linking Excel file problems
    By KevinH in forum Import/Export Data
    Replies: 1
    Last Post: 12-20-2009, 09:28 AM
  2. Replies: 0
    Last Post: 09-18-2009, 07:33 AM
  3. Issues with dating when importing excel file
    By Lainie in forum Import/Export Data
    Replies: 0
    Last Post: 01-22-2009, 10:50 AM
  4. cmd to print/view an MS-Excel file
    By gkast in forum Forms
    Replies: 0
    Last Post: 11-23-2008, 07:39 PM
  5. Replies: 0
    Last Post: 02-21-2008, 09:52 AM

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