Solved: Vba / Macro To Talk To Web Via Ie6

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
I am untrained (sloppy) but usually successful (eventually) macro programmer. My system includes XP Pro, Office 2003 Pro and IE6. I have automated most of my business spreadsheets, many of which use QueryTables to get data from the internet. I'm really stumped on this one, though:

I need to retrieve Sunrise / Sunset data for several US cities and paste it onto a worksheet. I don't think I can use QueryTable as the data is not directly accessable as a URL. In other words, I must enter information on one web page, and then that page calculates and displays the data as another page.

The following code works for me up to a point. I have commented my questions (problems) within the code. Please take a look and offer any suggestions you may have...short of "Don't give up my day job". In theory, this code should run on your computers.

Thanks, Tom

Sub Get_Sun_Data()

'Microsoft Internet Controls and Widows Scrpt Host Object Model
'references are on.

Dim IExp
Dim objWSS
Dim a, a3 As Variant

Set IExp = CreateObject("InternetExplorer.Application")
Set objWSS = CreateObject("WScript.Shell")

'the following line is commented for this example only:

'a3 = ActiveWorkbook.Name

IExp.Visible = True
IExp.Navigate "http://aa.usno.navy.mil/data/docs/RS_OneYear.html"
a4 = ActiveWindow.Caption
While IExp.Busy
Wend

'The following code works assuming it takes 11 tabs to get to the
'Sunrise/Sunset input box. Note I have Favorites on. This tab count
'will most likely be different on someone else's computer depending on
'their IE window configuation. I would prefer to go directly to this
'input box but I don't know how. Perhaps I could somehow monitor the
'the StatusBar as I tab through through the page to find a known
'refernce point on the page (not the window), and then tab from there.

'This code tabs over YEAR (2006), confirms SUNRISE/SUNSET in the next
'box, resets the STATE box to the top and then tabs down to North
'Carolina, inserts ROLESVILLE in the LOCATION box and presses ENTER.

objWSS.SendKeys "{Tab 11}", True
objWSS.SendKeys "{HOME}", True
objWSS.SendKeys "{Tab 1}", True
objWSS.SendKeys "{HOME}", True
objWSS.SendKeys "{DOWN 35}", True
objWSS.SendKeys "{Tab 1}", True
objWSS.SendKeys "ROLESVILLE", True
objWSS.SendKeys "{ENTER}", True
While IExp.Busy
Wend

Stop

'There should now be a Table of Sunrise/Sunset times displayed. I need
'to copy this table to the workbook named a3 (commented out above).
'I can use the keyboard CTRL A to highlight the data, CTRL C to copy
'the data to the clipboard and CTRL V to paste it into the workbook.
'I cannot get SendKeyes to do this, though.

'In case there is a timing issue, I have tried this 10 second delay:
Timer1 = Now() + (10 / 86400)
Do While Timer1 > Now()
Loop

'I have tried several variations on the following to get the data to
'the clipboard with no success:
objWSS.SendKeys "^A", True
objWSS.SendKeys "^C", True

'The following is the final step to paste the data:
'Windows(a3).Activate
'Sheets("T 1").Cells(1, 13).Select
'ActiveSheet.Paste


End Sub
 
Joined
Aug 31, 2005
Messages
2
http://aa.usno.navy.mil/cgi-bin/aa_rstablew.pl?FFX=1&xxy=2006&st=NC&place=ROLESVILLE&ZZZ=END

Use this URL (all one line) and go straight to the data - no need to use send keys to fill in the form!
This is all that the form does, collects the input and submits it to a perl script.

I think that I would scrap the copy/paste idea and instead save the page to a temporary text file and parse the data into the spreadsheet from there.

Good Luck!

Ken
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top