automating the filling out of a form using tables of info

[primuspaul]

I have to fill out a form pretty often. Basically, the data is in an excel file in tables.

There's a table for owners: name, address, tel#, Fax #, position (can be more than 1), and signature (image!).
There is a table for clients: name, address, tel#, fax #.

All that data is already entered, but I want to be able to just select an owner by name, select a client by name, and have the form automatically filled out with those parameters based on the tables.

I thought about mailmerge, linking the document to excel, but it really doesn't work well since I'm creating one combination of owner and client, not batch generating 100 static client forms.

Any suggestions? Ideally the end product would have the image of the blank form in the background of a docx file and all I'd need to do is pick from a drop-down menu the owner and the client and the rest of it would be filled out for me.

 

[primuspaul]

Anyone have any idea? Before I was using a mailmerge worksheet in an excel file to generate one "entry" that was based on the two other worksheets (referencing the tables with index(match()). However, this ended up being quite clunky and had no solution to the signature (image) requirement.

 

[My Aswer Is This]

I looked at this question but have a lot of questions.

I would need to know what sheet these tables are located on and the exact name of these Tables

And you said:
I want to be able to just select an owner by name, select a client by name

How are you going to select an owner and a client name?

See to help with questions we always need specific written details like this.

Please do not just post a image or say here is a link to my file.

I would like to help but I need specific details.

 

[primuspaul]

I looked at this question but have a lot of questions.

I would need to know what sheet these tables are located on and the exact name of these Tables

And you said:
I want to be able to just select an owner by name, select a client by name

How are you going to select an owner and a client name?

See to help with questions we always need specific written details like this.

Please do not just post a image or say here is a link to my file.

I would like to help but I need specific details.

Presently the owners are on a sheet called OWNERS in a table labeled (and can be referenced by) OWNERS. Clients are on a sheet called CLIENTS and the table is called CLIENTS.

I would like this to be on a third excel sheet where I have a validate field for CLIENT, OWNER1, OWNER2. There can be only one client per one of these forms, but the number of owners is either one or two. So if the company is owned by John, Mary, Tom, and Larry (let's say a law firm or something) and they do work for Company ABC and DED Development, the OWNERS dropdowns (validate field based on OWNERS table) would EACH have John, Mary, Tom, and Larry to select from while the CLIENT dropdown would reference and have as selection Company ABC and DED Development (you'd be allowed to pick one).

The way I have it now is kind of a workaround. I do have the CLIENTS and OWNERS tabs and tables. Then I have a SELECTION table/sheet with two rows. It contains columns for owner selection and company selection (the latter only needs to be selected on the first row as there can only be one company per one of these forms anyway) that are validate fields based on one of the two tables and the rest are autofilled with index(match()) based on owners table for the owners cells and based on clients table for the client cell. And THEN I have a fourth MAILMERGE table/sheet that contains just ONE totally programmed data row (also has a headings row obviously) with rows like OWNER1, OWNER2, CLIENT, OWNER1_address, OWNER2_address, CLIENT_address, etc...

Then I was using Word to "mail merge" this info into textboxes placed on top of an 8.5x11 image with no margins (image of the form already has its own whitespace margins). I didn't even need to press mail merge since there was only one entry in the MAILMERGE sheet to begin with.

My problem is it's a bit clunky. I would prefer to just have 3 sheets. One for clients, one for owners, and one for where I do the selection. And for it to be a bit more "logical" in case I come back to it after a month. It also sucks because if I move the files, they get orphaned and I need to redo the word-to-excel data link. I'd rather just have one file.

 

[My Aswer Is This]

Presently the owners are on a sheet called OWNERS in a table labeled (and can be referenced by) OWNERS. Clients are on a sheet called CLIENTS and the table is called CLIENTS.

I would like this to be on a third excel sheet where I have a validate field for CLIENT, OWNER1, OWNER2. There can be only one client per one of these forms, but the number of owners is either one or two. So if the company is owned by John, Mary, Tom, and Larry (let's say a law firm or something) and they do work for Company ABC and DED Development, the OWNERS dropdowns (validate field based on OWNERS table) would EACH have John, Mary, Tom, and Larry to select from while the CLIENT dropdown would reference and have as selection Company ABC and DED Development (you'd be allowed to pick one).

The way I have it now is kind of a workaround. I do have the CLIENTS and OWNERS tabs and tables. Then I have a SELECTION table/sheet with two rows. It contains columns for owner selection and company selection (the latter only needs to be selected on the first row as there can only be one company per one of these forms anyway) that are validate fields based on one of the two tables and the rest are autofilled with index(match()) based on owners table for the owners cells and based on clients table for the client cell. And THEN I have a fourth MAILMERGE table/sheet that contains just ONE totally programmed data row (also has a headings row obviously) with rows like OWNER1, OWNER2, CLIENT, OWNER1_address, OWNER2_address, CLIENT_address, etc...

Then I was using Word to "mail merge" this info into textboxes placed on top of an 8.5x11 image with no margins (image of the form already has its own whitespace margins). I didn't even need to press mail merge since there was only one entry in the MAILMERGE sheet to begin with.

My problem is it's a bit clunky. I would prefer to just have 3 sheets. One for clients, one for owners, and one for where I do the selection. And for it to be a bit more "logical" in case I come back to it after a month. It also sucks because if I move the files, they get orphaned and I need to redo the word-to-excel data link. I'd rather just have one file.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[primuspaul]

I did some more work on it and now all of the "selection" fields are in MAILMERGE and the search is performed directly against CLIENTS and OWNERS instead of having an intermediate SELECTION sheet (which is now deleted). I also conveniently placed all of my dropdown fields on the MAILMERGE sheet on the left so I see what I need to choose.

I think this is adequate, especially since orphaned files (after location change) are fixed the first time you open the file in the new location (you just select the excel file).

However, I would still like to figure out how to:
1. Make the link to from the Word file to the Excel file (using mail merge feature) RELATIVE (.../file.xlsx) instead of ABSOLUTE (c:/documents/file.xlsx) to avoid the orphaning issue altogether.
2. I still have no idea how to pull an image out of excel for the owners' signatures and am stuck manually pasting them on.

 

[primuspaul]

Re: #2: I had some luck with 2 Ways to Batch Insert Multiple Pictures into Your Word Document via Mail Merge - Data Recovery Blog

However the picture comes out in a fixed size (dimensions) which don't make sense AND it doesn't seem to change when I make changes to the excel file.

 

[primuspaul]

I think I have a way of solving the image mailmerge issue, but only theoretically. If I add the signature images to a custom font, making each signature a letter (for example, owner John Smith would be "a" and owner Mary Jane would be "b"), I could just set those letters in the excel file and set the font of the field in Word to that font and it should work.

I just need to find out how to make a font and how to get Word to recognize the ttf file in the directory without needing to install it on the computer (to make the file work across different folders and on different computers out of the box).

Can someone give me some guidance?