Macro Using PDF File 2

[JeaShe]

I wish to develop a macro that will exchange info with a mainframe application and an online fillable PDF file. Has anyone tried this? If so, how did it work for you?

 

[Dee0612]

would love to know this myself, tried researching this a while back but couldn't find the answer on any VB forum.

 

[JeaShe]

Drats! I thought someone had an answer! Still no info from Attachmate, though I've put in a call to them. Will share if I find something.

 

[JeaShe]

I understand it should work and we should treat it like any application by declaring objects, etc. Any examples someone would like to share? It's a little more than I know.

 

[Skie]

Do you have control of the PDF? If so, then you can put JavaScript within the PDF, have it run the script when the PDF opens, and populate it based on the command line.

function StartMe()
{
  var path = this.path;
  var fields;
  path = path.substring(path.indexOf("?")+1,path.length);
  fields = path.split("&");
  var i;
    for (i=0;i<2;i++)	{
      switch (fields[i].charAt(0))	{
        case 'A':
          var A = this.getField("A");
          A.value = fields[i].substring(2,fields[i].length);
          break;
        case 'B': 
          var B = this.getField("B")
          B.value = fields[i].substring(2,fields[i].length);
          break;
      }
    }  
  }
}

If not, I don't know if there's a non-sendkeys way to populate the pdf. But, you could populate it through internet explorer with sendkeys fairly easily. It's just not going to be 100% reliable.

 

[JeaShe]

I'm not very sophisticated code writer. I don't really know how to create the pdf object so that I can send keys, etc.

For Excel I usually do something like:

Set objExcel = GetObject(, "Excel.Application.9")
If objExcel is Nothing Then
Set objExcel = CreateObject("Excel.Application")
If objExcel is Nothing Then
Msgbox "Could not open Excel."
Stop
End If
End If

Then I open the spreadsheet with:

Excelpath = "Name of file"

Set filesys = CreateObject("Scripting.FileSystemObject")
Set objWorkBook = objExcel.Workbooks.Open(Excelpath )
Set filesys = Nothing
If objWorkBook is Nothing Then
MsgBox "Cannot open the Excel spreadsheet."
objExcel.Quit
Stop
End If

I need something like this that would create the pdf object.

 

[Skie]

Well, this is some code I found that uses Adobe objects. It might point you in the right direction.

Sub InsertFormCaptures()
'------ Written by Theo Callahan 4/2003 -------------

Const DOC_FOLDER As String = "C:\Documentation"

Dim objCurrent As AccessObject
Dim frmCurrent As Form
Dim strFormName As String
Dim blnNeedToClose As Boolean

Dim Acroapp As CAcroApp
Dim avCodeFile As CAcroAVDoc
Dim avFormCapture As CAcroAVDoc
Dim pdCodeFile As CAcroPDDoc
Dim pdFormCapture As CAcroPDDoc
Dim lngPage As Long
Dim AVPage As CAcroAVPageView
Dim PDPage As CAcroPDPage

'Start Acrobat in the background
Set Acroapp = CreateObject("AcroExch.App")

'Uncomment the following line if you want to watch the program run
Acroapp.Show

Set avCodeFile = CreateObject("AcroExch.AVDoc") 'This is the code file
Set avFormCapture = CreateObject("AcroExch.AVDoc") 'This will be each jpg in turn

'Open the already created code file
avCodeFile.Open DOC_FOLDER & "\CodeFile.pdf", "Code File"
Set pdCodeFile = avCodeFile.GetPDDoc

'Loop through each form. We have to use the AllForms collection because Forms
'only shows open forms. In order to document all of them, we loop through
'the AllForms collection and open forms as needed. They have to be open for
'us to check whether or not they have modules(associated code)

For Each objCurrent In CurrentProject.AllForms

'Open the form if it's not already open
If Not objCurrent.IsLoaded Then
blnNeedToClose = True 'This reminds us to close the form when done
DoCmd.OpenForm objCurrent.Name, acDesign, , , acFormPropertySettings, acWindowNormal
Set frmCurrent = Application.Screen.ActiveForm
strFormName = frmCurrent.Name
Else
blnNeedToClose = False
Set frmCurrent = Forms(objCurrent.Name)
strFormName = frmCurrent.Name
End If

'Open the jpg file
avFormCapture.Open DOC_FOLDER & "\" & strFormName & ".jpg", ""
Set pdFormCapture = avFormCapture.GetPDDoc

If frmCurrent.HasModule Then 'if there's code, look for the right spot

'Look for the form name and ' - 1' in the code file: that's page 1 of code
avCodeFile.FindText "Form_" & strFormName & " - 1", 0, 0, 1
Set AVPage = avCodeFile.GetAVPageView

'Go to the page just before the form's code
lngPage = AVPage.GetPageNum - 1 'we want page before
If lngPage < 0 Then lngPage = 0

Else
'If there's no code, throw the form in the back of the package
lngPage = pdCodeFile.GetNumPages - 1
End If

'Insert the jpg at the right page
pdCodeFile.InsertPages lngPage, pdFormCapture, 0, 1, 0

'Unfortunately, there is no page 0 so the first form comes AFTER the first
'page of code if it's on page 1. We need to swap the image and code if that's the
'case
If lngPage = 0 Then pdCodeFile.MovePage 1, 0

'Close the jpg file
pdFormCapture.Close
avFormCapture.Close 1
Set pdFormCapture = Nothing

'If we need to, close the form
If blnNeedToClose Then DoCmd.Close acForm, strFormName, acSaveNo

Next objCurrent

'close the doc file now with form captures
pdCodeFile.Close
avCodeFile.Close 0

'Exit Acrobat
Acroapp.Exit
Set objCurrent = Nothing
Set frmCurrent = Nothing
Set Acroapp = Nothing
Set avCodeFile = Nothing
Set pdCodeFile = Nothing
Set avFormCapture = Nothing

End Sub

If you'd rather just use SendKeys then.

Dim oIE as object
Set oIE = CreateObject("InternetExplorer.Application")
If oIE is Nothing then
  MsgBox "Could not create IE object"
  Stop
End If
URL = "[URL unfurl="true"]http://www.url.com/adobe.pdf"[/URL]
oIE.Navigate URL
While oIE.Busy
  DoEvents
Wend
oIE.Visible = true
SendKeys "Inputting to the Form",-1

 

[JeaShe]

I'll give this a try! Thanks!

 

[IknowMe]

More sample code

Set acr = CreateObject("Acroexch.app")
' acr.show
Set acrAVDoc = CreateObject("Acroexch.AVDoc")

s_AcrAVDoc = s_formsloc+"F8765.pdf"

acrAVDoc.open s_AcrAVDoc,"F8765"
set acrPDDoc = acrAVDoc.GetPDDoc

Set AcroForm = CreateObject("AFormaut.App")
Set FrmFields = AcroForm.fields

If F8765Dialog.checkbox1 = 1 then FrmFields("b1").value = "Yes"
If F8765Dialog.checkbox2 = 1 then FrmFields("b2").value = "Yes"
FrmFields("b3").value = F8765Dialog.Textbox1
FrmFields("b4").value = F8765Dialog.Textbox2
If F8765Dialog.checkbox3 = 1 then FrmFields("b5").value = "Yes"
FrmFields("b6").value = F8765Dialog.Textbox3
FrmFields("b7").value = F8765Dialog.Textbox4

Msgbox ("F8765 will now be printed."),0,"F8765"
iNumOfPages = AcrPDDoc.GetNumPages -1

acrAVDoc.PrintPages 0,iNumOfPages,3,false,true

acrAVDoc.close 1
acr.exit

Set Acr = nothing
Set acrAVDoc = nothing
Set AcroForm = nothing
Set AcrPDDoc = nothing
Set FrmFields = nothing

 

[JeaShe]

Ok, so does this presume you have a doc called "F8765.pdf"? If so, what is it's path? It doesn't seem to work for me and I'm thinking that is why.

 

[IknowMe]

s_AcrAVDoc = "yourpath/your.pdf"

I'm using

s_AcrAVDoc = s_formsloc+"F8765.pdf"

s_formsloc = "c:/blah/blah/"

Yes my pdf is called F8765.pdf and has a lot more fields than shown in the clip. I left a few their so you could see how I am populating the form.

user enters a value or checkbox on the dialog box(not included) then

(corresponding field in pdf) = (user input into dialog box)
FrmFields("b4").value = F8765Dialog.Textbox2

you'll need to change the variables to relate to your form

 

[JeaShe]

Ok, I'm trying it right now! Thanks

 

[JeaShe]

One more thing... what is the command to make the form visible? I can't seem to get it right. Thanks

 

[JeaShe]

Have you already established the connection of FrmFields("b3").value so that it knows what FrmFields is? When I use that command, it doesn't do anything as if it doesn't know what it is.

 

[Skie]

You need to use Acr.Show to make it visible. * to mgw for the code.

 

[JeaShe]

I'll work with this. Thanks so much Skie!

 

[IknowMe]

Set FrmFields = AcroForm.fields is setting the fields to object. You can get the field names for your form by using the "Form Tool (F)" button on your adobe toolbar. In the example I showed you my field titles were b1 ect. yours will be different for ex.

FrmFields("FillText25").value = "My Last Name"

Sorry for the late response, Holiday Lag. If you still have questions you know where to find us. Thanks for the star Skie.

 

[JeaShe]

I have some holiday lag too. Will try these suggestions soon. Thank you