0

Hi: I'm using excel to get values from this webpage: https://www2.agenciatributaria.gob.es/es13/h/iexmmmfi.html

How can I get the code for excel VBA to get the fiels for NIF, EJF, MOD and CEL?

I tried with getelementbyid("NIF") and "name" but with no results

Thanks!

Previos thread: Excel VBA: Get inner text of HTML table td

This is the code I use:

Sub AEAT()
  Dim IE As Object
  Application.ScreenUpdating = False
  Set IE = CreateObject("InternetExplorer.Application")

  IE.Navigate "https://www2.agenciatributaria.gob.es/es13/h/iexmmmfi.html"

  Application.Wait (Now + TimeValue("0:00:02"))


  IE.Document.getElementById("NIF").Value = Range("A1").Value
  Application.Wait (Now + TimeValue("0:00:01"))

  IE.Document.getElementById("EJF").Value = "2016"
  Application.Wait (Now + TimeValue("0:00:01"))

  IE.Document.getElementById("MOD").Value = "347"
  Application.Wait (Now + TimeValue("0:00:01"))

  IE.Document.getElementById("CEL").Value = Range("a4").Value
  Application.Wait (Now + TimeValue("0:00:01"))

  IE.Document.getElementById("env_button").Click

End Sub
Community
  • 1
  • 1
G. Asins
  • 69
  • 7
  • 1
    Post the code you have used – Tarun Lalwani Aug 19 '17 at 07:14
  • Code added. Thanks! – G. Asins Aug 20 '17 at 07:54
  • Your problem may be more fundamental. Try opening the browser with this URL, open the console (usually by pressing F12) and write `document.getElementById('NIF')`. Do you get some kind of result? Once you can successfully use the DOM outside of VBA, converting the code within VBA is relatively trivial. – Zev Spitz Aug 20 '17 at 08:46
  • Also, I would strongly suggest including the HTML you are trying to parse. In my case, I am running a browser without Java, and so cannot see the webpage you are trying to parse. – Zev Spitz Aug 20 '17 at 08:53
  • ok, thanks. I'll do it – G. Asins Aug 20 '17 at 09:34

1 Answers1

0

Okie, so I don't have a Excel in Windows VM, so I tested your code using VBScript. So the solution should work. You have 3 problems

  1. Navigate doesn't wait for Page for to load
  2. Your site installs a ActiveX control, which needs to be loaded first
  3. Even when you fix #1 and #2, you will get an exception "The client disconnected" and the object will not be usable anymore

Solution

Add the site to Trusted Sites in your IE Setting.

IE Settings

Open IE manually and install the ActiveX control

Sub AEAT()
  Dim IE
  Set IE = CreateObject("InternetExplorer.Application")
  IE.visible = True
  hwnd = ie.hwnd
  IE.Navigate "https://www2.agenciatributaria.gob.es/es13/h/iexmmmfi.html"
  msgbox ("wait")

  Set oShell = CreateObject("Shell.Application")

  For Each Wnd In oShell.Windows
         If hwnd = Wnd.hwnd Then Set ie = Wnd
  Next 
  IE.Document.getElementById("NIF").Value = "123"
End Sub

Call AEAT

I have put msgbox to insert wait for now, but you can fix that yourself by using below in excel

  Do
    DoEvents
  Loop Until ie.ReadyState = READYSTATE_COMPLETE

PS: reference from below URLs

Internet Explorer VBA Automation Error: The object Invoked has disconnected from its clients

Error "The object invoked has disconnected from its clients" - automate IE 8 with python and win32com

Tarun Lalwani
  • 142,312
  • 9
  • 204
  • 265
  • Great! Imany thanks! But i still have a problem with the field "MOD" or "ID_MOD", it doesn't work neither by using get elementbyID or byname. – G. Asins Aug 20 '17 at 10:11
  • MOD only has name so you need to use GetElementsByName, which returns an array not a object. So you need to use `document.getElementsByName("MOD")[0].value = "038"` – Tarun Lalwani Aug 20 '17 at 10:17
  • Sorry, it returns me "syntax error" in excel VBA. getElementsByName("MOD")[0] – G. Asins Aug 20 '17 at 10:46
  • Excel VBA Code:Sub AEATWEB() Dim IE Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True Hwnd = IE.Hwnd IE.Navigate "https://www2.agenciatributaria.gob.es/es13/h/iexmmmfi.html" MsgBox ("wait") Set oShell = CreateObject("Shell.Application") For Each Wnd In oShell.Windows If Hwnd = Wnd.Hwnd Then Set IE = Wnd Next IE.Document.getElementById("EJF").Value = Range("A2").Value IE.Document.getElementsByName("MOD")[0].value = "347" End Sub – G. Asins Aug 20 '17 at 10:52
  • Get the value in to another variable first and then use index on it – Tarun Lalwani Aug 20 '17 at 11:12
  • `Set MOD = IE.Document.getElementsByName("MOD")` and `MOD[0].value = "347"` – Tarun Lalwani Aug 20 '17 at 14:19
  • I'm sorry but it seems not working... or I don't know how to complete the vba code correctly. Help! – G. Asins Aug 20 '17 at 17:18
  • after writing the code "set MOD = IE.document........." in VBA module apears error: Identifier expected – G. Asins Aug 20 '17 at 17:34
  • Use `IE.Document.getElementsByName("MOD").Item(0).value = "347"` – Tarun Lalwani Aug 20 '17 at 17:39