Trying to open a website using Workbooks.Open Filename:= fails- gives back page source

[chiuauaua]

Hi,

This is my first post and hopefully a quite easy problem to answer
I am trying to get excel to open a certain webpage using:

Please Login or Register  to view this content.

What i get in return can be seen on a screenshot below. Is there a way around this to be able to import this certain website using VBA?

Thank you in advance

image001.jpg

[Kyle123]

Have you tried a web query?

Or maybe a hyperlink, I'm not really sure what you're trying to do

[chiuauaua]

Thank you for quick reply.

What do you mean by a web query?

What I'm trying to do is for VBA macro to copy the web page content into excel. Instead of that, I am getting the web page source in excel. My guess is (cause some pages copy correctly) that this page has some script or css or something that excel does not recognise and can't convert as i am using an Workbooks.Open Filename:=

Maybe there is a command that let's excel open a web page?

If you try to run this macro you should get the same problem, though if you run it on a simpler page, it might copy the content to excel correctly

Thanks in advance

[Kyle123]

Why don;t you use a web query? That's what they are designed for

[chiuauaua]

I need to pull the data from web through VBA, as this is only a part of the macro (i have multiple sites to draw data from, and then it is processed) and i would like to be able to do this with one click. Web query is an excel function and i don't think there is a way to use it directly with a command from VBA, is there?

Any ideas?

Thanks

[Kyle123]

Yes, you can use a webquery from vba. You can generally do anything in VBA that you can do in Excel, try recording a macro whilst you do a we-query and adapt as necessary

[chiuauaua]

Thank you for your reply,

After trying to do it your way and hours of searching through the net i think this has something to do with that it's a page written in PHP, so excel can see the page but cannot grab from the php app.

on the page here: http://www.gpw.pl/portfele_indeksow/...95&lang=PL you can see that there are vertical buttons that change the content of the page. What i need is to be able to get the chart from below the button (in each "chapter") Do you know a way around excel not importing php content?

Thank you

[Kyle123]

Ok, the problem isn't that the page is written in php (90% of all web pages are), it's that the page loads its data through AJAX, put simply, the data isn't in the source code of the web page. This means that you can't get the data through the normal routes - WebQuery etc.

To cut a long story short, it's not going be easy to get the data out of the page. The easiest way is probably to start automating internet explorer, so you control internet explorer through excel and return the data - it is very slow though, but it will do what you want

Have a google around, there are loads of examples of controlling internet explorer with excel. When you get stuck, post back and we can help you out

[Kyle123]

Here's something to get you going, it doesn't use ie so it's fast, you'll need to work out which page you want to query however:

Please Login or Register  to view this content.

[chiuauaua]

Well at least i know what to look for thank you for pointing me in the right direction, hopefully i can find a way around this with your solution

Cheers

PS Thanks for posting this sample script, this will be much easier now

[Kyle123]

having tested it a bit more, my function will return data from all the tabs. It seems that the only thing that changes in the request is the long number following PL

I don't really know how you'd find out which number to request, but the code will work for them all.

eg: try changing the url in my code to "http://www.gpw.pl/ajaxindex.php?action=GPWPortfele&start=listForIndex&isin=PL9999999912&lang=PL"

It rerurns the table from mWIG40 Tab to sheet1. Only the number has changed.

Thanks for the feedback and rep

[chiuauaua]

Hi again,

I had some time to play with the macro, though when i paste your text into a clean macro i get an error:

Run-time error 91

Object variable or With block variable not set


When i hit debug it stays on the following line:
For Each tr In table.Rows

Any ideas?

[Kyle123]

Ok, 2 questions:

1. Have you changed any of the code at all?
2. Which version of IE do you have installed?

[chiuauaua]

nice guess

I was testing it on an old xp machine with only IE6. On my computer (xp IE8) it works like a charm, later I will have to test it under win7 (ie9 i think) but I suppose it should work too

Thank you

[Kyle123]

I'm running Win7/IE9 so it works there too