VBA SendKeys to Webpage


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!
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Brad Sims, CCNAConnect With a Mentor Network AdministratorCommented:
I had a previous question in regards to SendKeys and VBA here that might help you.  I have posted the link below.

An easier way is if this link will download a new file everyday you can use the code attached.

Keep in mind with SendKeys the number of actions must be the same each time, and you cannot use the keyboard while it is running.

Sub Daily_Prices()
Dim browser As Object

   Set browser = CreateObject("InternetExplorer.Application")
   With browser
   browser.Navigate ("http://www.dmo.gov.uk/objectView.aspx?format=exceld&id=61009904&page=Gilts/Daily_Prices")
    .StatusBar = False
    .Toolbar = True
    .Visible = True
    .Resizable = False
    .AddressBar = True
    End With

End Sub

Open in new window

Meir RivkinFull stack Software EngineerCommented:
the link seems to be invalid, i get "Data currently unavailable" in the webpage.
see screenshot
mcs26Author Commented:
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,
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

mcs26Author Commented:
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?
Brad Sims, CCNANetwork AdministratorCommented:
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")
mcs26Author Commented:
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
Brad Sims, CCNANetwork AdministratorCommented:
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.
All Courses

From novice to tech pro — start learning today.