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.
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)
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
Whenever you right-click the area covered by your query and choose refresh data, excel will pickup new data from access.