Hello,
Everyday, I have to grab a table from a website and paste it into excel. Sometimes this works by simple copy/paste and sometimes the table pastes all in one cell. When this is the case, I have to use the Get Data > From website tool. However, this becomes very time consuming for me. I was researching possible automatic solutions using VBA to help me out. However, all the sample codes I can find are all a bit different and I cannot figure out a way to get this working on my own.
The best I have been able to do so far is being able to open the website:
Sub GetDailyRates ()
Dim browser As InternetExplorer
Dim page as HTML Document
Set browser = New InternetExplorer
Browser.Visible = True
Browser.navigate ("https://... .com")
End sub
What I am ultimately hoping for is the ability to grab the table from the website (2nd image attached) and add it to the table I am using in Excel already (1st image attached). The website has two tables on it - but I just need to get the first one. The second is the legend and is not needed.
My current Excel table has filters to sort the extra data from the website table as well as some formatting (so the incoming data needs to take on the pre-established table formatting).
The website table only has the institution name in select cells. If possible, the cells below each need to be copied down until the next institution name. It is okay if the institution name is not filled into each cell as long as the cell comes in as actually blank (currently when it is grabbed via Get Data > website, it comes in as a non-blank cell (even though it looks blank).
The date column needs to be populated daily as well, however this is okay to do manually, especially if it causes difficulty doing it automatically.
The data from the website table needs to start in column B (through M) underneath of the current table entries and cannot include the headings.
Once the VBA code is created and working, I just need a simple way to run it daily by command (unless it is possible to have it run at say, 8:30am, every morning).
I thought this would be simpler than it is and I appreciate the help. Thank you so much!
(I have had to remove the names and cannot give out the website due to privacy, but I have done my best to, hopefully, illustrate with images.)
Everyday, I have to grab a table from a website and paste it into excel. Sometimes this works by simple copy/paste and sometimes the table pastes all in one cell. When this is the case, I have to use the Get Data > From website tool. However, this becomes very time consuming for me. I was researching possible automatic solutions using VBA to help me out. However, all the sample codes I can find are all a bit different and I cannot figure out a way to get this working on my own.
The best I have been able to do so far is being able to open the website:
Sub GetDailyRates ()
Dim browser As InternetExplorer
Dim page as HTML Document
Set browser = New InternetExplorer
Browser.Visible = True
Browser.navigate ("https://... .com")
End sub
What I am ultimately hoping for is the ability to grab the table from the website (2nd image attached) and add it to the table I am using in Excel already (1st image attached). The website has two tables on it - but I just need to get the first one. The second is the legend and is not needed.
My current Excel table has filters to sort the extra data from the website table as well as some formatting (so the incoming data needs to take on the pre-established table formatting).
The website table only has the institution name in select cells. If possible, the cells below each need to be copied down until the next institution name. It is okay if the institution name is not filled into each cell as long as the cell comes in as actually blank (currently when it is grabbed via Get Data > website, it comes in as a non-blank cell (even though it looks blank).
The date column needs to be populated daily as well, however this is okay to do manually, especially if it causes difficulty doing it automatically.
The data from the website table needs to start in column B (through M) underneath of the current table entries and cannot include the headings.
Once the VBA code is created and working, I just need a simple way to run it daily by command (unless it is possible to have it run at say, 8:30am, every morning).
I thought this would be simpler than it is and I appreciate the help. Thank you so much!
(I have had to remove the names and cannot give out the website due to privacy, but I have done my best to, hopefully, illustrate with images.)
