Converting from: Send Key Event to: different event

[MrMarlon]

Hi Team, this is the first time I am posting since my first visit to this forum in 5 years. Always found what I needed by just browsing, and that's how I have been learning. I designed a macro by gathering information from few posts from here it is supose to go to a website and convert a set of coordenates to another system and copy and paste info in to active workbook. It always ran as expected. My Boss asked me to load this program on his computer (he loved what it was capable of doing). And that was my worse mistake, The code would do all but nothing that it was programed to do oon his computer. To make the story short, I almost got fired on the spot..
know I need help doing what I was always adviced to do: Avoid using the "Key send event". Need to convert the "Send Key" event part of it in a different way, perhaps by grabbing the form fields and populateing them, I am new to programmimg but I would hate to start looking for another job..PLEASE HELP :(
Code:
Sub ConvertToNAD27()
'reset query
On Error Resume Next
Range("A1").Select
'Dim IE As New InternetExplorer window
Dim ie As Object
Set ie = CreateObject("InternetExplorer.application")
'Make Internet Explorer visible and go to Website
Dim Lati As String
Dim Longi As String
Lati = ActiveSheet.Range("G14").Value
Longi = ActiveSheet.Range("G16").Value
'Application.WindowState = xlMinimized
Set ie = CreateObject("InternetExplorer.Application.vbMaximizedFocus")
ie.Visible = False
'Go to this Web Page!
ie.Navigate ngs.noaa.gov / cgi - bin / nadcon.prl
'Check for good connection to web page loop!
Do
If ie.ReadyState = 4 Then
ie.Visible = True
Exit Do
Else
DoEvents
End If
Loop
'Wait for window to open!
Application.Wait (Now + TimeValue("0:00:09"))
SendKeys "^{f}", True
Application.Wait (Now + TimeValue("0:00:04"))
mySendKeys "Select direction", True
mySendKeys "{TAB}", True
mySendKeys "{TAB}", True
mySendKeys "{TAB}", True
mySendKeys "{TAB}", True
mySendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:04"))
mySendKeys "{ESC}", True
mySendKeys "{TAB}", True
mySendKeys "{RIGHT}", True
mySendKeys "{TAB}", True
mySendKeys Lati, True
mySendKeys "{TAB}", True
myApplication.Wait (Now + TimeValue("0:00:04"))
mySendKeys Longi, True
mySendKeys "{TAB}", True
mySendKeys "{TAB}", True
Application.Wait (Now + TimeValue("0:00:01"))
'mySendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:07"))
' Select All Intenet Explorer data and then
ie.ExecWB 17, 0
'Copy to the clipboard
ie.ExecWB 12, 0
'Close Internet Explorer
ie.Visible = False
ie.Quit
Set ie = Nothing
Application.ScreenUpdating = False
Sheets("Profile").Select
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Range("A20").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Edit by Lucas: Added vba tags to your code for easier reading....select your code when posting and hit the vba button to enclose your code in vba tags.

[lucas]

So....MrMarlin, Your downloading from a website?
If its a table you should search the knowledgebase as I think there are some entries that address this.

[Charlize]

Why do you create two instances of internet explorer ? There are two statements withset ie = createobject(...)
I know that sendkeys exists as a command but mySendKeys not. Has your boss this routine on his computer ?

Charlize

[MrMarlon]

Lucas: is a simple form in a goverment public website that translates set of coordenates and returns the results I copy and paste on the activeworkbook, no tables.

Charlize: On my computer, when the frist intance of ie fires, it opens ie but does not set focus on it, on ly flashes on the task bar, and send key statements don't work. The second instance of ie does that, it forces focus to that window and maximize it.
mySendkey statement works as Send key statement, difference is mySend Key is just a modifyed version I found that trigers API calls instead of just VBA. I tryed both methods and none worked. :banghead:

[MrMarlon]

Lucas: is a simple form in a goverment public website that translates set of coordenates and returns the results I copy and paste on the activeworkbook, no tables.

Charlize: On my computer, when the frist intance of ie fires, it opens ie but does not set focus on it, on ly flashes on the task bar, and send key statements don't work. The second instance of ie does that, it forces focus to that window and maximize it.
mySendkey statement works as Send key statement, difference is mySend Key is just a modifyed version I found that trigers API calls instead of just VBA. I tryed both methods and none worked. :banghead:

[Norie]

MrMarlon

If you could refrain from cross-posting and answer the questions asked I think I could probably help.:)

I'll check back at MrExcel for the URL as it doesn't seem to have come across here.

[lucas]

MrMarlon,
If your cross posting please read this:
Click here for an explanation of cross-posting

http://www.excelguru.ca/node/7

[Charlize]

Values that you wanted to copy are being paste in a new workbook. From there you can modify the pasted values (stripping spaces, copying values ...). The letters to go to the paste menu in excel must be changed. If you got a dutch version, it will work. Otherwise change the w in e (edit) and p (for paste ?).
Sub ConvertToNAD27()
Dim Lati As String
Dim Longi As String
Dim sUrl As String
Dim ie As Object
Dim oDoc As Object
Dim ieForm As Variant
Dim wb As Workbook
On Error Resume Next
sUrl = "www.ngs.noaa.gov/cgi-bin/nadcon.prl (http://www.ngs.noaa.gov/cgi-bin/nadcon.prl)"
Sheets(1).Range("A1").Activate
Set ie = CreateObject("InternetExplorer.application")
Lati = Sheets(1).Range("B1").Value
Longi = Sheets(1).Range("B2").Value
ie.Visible = False
ie.Navigate sUrl
Do
If ie.ReadyState = 4 Then
ie.Visible = True
Exit Do
Else
DoEvents
End If
Loop
Set oDoc = ie.document
Set ieForm = oDoc.forms(0)
'Fill in the form with values of your sheet
ieForm(2).innertext = Lati
ieForm(3).innertext = Longi
ie.document.forms(0).submit

'Wait for window to open!
Application.Wait (Now + TimeValue("0:00:5"))
' Select All Intenet Explorer data and then
ie.ExecWB 17, 0
'Copy to the clipboard
ie.ExecWB 12, 0
'Close Internet Explorer
ie.Visible = False
ie.Quit
Set ie = Nothing
Application.Wait (Now + TimeValue("0:00:1"))
'Create new workbook where we are going to paste the clipboard
Set wb = Workbooks.Add
Application.Wait (Now + TimeValue("0:00:3"))
SendKeys "%"
Application.Wait (Now + TimeValue("0:00:1"))
'w and p must be changed to your local menu commands
'edit and paste (I think) so e and p
SendKeys "w"
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "p"
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "{UP}"
End Sub

Charlize

[Norie]

Charlize

This is a cross post (http://www.mrexcel.com/board2/viewtopic.php?t=255067).