Participant

Question

Filling tax forms from Excel

Forum|Forum|5 years ago
March 22, 2021
3 replies
1318 views

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?

PDF forms

This topic has been closed for replies.

E

ExcelUserAuthor

Participant

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.

try67

Community Expert

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.

E

ExcelUserAuthor

Participant

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.

try67

Community Expert

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

Bernd Alheit

Community Expert

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded