excel with IE

[rocket0612]

apologies if this is in the correct forum, wasn't too sure.

I have some code which takes date from excel, then navigates through attachmate (my mainframe application) collecting details and now I want to populate an online form with the details.

The online form is a PDF file and I need to populate certain boxes. I have uploaded what it looks like.

I have no experience using VB to work with IE. my code so far to open the form is:

Dim ie As Object
    Set ie = CreateObject("Internetexplorer.Application")
    ie.Visible = True
    ie.Navigate "http://www.liquidoffice.pru.local/lfserver/45e2cfzfdb29d6b2dzx6b6010x224x1x223?DFS__Action=FormsGT&DFS__ShortID=13be052a79f1bzb10996d35ff4zx37cd10x231x240x20&3e5a91:10996d35ff4:-37a2"

Is it easy to now write code that will populate some of the boxes on the form I have uploaded or is it somthing you really need to have a fair bit of knowledge to do?

If it isn't too difficult, I would like to learn how to do this.

many thanks

[Static]

This would be better off in the General VB questions.. but thats fine.

anyway.. I tried to nav to that URL butit wouldnt come up.

Can u post URL source code? or is it a PDF online... if its a pdf file.. im not sure what can be done with that... is there even source code for it???

[rocket0612]

thanks for the reply.

The page needs a password to log-in, thus the reason you couldn't access it. I have uploaded what the form looks like, took a screenshot & pasted it to word.

Even though it is a PDF, it opens through IE. I'm not expereinced enough to know if you need IE code or PDF code, but would assume PDF code.

Unfortunately, I have only ever written code for office & attachmate, I know absolutely nothing when it comes to to PDF or IE.

The only code I have ever seen with regards to PDf was this:

[Static]

if u right click the page ... can u pick "view source" ? or is it a save as....
my guess would be PDF codes of some kind... best bet may be to use sendkeys and tab thru it.. but that should be a last resort....

I would need to see an example of the online doc to figure out more.. if u can find ANY pdf online with fields to fill in.. then I could check it out more.

[rocket0612]

thanks for the reply,

only thing I can find is on the page properties:

Protocol: HyperText Transfer Protocol
Type: Adobe Acrobat document
Connection: Not Encrypted

I have found a site which has a form in the same format:

http://www.nps.gov/renew/opm71.pdf

thanks alot

[Static]

Ok, it looks as if u MAY be able to access it the document thru code.. as far as how.. imi not sure yet... I would say first start digging on google.. look for stuff about accessing Acrobat thru VB. once u can figure that.. it shouldnt be hard to set the acrobat object to the webbrowser document..

I will see what I can find as well

[rocket0612]

thanks static, I found this that seems to use PDF:

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

and this which says uses SendKeys as an alternative:

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