XML import from https - error (1 Viewer)

dlugirapfr

Registered User.
Local time
Today, 14:25
Joined
Nov 22, 2012
Messages
68
Hi,
in my database I have macro which download xml file from url https .

Simply macro with that code
Code:
Application.ImportXML _
 DataSource:="https:// xxxxxxxx type=.xml", _
 ImportOptions:=acAppendData

It worked perfect. But from some update of Windows 10 or Office 365 32 bit I receive error: Run-time error '-2147024891 (8007005)'
When I press debug I see Visual Basic like this:

error.png


I don't know why. When I copy paste url from vba code to web browser I can download manually xml table. If I write in vba not url like https:// but local like c:/base/test.xml it will import file without errors. I think it is something with downloading XML by Access VBA from https.
Do you have similar problem? Please help - it is waste of time to download manually 15 tables from urls.

Karol
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:25
Joined
Oct 29, 2018
Messages
21,358
Hi Karol. I don't have O365, so I can't tell you what's wrong. But maybe, you could try using the HttpRequest class.
 

cheekybuddha

AWF VIP
Local time
Today, 13:25
Joined
Jul 21, 2014
Messages
2,238
I just tried importing an xml file from the web using Access 2007 and it worked (albeit with some import errors).

If there is a new security block with your version of Access, perhaps download the file (through code) and import locally?
 

dlugirapfr

Registered User.
Local time
Today, 14:25
Joined
Nov 22, 2012
Messages
68
I tried to make import by integrated tool. So I press on ribbon -> External data ->New data source -> from XML file and paste URL. When I press OK Access don't do anything. Just close window with URL.
My download xml file from URL I don't need any authorization - just unique url.
 

dlugirapfr

Registered User.
Local time
Today, 14:25
Joined
Nov 22, 2012
Messages
68
Still I have problem with it. Do you have similar problem with it? Do you have link to any sample xml table which I can use to check download process ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:25
Joined
Oct 29, 2018
Messages
21,358
Still I have problem with it. Do you have similar problem with it? Do you have link to any sample xml table which I can use to check download process ?
Hi. Did you try my suggestion? Just curious.
 

cheekybuddha

AWF VIP
Local time
Today, 13:25
Joined
Jul 21, 2014
Messages
2,238
In addition to DBG, have you tested with another XML source?

Try these tests.

First, paste into a new module the following code (based on the link that DBG gave):
Code:
Function HTTP_Get(webServiceURL As String) As String

  Const XMLHTTP             As String = "Microsoft.XMLHTTP", _
        METHOD_GET          As String = "GET", _
        HTTP_STATUS_SUCCESS As Integer = 200
    
  With CreateObject(XMLHTTP)
    .Open METHOD_GET, webServiceURL, False
    .Send
    If .Status = HTTP_STATUS_SUCCESS Then
      HTTP_Get = .ResponseText
      Debug.Print .getAllResponseHeaders
    Else
      Debug.Print .Status & ": " & .StatusText
    End If
  End With
  
End Function

Then, in the Immediate Window (Ctrl+G), run the following:
Code:
?HTTP_Get("https://xkcd.com/rss.xml")
Code:
Application.ImportXML "https://xkcd.com/rss.xml"
Code:
?HTTP_Get("https://Your/URL/yourFile.xml")

Let us know the results.

d
 

dlugirapfr

Registered User.
Local time
Today, 14:25
Joined
Nov 22, 2012
Messages
68
I'm really shocked. I made new empty database and I checked code
Code:
Application.ImportXML "https://myurl.xml"
And it works. :O But in my original database doesn't work. I put compact & repair database and it doesn't work.
In my original database I don't have table but link to table.
 

dlugirapfr

Registered User.
Local time
Today, 14:25
Joined
Nov 22, 2012
Messages
68
Ok I found solution.
At head of my code VBA I add
Code:
?HTTP_Get("https://mysecreturl/file.xml")

And now everything works ! Thank you very much for your help. Now I will not waste my life time to manually download. But to be honest I don''t know why it works in past and now it doesn't
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:25
Joined
Oct 29, 2018
Messages
21,358
Ok I found solution.
At head of my code VBA I add
Code:
?HTTP_Get("https://mysecreturl/file.xml")

And now everything works ! Thank you very much for your help. Now I will not waste my life time to manually download. But to be honest I don''t know why it works in past and now it doesn't
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom