Switch the table to be imported

cknnugget

Board Regular
Joined
Jun 29, 2020
Messages
50
Office Version
  1. 365
Platform
  1. Windows
I am getting stock news data from a website and am changing the source dynamically by changing the ticker symbol entered in a cell. Depending on the ticker symbol, the table needed from the webpage may be table 0,1,2,3 or 4. If it is the wrong table, it errors. I added a way I can toggle between the Numbers to import the results "Tbl" but you have to manually change the cell value in excel and refresh until it no longer errors and imports the desired table.

Not sure how to do it but is there a way to write in the m code to automatically check each table until it finds the table without error? If then else?

Here are two links to try


Here is the code currently

"News" is linked to a cell to change the ticker symbol
"Tbl" is linked to a cell to change the table #

News=Excel.CurrentWorkbook(){[Name="News"]}[Content]{0}[Column1],
Tbl=Excel.CurrentWorkbook(){[Name="Table"]}[Content]{0}[Column1],
Source = Web.Page(Web.Contents(News)),
Data3 = Source{Tbl}[Data],
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Looks like there's news-table identifier in the Id column (at least there is for ICU and AMZN). If that's always the case then try:

Power Query:
News = Excel.CurrentWorkbook(){[Name="News"]}[Content]{0}[Column1],
Data3 = Web.Page(Web.Contents(News)){[Id = "news-table"]}[Data]

your Tbl and Source steps aren't needed for this approach
 
Upvote 0
Solution

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top