Excel 스프레드 시트 셀 대신 VBA 변수로 웹 쿼리 데이터 가져 오기

debugcn 에 게시 Dev

클로스

Excel 스프레드 시트에서 웹 쿼리를 수행하고 싶습니다. 그러나 워크 시트에 데이터를 표시하고 싶지 않습니다. VBA 배열에 직접 저장하고 싶습니다.

다음은 ODBC 연결이있는 링크의 코딩 된 솔루션입니다. 이것을 웹 쿼리 솔루션에 적용하고 싶습니다. 수정 방법을 모르겠습니다.

Dim ws As Workspace, db As Database, rs As Recordset 
Dim sqlstr As String, ToolID As String 

Private Sub OpenODBC(ws As Workspace, db As Database, dsn As String, id  As String, pwd As String) 
  Dim dsnStr As String 
  Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC) 
  Workspaces.Append ws 
  ws.LoginTimeout = 300 
  dsnStr = "ODBC;DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd 
  Set db = ws.OpenConnection(dsn, dbDriverNoPrompt, False, dsnStr) 
  db.QueryTimeout = 1800 
End Sub 

Sub Tool() 

  On Error Goto errhandler: 

  Call OpenODBC(ws, db, "AC", "USERNAME", "PASSWORD") 

  sqlstr = "SELECT FHOPEHS.LOT_ID, FHOPEHS.TOOL_ID" & Chr(13) & "" & Chr(10) & "FROM DB2.FHOPEHS FHOPEHS" & Chr(13) & "" & Chr(10) & "WHERE (FHOPEHS.LOT_ID='NPCC1450.6H') AND (FHOPEHS.TOOL_ID Like 'WPTMZ%')" 

  Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot) 

  ToolID = rs("TOOL_ID") 

  Goto ending 

  errhandler: 
  If Err.Number = 1004 Then 
      Goto ending 
  End If 
  ending: 

  MsgBox ToolID 

End Sub

공유 할 외부 링크가 없습니다. 이것은 인트라넷이지만 결과를 워크 시트 셀 대신 배열에 저장하기 위해 수정하려는 코드는 아래에 있습니다. 아래 코드에서 대상은 셀입니다. 워크 시트의 "A1"

내가 게시 한 초기 예제는 " Set rs = db.OpenRecordset (sqlstr, dbOpenSnapshot) " 변수에 데이터를 직접 저장하는 방법을 보여줍니다 .

인터넷에서 찾은 다른 솔루션은 데이터를 워크 시트의 위치에 저장 한 다음 배열로 이동하여 워크 시트의 내용을 삭제하는 작업을 완료합니다. 이 절차를 수행하는 데 관심이 없으며 쿼리 결과에서 변수로 직접 이동하고 싶습니다.

    Sheets("Raw Data").Select

Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://myInternalAddress/myServerSideApp.php", Destination:=Range("A1"))
    .Name = "AcctQry"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

예상 결과는 이름 및 이니셜 목록입니다.

데이터를 스트리밍하는 PHP 코드는 다음과 같습니다.

    function getEngineers()
    {
        $sql = 'select `engname` as `name`, `engineer` as `initials` from `engineers`';
        if ( $result = $db->query($sql) )
        {
            if ($result->num_rows > 0)
            {
?>
                    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
                    <html lang="en">
                        <head></head>
                        <body>
                            <table>
                                <tbody>
<?php
                                    while ($n = $result->fetch_array()) 
                                    {
                                        echo '<tr><td>'.$n['name'].'</td><td>'.$n['initials'].'</td></tr>';
                                    }
?>
                                </tbody>
                            </table>

                        </body>
                    </html>
<?php
            }else{
                throw new Exception('No names returned');
            }
        }else{
            throw new Exception("Query to get engineer's names failed");
        }
    }

다음은 브라우저의 출력입니다. 기본적으로 두 개의 열이 있습니다. 1. 이름, 2. 이니셜

Ok 여기에 HTML 코드의 스크린 샷이 있습니다.

오메가 스트라이프

다음은 IE를 자동화하고 DOM에서 데이터를 검색하고 XHR을 만들고 응답을 구문 분석하는 방법을 보여주는 예입니다.

테스트 용 샘플은 다음과 같습니다.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html lang="en">
    <head></head>
    <body>
        <table>
            <tbody>
                <tr><td>Miggs, Thomas </td><td>TJM</td></tr>
                <tr><td>Nevis, Scott </td><td>SRN</td></tr>
                <tr><td>Swartz, Jeff </td><td>JRS</td></tr>
                <tr><td>Manner, Jack </td><td>JTM</td></tr>
                <tr><td>Muskey, Timothy </td><td>TMM</td></tr>
                <tr><td>Koeller, Steven </td><td>SRK</td></tr>
                <tr><td>Masters, Jeff </td><td>JLM</td></tr>
            </tbody>
        </table>
    </body>
</html>

디버그 목적으로 액세스 할 수 있도록 링크 로 배치했습니다 .

