Filling tax forms from Excel

New Here ,
Mar 22, 2021 Mar 22, 2021

Copy link to clipboard

Copied

I'm using the Excel VBA SendKeys method to directly complete UK tax forms from data within Excel. This sends the required data field-by-field with tabs to move to the next field.

This works OK using Adobe Reader but is slow as: (1) need a 1 second delay between each SendKeys call; (2) having to tab over irrelevant fields to get to the next relevant field - can be over 100 tabs.

My question is: if I use Adobe Pro instead of Reader, will I be able to find the form field names and go directly to the relevant ones (a) still using SendKeys or (b) an alternative method within Excel?

TOPICS
PDF forms

Views

186

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Mar 22, 2021 Mar 22, 2021

Copy link to clipboard

Copied

(b) you can use the VB-Javascript interface of Acrobat.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Most Valuable Participant ,
Mar 22, 2021 Mar 22, 2021

Copy link to clipboard

Copied

We can't answer those questions without seeing the PDF file. If it's a normal PDF form then you should be able to do it, yes, but if it's a LiveCycle Designer form (which many official forms are) then no, or at least, it will be much more difficult.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Mar 23, 2021 Mar 23, 2021

Copy link to clipboard

Copied

Here's a link to form IHT407 Household and personal goods - Schedule IHT407 (publishing.service.gov.uk)

Is it possible to say whether this is a LiveCycle form?

This is actually the most annoying form as after filling in the 1st page, numerous tabs are needed to bypass the Jewellery, Vintage Cars and Antiques sections, none of which I have, to get to the only box that needs entered on the last page. This isn't a problem when completing directly but is from Excel.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Most Valuable Participant ,
Mar 23, 2021 Mar 23, 2021

Copy link to clipboard

Copied

It's not an LCD form, so that's good. And it's also not secured, which is even better.

So the road is clear to importing data into it. I don't see why you would need to use VBA, though.

All you have to do is format the data in the correct format and then you could import it directly.

Doing it in Reader is a bit tricky, but it's not impossible. You can use this (paid-for) script I've created to both export the form field names in the correct format to a plain-text tab-delimited text file, edit that file in Excel and then import the data back into the form: https://www.try67.com/tool/adobe-reader-import-export-pdf-form-data

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 02, 2021 Apr 02, 2021

Copy link to clipboard

Copied

LATEST

My one-stop solution within Excel:

(1) installed Acrobat Pro;

(2) Prepare Forms - Export Data to fdf format for previously completed forms; need only do this once; 

(3) In Excel VBA, edited the fdf file to update previous values, then :

WaitTime = 0.000075
Dim pid As Variant
pid = Shell("C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Acrobat.exe " & FDFname)
Application.Wait Now + WaitTime
Application.SendKeys "+^s", True: Application.Wait Now + WaitTime 'Shift + Control + S
Application.SendKeys "~", True: Application.Wait Now + WaitTime 'Enter
Application.SendKeys PDFname, True: Application.Wait Now + WaitTime 'New filename
Application.SendKeys "%s", True: Application.Wait Now + WaitTime 'Save file
Application.SendKeys "^q", True: Application.Wait Now + WaitTime 'Exit Acrobat

(4) Repeat (3) for each of the 12 forms.

This has reduced the previous solution using Reader from 45 to 5 minutes which would be a fraction of this but for need for an approx 7.5 seconds delay between SendKeys statements to allow Acrobat to process the keystrokes. Had looked at Action Wizard to auomatically save files but seems this needs to be invoked manually which defeats objective of one-stop solution. Receptive to suggestions here.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines