vicC
07-13-2010, 12:36 PM
I work in health care environment so everything is very heavy with security. I am not able for what ever reason import data from a table off our intranet. I can view what I think is HTML code. I have copied and pasted the code for the table into notepad. than have copied text from notepad and this make my table very nice. But I have 44 tables that I would have to repeat this every week. I know VBA is very powerful. I can open web page with VBA.
1. Is it possible to have VBA Find the table ID in the source code and copy to text file and than back into wookbook.
2. I am I crazy, no don't answer that.
Here is some of the source code from web page.
<table id="tblMatrix" border="0" cellpadding="2" cellspacing="0" class="matrix">
<colgroup span="1" class="census"></colgroup>
<thead>
<tr>
<th class="census">Census</th>
<th id="thWeekday" colspan="5">Weekdays</th>
<th id="thWeekend" colspan="5">Weekends</th>
</tr>
<tr class="census">
<th class="census" style="border-top-style: none">Level</th>
<th>RN<input name="repeaterSkillWeekday$ctl00$txtId" type="hidden" id="repeaterSkillWeekday_ctl00_txtId" value="1" /></th>
<th>LVN<input name="repeaterSkillWeekday$ctl01$txtId" type="hidden" id="repeaterSkillWeekday_ctl01_txtId" value="2" /></th>
<th>CNA<input name="repeaterSkillWeekday$ctl02$txtId" type="hidden" id="repeaterSkillWeekday_ctl02_txtId" value="4103" /></th>
<th>SIT<input name="repeaterSkillWeekday$ctl03$txtId" type="hidden" id="repeaterSkillWeekday_ctl03_txtId" value="51" /></th>
<th>US<input name="repeaterSkillWeekday$ctl04$txtId" type="hidden" id="repeaterSkillWeekday_ctl04_txtId" value="4" /></th>
<th>RN</th>
<th>LVN</th>
<th>CNA</th>
<th>SIT</th>
<th>US</th>
</tr>
</thead>
<tbody id="bodyMatrix">
<tr>
<td class="census">0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">1</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">2</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">3</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">4</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">5</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
This is The code I have been trying. It of course came from the kb here on this site, sorry I do not know BY who. I edited website for security but I know you get the picture. There 130 rows of data in each of the tables if that matters. I will have to find some way of going to the next web page to get the next table. table Id is the same on each web page be cause you have to select things example 7Am shift.... but I can just go start to to webpage that holds each table. This is a Query and I have not been able to make it work just errors off with can not open information.
Sub gethtmltable()
Dim ie As Object
Dim objWeb As QueryTable
Dim sWebTable As String
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
With ie
.Visible = False
.Navigate "https://website.net/FacilityScheduler/NorthTexas/DepartmentMatrixView.aspx?id=4217" ' should work for any URL
Do Until .ReadyState = 4: DoEvents: Loop
'You have to count down the tables on the URL listed in your query
'This example shows how to retrieve the 2nd table from the web page.
sWebTable = 14
'Sets the url to run the query and the destination in the excel file
'You can change both to suit your needs
Set objWeb =
.QueryTables.Navigate("https://website.net/FacilityScheduler/NorthTexas/DepartmentMatrixView.aspx?id=4217", _
Destination:=Range("A1"))
With objWeb
.WebSelectionType = xlSpecifiedTables
.WebTables = sWebTable
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End With
Set objWeb = Nothing
End Sub
Thanks for any help, direction, input
and as always your time.
1. Is it possible to have VBA Find the table ID in the source code and copy to text file and than back into wookbook.
2. I am I crazy, no don't answer that.
Here is some of the source code from web page.
<table id="tblMatrix" border="0" cellpadding="2" cellspacing="0" class="matrix">
<colgroup span="1" class="census"></colgroup>
<thead>
<tr>
<th class="census">Census</th>
<th id="thWeekday" colspan="5">Weekdays</th>
<th id="thWeekend" colspan="5">Weekends</th>
</tr>
<tr class="census">
<th class="census" style="border-top-style: none">Level</th>
<th>RN<input name="repeaterSkillWeekday$ctl00$txtId" type="hidden" id="repeaterSkillWeekday_ctl00_txtId" value="1" /></th>
<th>LVN<input name="repeaterSkillWeekday$ctl01$txtId" type="hidden" id="repeaterSkillWeekday_ctl01_txtId" value="2" /></th>
<th>CNA<input name="repeaterSkillWeekday$ctl02$txtId" type="hidden" id="repeaterSkillWeekday_ctl02_txtId" value="4103" /></th>
<th>SIT<input name="repeaterSkillWeekday$ctl03$txtId" type="hidden" id="repeaterSkillWeekday_ctl03_txtId" value="51" /></th>
<th>US<input name="repeaterSkillWeekday$ctl04$txtId" type="hidden" id="repeaterSkillWeekday_ctl04_txtId" value="4" /></th>
<th>RN</th>
<th>LVN</th>
<th>CNA</th>
<th>SIT</th>
<th>US</th>
</tr>
</thead>
<tbody id="bodyMatrix">
<tr>
<td class="census">0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">1</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">2</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">3</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">4</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td class="census">5</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
This is The code I have been trying. It of course came from the kb here on this site, sorry I do not know BY who. I edited website for security but I know you get the picture. There 130 rows of data in each of the tables if that matters. I will have to find some way of going to the next web page to get the next table. table Id is the same on each web page be cause you have to select things example 7Am shift.... but I can just go start to to webpage that holds each table. This is a Query and I have not been able to make it work just errors off with can not open information.
Sub gethtmltable()
Dim ie As Object
Dim objWeb As QueryTable
Dim sWebTable As String
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
With ie
.Visible = False
.Navigate "https://website.net/FacilityScheduler/NorthTexas/DepartmentMatrixView.aspx?id=4217" ' should work for any URL
Do Until .ReadyState = 4: DoEvents: Loop
'You have to count down the tables on the URL listed in your query
'This example shows how to retrieve the 2nd table from the web page.
sWebTable = 14
'Sets the url to run the query and the destination in the excel file
'You can change both to suit your needs
Set objWeb =
.QueryTables.Navigate("https://website.net/FacilityScheduler/NorthTexas/DepartmentMatrixView.aspx?id=4217", _
Destination:=Range("A1"))
With objWeb
.WebSelectionType = xlSpecifiedTables
.WebTables = sWebTable
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End With
Set objWeb = Nothing
End Sub
Thanks for any help, direction, input
and as always your time.