IE를 자동화하고 DOM에서 필요한 데이터를 검색하는 코드 :

Sub TestIE()

    Dim aRes As Variant
    Dim i As Long

    With CreateObject("InternetExplorer.Application")
        ' Make visible for debug
        .Visible = True
        ' Navigate to page
        .Navigate "https://pastebin.com/raw/YGiZ3tyk"
        ' Wait for IE ready
        Do While .ReadyState <> 4 Or .Busy
            DoEvents
        Loop
        ' Wait for document complete
        Do While .Document.ReadyState <> "complete"
            DoEvents
        Loop
        ' Wait for target table accessible
        Do While .Document.getElementsByTagName("table").Length = 0
            DoEvents
        Loop
        ' Process target table
        With .Document.getElementsByTagName("table")(0)
            ' Create 2d array
            ReDim aRes(1 To .Rows.Length, 1 To 2)
            ' Process each table row
            For i = 1 To .Rows.Length
                With .Rows(i - 1).Cells
                    ' Assign cells content to array elements
                    aRes(i, 1) = .Item(0).innerText
                    aRes(i, 2) = .Item(1).innerText
                End With
            Next
        End With
        .Quit
    End With

End Sub

XHR로 요청하고 RegEx로 응답을 구문 분석하는 코드 :

Sub TestXHR()

    Dim sRespText As String
    Dim aRes As Variant
    Dim i As Long

    With CreateObject("MSXML2.ServerXMLHttp")
        .Open "GET", "https://pastebin.com/raw/YGiZ3tyk", False
        .Send
        sRespText = .responseText
    End With

    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "<tr><td>([\s\S]*?)</td><td>([\s\S]*?)</td></tr>"
        ' Get matches collection
        With .Execute(sRespText)
            ' Create 2d array
            ReDim aRes(1 To .Count, 1 To 2)
            ' Process each match
            For i = 1 To .Count
                ' Assign submatches content to array elements
                With .Item(i - 1)
                    aRes(i, 1) = .SubMatches(0)
                    aRes(i, 2) = .SubMatches(1)
                End With
            Next
        End With
    End With

End Sub

두 방법 모두 aRes마지막 줄 중단 점의 배열에 동일한 결과를 제공합니다 .

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-06-9

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

Excel 스프레드 시트 셀 대신 VBA 변수로 웹 쿼리 데이터 가져 오기

Excel 스프레드 시트 셀 대신 VBA 변수로 웹 쿼리 데이터 가져 오기

코어 Java를 사용하여 Excel 시트에서 셀레늄 웹 드라이버로 데이터 가져 오기 문제

VBA를 사용하여 Google 스프레드 시트에서 Excel로 데이터를 가져 오는 방법

여러 내부 텍스트로 웹 페이지 Excel vba에서 데이터 가져 오기

Excel VBA로 웹 데이터 가져 오기

셀 값을 기준으로 액세스 데이터를 가져 오는 Microsoft Excel 파워 쿼리

웹 사이트에서 VBA로 데이터 가져 오기

Google 스프레드 시트 셀 데이터를 간단한 HTML 요소로 가져 오기

웹 사이트 채우기 양식 데이터를 스프레드 시트 또는 데이터베이스로 가져 오기

Postgresql 데이터베이스로 Excel 시트 가져 오기

VBA를 사용하여 특정 텍스트 파일을 Excel 스프레드 시트로 가져 오기

데이터 프레임을 Excel 시트로 내보내기 / 가져 오기

Excel-웹 사이트에서 Excel로 데이터 자동 가져 오기

셀 VBA Excel에서 명시 적 시트 하이퍼 링크 가져 오기

Excel VBA-인접 셀에 대한 업데이트를 기반으로 버튼 가시성 변경

Excel VBA-웹 스크랩 핑-HTML 표 셀에서 값 가져 오기

행 데이터를 기반으로 데이터 행을 다른 스프레드 시트로 정렬 Excel VBA

Excel 스프레드 시트를 배열로 가져 오기

스크립트 (seajs)로 작성된 웹 페이지에서 Excel로 데이터 가져 오기

Excel-텍스트가 일치하는 경우 모든 시트의 셀에서 데이터 가져 오기

CSV 데이터를 Google 스프레드 시트로 가져 오기

Excel 2016 : 다른 스프레드 시트에서 데이터를 가져 오는 수식

Google 스프레드 시트 쿼리-참조 셀을 사용하여 쿼리 데이터 범위 변경

쿼리 된 데이터로 시트 채우기 Google 스프레드 시트

쿼리 된 데이터로 시트 채우기 Google 스프레드 시트

Excel 스프레드 시트에서 VBA로 오류 읽기

데이터베이스에서 Excel 시트로 날짜 가져 오기

데이터를 Excel 스프레드 시트로 가져올 때 서식 제어

PHPExcel ...... 스프레드 시트가 다운로드 대신 웹 페이지에 표시됨

웹 앱을 통해 Excel을 Google 스프레드 시트로 가져 오는 방법