# Unable to grab table using GetData>FromWeb; can see in web view only



## doogis2003 (Jun 2, 2020)

Hello,

I tried using the Get Data > From Web feature to grab an exchange rate table from the following site: Rates Page

There isn't any table data in Table View but I can see it in Web View with the message "Table highlighting is disabled because the page uses Internet Explorer's Compatibility Mode.  I'm realizing that the way the website generates this table means I can't use the Get Data > From Web method.  

Is there a workaround to copy this table data using VBA or another method?

Thank you,

AC


----------



## Worf (Jun 4, 2020)

Welcome

You could use Power Query, see picture below:


----------



## doogis2003 (Jun 4, 2020)

Worf, 

Thank you for replying to my message.

I opened the Power Query Editor and selected "New Source" directing it to the same URL as before: Rates Page

I'm still not able to see the table from that page.  What I get is pictured below.  How were you able to work around this in your example above?  Note I am able to retrieve table data from a different exchange rates page though.  

Thank you,

AC


----------



## doogis2003 (Jun 4, 2020)

Once accepting "document" as the source, I dug into the HTML & Table to try to find the data but was only able to get to the 6 table headings but couldn't find the data.


----------



## doogis2003 (Jun 5, 2020)

I managed to get as far as here:


----------



## sandy666 (Jun 5, 2020)

try shortcut: Alt A PN O W
then paste url and ok

you should see this first



so select this then hit edit to see this




example file:








						s666-PQ-fromweb.xlsx
					

Microsoft Excel Workbook



					1drv.ms


----------



## doogis2003 (Jun 5, 2020)

Sandy666,

Tried your suggestion but after pasting the URL, I don't see the line "Rates updated on M/DD/YY at <time>".  I still only see "Document"  Tried both http & https.  Is there an Excel setting that could be causing this?


----------



## sandy666 (Jun 5, 2020)

to be sure, are you trying this?


----------



## doogis2003 (Jun 5, 2020)

Yes, exactly that.


----------



## doogis2003 (Jun 5, 2020)

Sandy666, these are the steps I am trying:


----------



## doogis2003 (Jun 2, 2020)

Hello,

I tried using the Get Data > From Web feature to grab an exchange rate table from the following site: Rates Page

There isn't any table data in Table View but I can see it in Web View with the message "Table highlighting is disabled because the page uses Internet Explorer's Compatibility Mode.  I'm realizing that the way the website generates this table means I can't use the Get Data > From Web method.  

Is there a workaround to copy this table data using VBA or another method?

Thank you,

AC


----------



## sandy666 (Jun 5, 2020)

hm, try to update your office
or try this link as test: `https://en.wikipedia.org/wiki/List_of_circulating_currencies`

did you try to refresh previous link?





on the end try to select this and if it is works - Edit


----------



## doogis2003 (Jun 5, 2020)

The test on the wikipedia link is successful which matches my previous success with another currency exchange site.  There's something about the currencywholesale one that behaves differently...

Tried refreshing previous link multiple times, no luck.  

Office is up to date (Microsoft 365 subscription w/ auto-update)


----------



## sandy666 (Jun 5, 2020)

did you try last option from my previous post?


----------



## doogis2003 (Jun 5, 2020)

The last option picture below.  I don't have an 'edit' option though but have shown what I do have:





Using the highlighted above, 'Transform Data' results in this:


----------



## sandy666 (Jun 5, 2020)

to eliminate problem with your Excel OR with the link you can try Power BI Desktop
to me this link works, another links too 

also you can try advanced option but no big hope


----------



## doogis2003 (Jun 5, 2020)

Odd... this is what I get with (fresh install of) Power BI:





Trying 'Add table using examples' doesn't seem to work either - can see the table but can't select it.


----------



## sandy666 (Jun 5, 2020)

PowerBI with Get Data - from Web




then


----------



## doogis2003 (Jun 5, 2020)

Interesting...other than software version, what else could cause such a difference in what we each see given the exact same URL?  I'm thinking a setting somewhere but no idea what.  Both of my programs don't seem to identify the table the same way yours do.


----------



## sandy666 (Jun 5, 2020)

maybe _rates.currencywholesale.com _doesn't like your domain, who knows?   

have you tried another table from _rates.currencywholesale.com _?

how about example excel file I posted, it works or not? (post#6)


----------



## doogis2003 (Jun 5, 2020)

The example excel file ends up the same.  Can see the connection you made but when I open it there's no data there.  Looks same as if I try to generate it from scratch.





You might be onto something with the domain.  Is that actually possible?

May be coincidence but it's the only condition that was changed - turned on VPN and went back to Power BI and was able to get to this point using exact same steps as before (Get Data > from Web > same URL):



 







Everything looks fine BUT.... on a refresh, reverts back to 





Also, no change in Excel.  I still can't get it to show me the table I need "Rates updated...."  Can a website be blocking me from seeing the data?

Not seeing another table to use as an example on the currencywholesale site.


----------



## doogis2003 (Jun 2, 2020)

Hello,

I tried using the Get Data > From Web feature to grab an exchange rate table from the following site: Rates Page

There isn't any table data in Table View but I can see it in Web View with the message "Table highlighting is disabled because the page uses Internet Explorer's Compatibility Mode.  I'm realizing that the way the website generates this table means I can't use the Get Data > From Web method.  

Is there a workaround to copy this table data using VBA or another method?

Thank you,

AC


----------



## sandy666 (Jun 5, 2020)

how excel works with/without vpn?
btw. i have no vpn and all works



doogis2003 said:


> Can a website be blocking me from seeing the data?


you need to ask owner of this site
but you see data in browser


----------



## doogis2003 (Jun 5, 2020)

To elaborate on Power BI trial, I am in Canada.  With VPN on, server through USA I can refresh data.  I drop the VPN, refresh and data disappears.  Either way, the VPN/region does not seem to have any impact on Excel which is where I need to pull the data to.

Do you think the region change effect on Power BI is purely coincidence or a symptom of the issue?


----------



## sandy666 (Jun 5, 2020)

If there is any chance change region to, eg Mexico or Germany?


----------



## doogis2003 (Jun 5, 2020)

Mexico is working similar to US; Germany is a no similar to Canada (for Power BI)

Still no effect on Excel


----------



## sandy666 (Jun 5, 2020)

you've two options:
- owner of this site
- microsoft excel help


----------



## doogis2003 (Jun 5, 2020)

Sandy666, thanks for all your help so far!

If anyone else has any ideas, would be appreciated!


----------



## sandy666 (Jun 5, 2020)

You are welcome


----------



## Worf (Jun 5, 2020)

This worked for me:


```
Public d As ChromeDriver

Sub withC()
Dim tb As Object
Set d = New ChromeDriver
d.get "https://rates.currencywholesale.com/rates/rates.html"
Set tb = d.FindElementByXPath("//*[@id=""currency_table""]")
d.Wait 2000
tb.AsTable.ToExcel ThisWorkbook.Worksheets("Sheet1").[a1]
End Sub
```


----------



## doogis2003 (Jun 5, 2020)

Thanks Worf, I'll give that a try.  Do I have to install an extension for ChromeDriver?


----------



## Worf (Jun 5, 2020)

Yes, see post #4 of this thread:

import data from Web


----------

