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)
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"):
On worksheet "contracts" A1 i have contract numbers as string. It is same number in worksheet "fetched" second row on each column.
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:
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.