By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,478 Members | 1,833 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,478 IT Pros & Developers. It's quick & easy.

Parse from HTML to Access

P: 1
Hi, I found and adjusted some code (VBA) to parse an HTML file into an access table. It is working fine but the HTML has some information which is enclosed in <div> and therefore not part of the HTML table which I need to store in my access table. This is the code I have:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command5_Click()
  2.  
  3. Dim rawHtml As String, tableChunk As String, tempFile As String
  4.     Dim tmpAt As Long, tableStart As Long, tableEnd As Long
  5.  
  6.     rawHtml = GetPage(Me.webadd)
  7.  
  8.     ' Search forward until we're just before the table we want
  9.     tmpAt = InStr(1, rawHtml, "<div class=""reservation-info"">")
  10.     'tmpAt = InStr(tmpAt, rawHtml, "<div class=""tableSection"">")
  11.  
  12.     ' Get the index of the start of the opening <table> tag
  13.     tableStart = InStr(tmpAt, rawHtml, "<div class")
  14.  
  15.     ' Get the index of the end of the closing </table> tag
  16.     tmpAt = InStr(tableStart, rawHtml, "</table")
  17.     tableEnd = InStr(tmpAt, rawHtml, ">")
  18.  
  19.     ' Extract the table
  20.     tableChunk = Mid(rawHtml, tableStart, tableEnd - tableStart + 1)
  21.  
  22.     ' Use native VBA file I/O
  23.     tempFile = "C:\temp\tempTable.html"
  24.     Open tempFile For Output As #1
  25.     Write #1, tableChunk
  26.     Close #1
  27.  
  28.     ' Import the file to a table
  29.     DoCmd.TransferText acImportHTML, , "T_SQLTYPES", tempFile, True
  30. 'test from here
  31.  
  32. Dim ie As InternetExplorer
  33. 'to refer to the HTML document returned
  34. Dim html As HTMLDocument
  35. 'open Internet Explorer in memory, and go to website
  36. Set ie = New InternetExplorer
  37. ie.Visible = True
  38. ie.Navigate "file:///C:/temp/tempTable.html"
  39. 'show text of HTML document returned
  40. Set html = ie.Document
  41. 'close down IE and reset status bar
  42. Set ie = Nothing
  43. 'Application.StatusBar = ""
  44. Dim QuestionList As IHTMLElement
  45. Set QuestionList = html.getElementsByName("reservation-info")
  46.  
  47.  
  48.  
  49. Me.Text3.Value = QuestionList
  50. Me.Requery
  51. Me.Refresh
  52.  
  53.  
  54.     ' Delete the temp file
  55.     'Kill tempFile
  56.     DoCmd.OpenTable ("T_SQLTYPES")
  57. End Sub
  58.  
All I want for now is to display an item enclosed in <div> in my HTML on Text3 textbox in my form -or better still include it in my table T_SQLTYPES. Here is my HTML code:

Expand|Select|Wrap|Line Numbers
  1. "<div class=""reservation-info"">
  2.         Name of Hotel  #603439460
  3.     </div>
  4.  
  5. </div> <!-- /span12 -->
  6.  
  7. </div> <!-- /row -->
  8.  
  9.  
  10.  
  11.  
  12. <div class=""row-fluid"">
  13.  
  14. <div class=""span6"">
  15.  
  16. <table class=""table table-condensed table-striped table-hover table-bordered table-darkheader"">
  17.  
  18.     <tr>
  19.         <th colspan=""3"" class=""booking-room"">Room 1</th>
  20.     </tr>
  21.  
  22.  
  23.  
  24.     <tr>
  25.         <td><b>Guest name</b></td>
  26.         <td>
  27.             <strong>
  28.                 Mr XXX
  29.             </strong>
  30.         </td>
  31.     </tr>
  32.     <tr><td>Checkin</td><td>14-05-2014</td></tr>
  33.     <tr><td>Checkout</td><td>25-05-2014</td></tr>
  34.  
  35.     <tr>
  36.         <td>Room type</td>
  37.         <td>
  38.             Double or Twin Room - Single Use
  39.         </td>
  40.     </tr>
  41.  
  42.     <tr>
  43.         <td>Number of persons</td>
  44.         <td>
  45.             1 
  46.         </td>
  47.     </tr>
  48.  
  49.  
  50.  
  51.  
  52. <tr>
  53.     <td>Arrival</td>
  54.     <td>
  55.  
  56.         Wednesday, May 14, 2014
  57.  
  58.     </td>
  59. </tr>
  60. <tr>
  61.     <td>Departure</td>
  62.     <td>
  63.  
  64.         Sunday, May 25, 2014
  65.  
  66.     </td>
  67. </tr>
  68.  
  69.  
  70.  
  71.  
  72.  
  73.  
  74.     <tr>
  75.         <td>Number of nights</td>
  76.         <td>
  77.             11
  78.         </td>
  79.     </tr>
  80.  
  81.     <tr>
  82.         <td>
  83.             Total costs
  84.             (based on Single Use)
  85.         </td>
  86.         <td>
  87.             EUR xxx
  88.  
  89.  
  90.  
  91.             <br>7 % VAT is included.
  92.  
  93.  
  94.         </td>
  95.     </tr>
  96.  
  97.     <tr>
  98.         <td>Costs per night</td>
  99.         <td>
  100.         18.80
  101.         </td>
  102.     </tr>
  103.  
  104.  
  105.     <tr>
  106.         <td>Costs per night for more than 1 person</td>
  107.         <td>
  108.  
  109.         </td>
  110.     </tr>
  111.  
  112.  
  113.     <tr>
  114.         <td>Status</td>
  115.         <td>
  116.             ok
  117.  
  118.         </td>
  119.     </tr>
  120.  
  121.     <tr>
  122.         <td>Smoking preference</td>
  123.         <td>
  124.             non-smoking
  125.         </td>
  126.     </tr>
  127.  
  128.  
  129.  
  130.  
  131.  
  132.     <tr>
  133. <td>Applicable Cancellation Policy</td>
  134. <td>
  135.  
  136.  
  137. If cancelled or modified up to 3 days before date of arrival,  no fee will be charged.<br />
  138.  
  139. If cancelled or modified later or in case of no-show, 100 percent of the total price of the reservation will be charged.<br />
  140.  
  141.  
  142. </td>
  143. </tr>
  144.  
  145.     <tr>
  146. <td>Applicable Deposit Policy</td>
  147. <td>
  148.  
  149.  
  150. The total price of the reservation will be charged at least 2 days prior to arrival.<br />
  151.  
  152.  
  153. </td>
  154. </tr>
  155.  
  156.  
  157.  
  158.     <tr>
  159.     <td>Applicable Meal Plan</td>
  160.     <td>Breakfast is included in the room rate.</td>
  161. </tr>
  162.  
  163.  
  164. </table>"
  165.  
I would like <div class=""reservation-info"">
Name of Hotel #603439460
</div> to be displayed either on my form Text3 or better still included in the table I am importing into Access. Any help would be appreciated. Thanks
May 31 '14 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.