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!
mcs26Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Meir RivkinFull stack Software EngineerCommented:
the link seems to be invalid, i get "Data currently unavailable" in the webpage.
see screenshot
Untitled.jpg
0
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,
0
Brad Sims, CCNANetwork 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.
http://www.dmo.gov.uk/objectView.aspx?format=exceld&id=61009904&page=Gilts/Daily_Prices

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.



http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25118983.html
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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?
0
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")
0
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
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.