MrMarlon
01-30-2007, 05:51 PM
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.
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.