mcs26
asked on
VBA SendKeys to Webpage
Hi,
I would like to know if the following is possible (its probably not but would like to automate this process). I load up the following webpage (http://www.dmo.gov.uk/chooseFormat.aspx?rptCode=D3B.2&page=Gilts/Daily_Prices). On that webpage there is a Continue button, I have to click this. Then another page loads up (you cannot set this as a link as it doesnt work) on this page I have to click the MS Excel button. This loads up an excel spreadsheet in a webpage. What I would like to do is have a macro that somehow loads this webpage up and can click this MS Excel button to & copy the spreadsheet into one of my excel files.
Any help once again would be great!
I would like to know if the following is possible (its probably not but would like to automate this process). I load up the following webpage (http://www.dmo.gov.uk/chooseFormat.aspx?rptCode=D3B.2&page=Gilts/Daily_Prices). On that webpage there is a Continue button, I have to click this. Then another page loads up (you cannot set this as a link as it doesnt work) on this page I have to click the MS Excel button. This loads up an excel spreadsheet in a webpage. What I would like to do is have a macro that somehow loads this webpage up and can click this MS Excel button to & copy the spreadsheet into one of my excel files.
Any help once again would be great!
ASKER
Hi Sedgwick
Yes that does happen sometimes might be better to use this link (http://www.dmo.gov.uk/index.aspx?page=Gilts/Daily_Prices) although now I have to click on "Latest available closing prices and yields" to get to the page I mentioned above. Is it possible to actually do this?
Thanks again,
Yes that does happen sometimes might be better to use this link (http://www.dmo.gov.uk/index.aspx?page=Gilts/Daily_Prices) although now I have to click on "Latest available closing prices and yields" to get to the page I mentioned above. Is it possible to actually do this?
Thanks again,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi John,
Thanks for replying. I have just tried to use your code with a few adjustments from the link you sent. It loads the webpage fine, however I am having a hard time telling if the SendKeys is actually doing anything or not?
Thanks for replying. I have just tried to use your code with a few adjustments from the link you sent. It loads the webpage fine, however I am having a hard time telling if the SendKeys is actually doing anything or not?
You should see SendKeys performing the actions on the screen. Make sure you adjust the Application.Wait to the appropriate amount of time. If IE isn't loaded completely SendKeys will perform it's task while IE is still loading. You can see in my code the first one only waits 4 seconds, but the second one waits 20 because I have to wait for the page to completely load. I'm guessing that's what is happening to yours because I had to make several adjustments to get the timing right.
Application.Wait Now + TimeValue("00:00:04")
Application.Wait Now + TimeValue("00:00:20")
Application.Wait Now + TimeValue("00:00:04")
Application.Wait Now + TimeValue("00:00:20")
ASKER
Yep, spot on my mistake! Thats great get excatly to where I want. The last problem is how do I copy the data from Excel spreadsheet in the webpage into my workbook?
Thanks again
Thanks again
An easier way would be to have it loaded into an Excel workbook. Go to Control Panel - Folder Options. On the File Type tab go find XLS (or XLSX if you're using 2007). Click on the Advanced button and uncheck the 'Browse In Same Window' box. This will make Excel open using Excel instead of IE.
Unless the other way is your preference. Let me know and we will figure out an alternative.
Unless the other way is your preference. Let me know and we will figure out an alternative.
see screenshot
Untitled.jpg