☰

vba - 워크시트를 엑셀로 변환하는 html 테이블

tag-icon • tag-icon microsoft-excel vba macros
vba - 워크시트를 엑셀로 변환하는 html 테이블

워크시트를 엑셀하기 위해 로컬 HTML 테이블 데이터를 추출할 수 있는 VBA 스크립트가 필요합니다. URL 링크를 사용하여 작동하는 코드(웹 어딘가에서 찾았습니다)가 있지만 내가 원하는 것은 로컬에 저장된 HTML 파일을 사용하여 코드를 수행할 수 있는 것입니다. 오류는 다음과 같습니다app defined or object defined error

Sub HTML_Table_To_Excel()

Dim htm As Object
Dim Tr As Object
Dim Td As Object
Dim Tab1 As Object


'Replace the URL of the webpage that you want to download
Web_URL = "http://espn.go.com/nba/"

'Create HTMLFile Object
Set HTML_Content = CreateObject("htmlfile")

'Get the WebPage Content to HTMLFile Object
With CreateObject("msxml2.xmlhttp")
.Open "GET", Web_URL, False
.send
HTML_Content.body.innerHTML = .responseText 'this is the highlighted part for the error
End With

Column_Num_To_Start = 1
iRow = 2
iCol = Column_Num_To_Start
iTable = 0

'Loop Through Each Table and Download it to Excel in Proper Format
For Each Tab1 In HTML_Content.getElementsByTagName("table")
With HTML_Content.getElementsByTagName("table")(iTable)
For Each Tr In .Rows
For Each Td In Tr.Cells
Sheets(1).Cells(iRow, iCol).Select
Sheets(1).Cells(iRow, iCol) = Td.innerText
iCol = iCol + 1
Next Td
iCol = Column_Num_To_Start
iRow = iRow + 1
Next Tr
End With

iTable = iTable + 1
iCol = Column_Num_To_Start
iRow = iRow + 1
Next Tab1

MsgBox "Process Completed"
End Sub

답변1

이번 주 초에 이 코드를 작성했습니다. 첫 번째 테이블을 검색하고 HTML 테이블에서 헤더를 제외한 모든 데이터를 A1에서 시작하는 활성 시트에 복사합니다.

첫 번째 따옴표 사이의 ie.navigate 줄 아래에 HTML 주소를 입력하세요.

Private Sub Test()

   Dim ie As Object, i As Long, strText As String

   Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object
   Dim tb As Object, bb As Object, tr As Object, td As Object

   Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

     Set wb = Excel.ActiveWorkbook
     Set ws = wb.ActiveSheet

     Set ie = CreateObject("InternetExplorer.Application")
     ie.Visible = True

      y = 1   'Column A in Excel
      z = 1   'Row 1 in Excel

     ie.navigate "http://", , , , "Content-Type: application/x-www-form-urlencoded" & vbCrLf

     Do While ie.busy: DoEvents: Loop
     Do While ie.ReadyState <> 4: DoEvents: Loop

     Set doc = ie.document
     Set hTable = doc.GetElementsByTagName("table")


     For Each tb In hTable

        Set hBody = tb.GetElementsByTagName("tbody")
        For Each bb In hBody

            Set hTR = bb.GetElementsByTagName("tr")
            For Each tr In hTR


                 Set hTD = tr.GetElementsByTagName("td")
                 y = 1 ' Resets back to column A
                 For Each td In hTD
                   ws.Cells(z, y).Value = td.innertext
                   y = y + 1
                 Next td
                 DoEvents
                 z = z + 1
            Next tr
            Exit For
        Next bb
    Exit For
  Next tb

End Sub

관련 정보