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.

External Data Range Imported into Excel-Data Format

Discussion in 'Business Applications' started by WAJ0606, Jun 25, 2008.

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

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    I have an external data range importing data (Access DB via file DSN) into an Excel spreadsheet.
    My problem is date/currency fields in Access do not format properly after refreshing. Most of the time the fields default to a general.

    Any ideas?
     

    Attached Files:

  2. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
  3. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
  4. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Try to check the Preserve cell fomatting option, or you tried this and it doesn't work!
     
  5. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    Preserve would keep them as the "General" type as the data expands. Also, when fields are added/deleted or rearranged in the Access query columns that were manually set to for example a date field, but now contain a number keep the date format.

    In essence what I need is to preserve the formatting from Access.
     
  6. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    i ended up adding some vb code to format columns based on the heading. certainly not a solution, but its a temp fix at least
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Could you post your code and explain in more detail what is wrong with it?
     
  8. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
    The VB code works. It formats the entire column based on the field headings. But, its not ideal because the format should stay the same from Access to Excel and the code should never be needed.

    I use a file dsn as to import the data to excel and is refreshed daily across ~30 files, all based on individual queries. End users are constantly changing their requirements which changes the order of the fields/columns. In excel if after a change to a query a date field is now where a number field was previously the format stays as a number and has to be manually changed.
     
  9. WAJ0606

    WAJ0606 Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    137
  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/724584

  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