Can anyone help with importing data from a row in Excel into Word

[LSmith4285]

Hello

What I am looking to do is setup a Word template which will autofill. I want to put a number in a field on the template and for it to go and look in column A for that number. When it finds that number I want it to then take the data within that row from column's B,C,D and E and copy them into the Word document.

Basically we want to type a job number into a Word document and for it to then check column A in a spreadsheet for this number, when it finds it we want the address data in column's B,C,D and E to be auto filed in the Word template.

I know this isn't a great way for us to be working but I am looking for a stickie plaster to tie us over for a bit

Thanks

[Lucio]

AFAIK what you're asking isn't a feature built into Word, the closest feature would be to make a mail merge document and then use the settings to print only the relevant record.

[scaryjim]

You can do this with VBA and controls, but it'd be a bit of a pain. I've written some very complex interactions into a Word document when I had no option, but I certainly wouldn't choose to do it. The big advantage of going down that route is that you could import the Excel object model as well as the Word one, making it easier to retrieve the data.

However, what you're really trying to do is a mail-merge, which Word can do for you, and it'll be a fair bit easier to implement than messing around in VB. I suspect that you could combine the two approaches, using a text box to enter the job number and then filtering the results of the mail merge, but I'm not sure off the top of my head, and frankly that's the kind of work I charge people £50 an hour for normally

Can you get the data into Access instead? It'd be *much* easier to query the job numbers that way (yet again I see a company using Excel as a database and it gives me the creeps...). In fact, if you could get Access you'd be able to create a little application that would do all this and then save the results as a formatted report. A little neater than your original intention (although possibly not quite as neat as a filtered mail-merge, tbh)

[LSmith4285]

Thanks for the quick response guys. I think I will have a quick look at filtered mail merge's otherwise the users will just have to wait for the new ERP system to manage all of this for them.

Apparently the last IT director banned Access hence we have so many spreadsheets

[scaryjim]

Apparently the last IT director banned Access hence we have so many spreadsheets

In which case the last IT director knew less about IT than he probably should have Mind you, the mess of VBA and Word and .csv output I had to hack together was because a certain large local authority had such a paranoid IT department that they wouldn't even let people have Excel unless they could produce a fully-researched business case for why they should have it. My brief was: they need a data entry tool for a project we're helping with, the only application they can use is Word.

Yes, it was a nightmare, and no, they didn't pay me enough to deal with that kind of nonsense!

[fuddam]

what about an embedded excel table that uses a vlookup....?
the vlookup data could be stored in a linked workbook