0

I have checked few related posts questions in the form before (Q1, Q2, and Q3) However, these my question is a bit different than those, even though they guided me up to where I am now.

I have a php page with form where I can input "contract_array", "start_date", and "end_date". And when I click "See Data" it will bring me a table like picture below. (table thead tbody) enter image description here

What I want is to get data of this output table tbody part and insert it to my excel template by matching correct date. Here is my excel template looks like (worksheet "fetched"): enter image description here

On worksheet "contracts" A1 i have contract numbers as string. It is same number in worksheet "fetched" second row on each column. enter image description here

Here is my VBA code:

Sub GetCabinet2DataUzex()
    Dim Request As New MSXML2.ServerXMLHTTP60
    Dim ReqURL As String
    Dim body As String
    Dim contract_array As String
    Dim start_date As String
    Dim end_date As String
    Dim Response As String
    Dim html As New HTMLDocument
    Set wsfetched = ThisWorkbook.Worksheets("fetched")
    Set wscontracts = ThisWorkbook.Worksheets("contracts")
    
    contract_array = wscontracts.Range("A1").Value
    start_date = wsfetched.Range("A4").Value
    end_date = wsfetched.Range("A1000000").End(xlUp).Value
    
    body = "contract_array=" + contract_array + "&start_date=" + start_date + "&end_date=" + end_date + "&dosubmit=1&see_data=1"
 
    ReqURL = "https://e-dostluk.com/deneme/test-project2.php"
    
    With Request
        .Open "POST", ReqURL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .Send (body)
        html.body.innerHTML = .responseText
        Response = .responseText
    End With
    
    Set tbody = html.getElementsByTagName("tbody")
    
    Column_Num_To_Start = 2
    iRow = 4
    iCol = Column_Num_To_Start
    
    With tbody
        For Each tr In .Rows
        For Each td In tr.Cells
            wsfetched.Cells(iRow, iCol).Select
            wsfetched.Cells(iRow, iCol) = td.innerText
            iCol = iCol + 1
            Next td
            iCol = Column_Num_To_Start
            iRow = iRow + 1
        Next tr
    End With
    
    If Request.Status = "200" Then 'success
        MsgBox "Data updated successfully"
    Else
        MsgBox "OPS! Something went wrong."
    End If
End Sub

I assigned this VBA code on button as macro. When I click it give an error "System error &H80072F7D (-2147012739)" as below: enter image description here

Not sure if my VBA code has mistake or the error is caused by another thing. I am stuck here. So, how can i get this VBA code working ? I am also aware that I have not integrated date match if condition in above VBA for now. If you can give me advice for it, i will appreciate.

esqeudero
  • 77
  • 1
  • 8
  • 1) Could we also have the contracts tab cell A1 value inserted as a string into the post via [edit]? 2) Should this `start_date = wsfetched.Range("B4")` be `start_date = wsfetched.Range("A4")` ? 3) What is your set-up? OS versioning and Excel? I can run a minimized version of your code and get a result. – QHarr Dec 27 '21 at 07:57
  • One possibility is TLS version and Excel version incompatibility. – QHarr Dec 27 '21 at 08:05
  • yes it is start_date = wsfetched.Range("A4"), i did typo here. but the error is same – esqeudero Dec 27 '21 at 08:26
  • i am using win7 and excel 2010 – esqeudero Dec 27 '21 at 08:27
  • Windows 7 and Server 2008 R2 support TLS 1.1 and 1.2, but it is disabled by default. – QHarr Dec 27 '21 at 08:36
  • i check my OS it is win7 home premium service pack 1. @QHarr can you guide me to enabled TLS support. – esqeudero Dec 27 '21 at 08:40
  • Did you try with WinHttp.WinHttpRequest.5.1 (I haven't research enough whether this side-steps the OS issue) Also, see https://stackoverflow.com/questions/58223243/does-msxml2-xmlhttp-object-support-tls-1-2-vba-excel – QHarr Dec 27 '21 at 08:40
  • But first look at this: https://www.wikihow.tech/Check-the-TLS-Version-on-a-Website This server supports TLS 1.3. This site works only in browsers with SNI support. – QHarr Dec 27 '21 at 08:43

0 Answers0