Ran into a Wall (on the road to ACA reporting)

I was so giddy with possible success that it was a shocking upset to see it fail.

Was I going to have to start all over?

[ap_divider color=”#CCCCCC” style=”double” thickness=”1px” width=”100%” mar_top=”20px” mar_bot=”20px”]

A couple of days ago I was working on creating a form in Word that looked like the IRS 1095-C form for mail merge with Excel. I was at a decent stopping point in my work on the ACA reporting, so I decided to tackle the problem of the IRS form because there was no way that I would be able to fill out 2100+ forms by hand. My idea was to mail merge Word with Excel. I first began by using the Microsoft snipping tool to snip various elements off of the pdf IRS form and pasting them onto a Word document. The idea was to paste the symbols and words from the IRS document as pictures onto Word and then the ensuing blank spaces would be used for filling in the information from Excel. This was a tedious process because I was trying to line up all of the shapes, pictures and words.

I did some testing by filling in the blanks manually (which was actually a blank rectangle shape) with the requisite information and found that the blank rectangles were fixed such that typed words went around the shape. I couldn’t type into the blank spaces. Okay, I needed to format the shape and I found a formatting type that allowed typed words to lie on top of the shape. (If you right-click on the shape or picture, you should get an option “Format shape”, or something like that, or if you see a picture of a half circle sitting within a series of lines, click on that to get formatting options. Try out the various options to see what they do.)

Ding ding ding ding! An epiphany!

If I can type over a shape which is essentially a picture, then I should be able to type over a picture of an IRS form. The trick would be the set up of lettering, spacing and lines but if I can cut down on the number of pictures to snip, paste and arrange on the Word document, I could save myself time.

So I did a single snip of the entire IRS form and pasted that picture into the Word document. That picture pasted beautifully without any need for adjusting size to fit the page. Then I found that my prior font selection, size and spacing worked well with the new pasted picture. I did have to adjust a few line spacing but not that much. (Font used was Arial and size was 6.)

So I was really giddy at the success of this solution that I felt I had the filling out of 2100+ forms nailed.

Until I tried a mail merge.

Words would then shift all over the page. Just because your mail merge field was centered in the correct blank box did not mean the field pulled from Excel would fall neatly into that box. It all depended on the length of the prior field. So if the prior field was 4 digits long, the next field would shift to the left. If the prior field was 15 digits long, then the next field shifted dramatically to the right.

And the numbers were worse. There were instances where you had to leave a box empty but if you did, the next field would shift to the left.

Everything was out of alignment! Neither the words nor the numbers would fall into the correct boxes or fall in place with the correct alignment. It didn’t look like mail merge was working.

Fortunately I had many ideas to try out in order to make this work (dashes instead of blanks for numbers, figuring out appropriate spacing for words, format codings for numbers, etc.) but none worked.

Finally, an idea popped into my head when I was driving home and my instincts were screaming this had to be it. First, for the numbers, you are probably better off converting the numbers into text in Excel. That way the formatting in Excel will roll over into Word. Then, set tabs in the Word document for each line. Just instituting tabs made everything fall into the correct box.

So, I learned a couple of new things: a) I can use pictures for forms and set the picture such that I can type words over the picture; b) for IRS forms, it looks like you can pretty much use Arial 6; c) for mail merge of numbers, your best bet is to convert the number into text in Excel first; and d) if you want words or numbers to align properly in Word, set up tabs to align the fields.