Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    1

    Unanswered: importing data into specific cell in excel

    As you can probably guess from the subject, I want to be able to have excel display certain pieces of data in certain cells. linking and exporting the spreadsheet to access is not an option because I have to use a pre-existing spreadsheet and have it display certain things from an access table in certain areas. It would also be nice if any changes I make in the excel spreadsheet would automatically update the database and vice versa. Any help would be greatly appreciated, I have asked everyone I know and no one has a clue about this.

  2. #2
    Join Date
    Mar 2003
    Location
    London
    Posts
    40

    Re: importing data into specific cell in excel

    Originally posted by wop4life
    I can modify the cells in the spreadsheet that the new data is going to go into but thats about it, the format and layout of the spreadsheet has to stay the same.
    The Spreadsheet will never change its name or location.
    I need to display integers (0-50) in a spreadsheet. The integers will also be what I am updating. The spreadsheet will be set up like this

    Boxes Envelopes Other
    recievingbank1 data data data
    recievingbank2 data data data
    recievingbank3 data data data
    recievingbank4 data data data
    recievingbank5 data data data


    the spots that say data are what will need to be synchronized with access and access should ignore everything else.



    1. To get Excel Data to Access (synchronously)

    Name the 'banks'+'data'+'column names' range in Excel something like 'Postage Info'
    (to do this select the range, goto Insert -> Name -> Define -> Add)
    Go to to Access, go to Tables tab, press New->Link Tables, select .xls files, choose display named ranges, choose 'Postage Info', finish linking having specified field names etc.

    Now you can use the linked table to create a select query based on it.

    Note that the linked table's data will update automatically whenever excel data changes

    2. To get Access Data to Excel

    Goto to your spreadsheet, Data->Get External Data->New Database Query->MS Access Database*->Ok
    Browse to your database->OK
    Choose the query you designed to be based on the linked Excel table
    Choose necessary columns
    Press Next until you see Return data to MS Excel->Finish
    Choose location (the range you specified before)
    Press Properties
    1) Save Query Definition - Yes
    2) Save Password - Yes
    3) Enable Bakcground Refresh - Yes
    4) Refresh Every - No
    5) Refresh data on file open - No
    6) Include field names (If you wish)
    7) Include Row Numbers - NO
    8) Preserve column sort ... YEs
    9) Adjust column width - (if you wish)
    10) Preserve cell formatting - yes

    Be careful with Inser cells, rows etc. settings at the bottom

    Think carefully what you wnat to do

    Press Ok


    Whenever you right-click the area covered by your query and choose refresh data, excel will pickup new data from access.

Posting Permissions

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