views:

66

answers:

2

Basically I have a lot of historical data, consisting of IPs, Contacts, and various other fields. Originally we kept all this data in an Excel spreadsheet. I recently created an Access Database with this historical data. Now I am wondering if there is any way to use VBA or something else to autofill fields in the Excel spreadsheet using data from the Access database. Example, I put in an IP Address and a search is done for matches in the database and the Contacts field is filled out automatically. If anyone has an idea on this please let me know.

EDIT: What I'm trying to do is have the field auto-populate with the information from the Access database when I fill in the cell in the Source IP column in the spreadsheet. I import these IPs in a csv file and paste them into the spreadsheet. We have to add anywhere from 10-100 IPs and I'm not wanting to run a Query on each individual one. I'm wanting to automate the process we have right now, so basically I cut and paste, a macro or something runs and the remaining fields auto-populate with the data that's in Access. Edit/Delete Message

A: 

Could you provide some more information, I do not understand your question.

You have imported your information into your database, surely you just need to run a query which will display what you need. There does not seem to be any data manipulation required here at all, VBA or otherwise.

Set your IP address to your primary key, and use it to link into your other tables?

Reply back here with some more information and I will try to help further.

EDIT due to more information, I would consider importing Excel to Access and not the other way around, this will be much more manageable and will allow greater flexability.

Import Excel sheet to access with the above code, play about in there by linking PK's then export back out to Excel for your users

Waller
A: 

It sounds as though you need to write an Excel macro to retrieve data from your Access database, based on data (eg. IP Address) already entered into the spreadsheet.

EDIT: if I understand the edited question correctly, it sounds as though all the database values will have already been pasted into the spreadsheet, so that it won't be necessary to re-query the database. In which case, the contacts field can be populated using the Excel VLOOKUP function.

Mark Bannister
I guess I'm not really sure how to go about writing the Macro to direct it to import information from Access. I can import info from another worksheet in Excel. Could you just give me a generic sample string to go off of?
William
I haven't done this before myself, but some of the answers to this question should be of use: http://stackoverflow.com/questions/358603/access-table-data-to-excel
Mark Bannister
Thanks Mark. VLOOKUP worked like a charm. Of course now I have to figure out have to protect the formulas in the cells, when I'm trying to paste over them since they fall in that range when I paste special using values.
William
Well figured that out too. Thanks again.
William