JimmyTheHand
11-21-2007, 01:08 AM
Hi
I'm trying to pull data from the web using webquery. Sometimes it works, sometimes it doesn't.
Background:
Testing laboratory needs to work according to standards. It is required to follow any changes, and use the newest standards available. The standards I need to check periodically are from different sources. There are ASTM, ISO, ISO EN, MSZ (this latter is Hungarian national std.), etc. I need to determine the date (year) of the latest issue.
Problem description:
I experimented with ASTM and ISO websites, and got their seach engines working by updating the connection string with standard numbers as parameters. It works fine, no problem. But when I try to do the same with MSZ site, it just fails. The error message is:
Run-time error '1004':
https://...(URL)
cannot be opened...(etc.)
And here's the code I'm using. (For start, I used constant parameters 'MSZ' and '11721*', in the connection string.)
Sub MSZ_qry()
Dim Conn As String
Dim LDR As Worksheet
Set LDR = ThisWorkbook.Sheets(1)
Conn = "URL;https://www.mszt.hu/mszt/portal/user/anon/page/default.psml/js_panename/msztObjectBrowser?jelzet=MSZ+11721*&tipus=S"
With LDR.QueryTables.Add(Connection:=Conn, Destination:=LDR.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
The same URL can be opened with internet browser. So what's wrong with the code? How can I overcome this problem?
Thanks in advance for any tips.
Jimmy
I'm trying to pull data from the web using webquery. Sometimes it works, sometimes it doesn't.
Background:
Testing laboratory needs to work according to standards. It is required to follow any changes, and use the newest standards available. The standards I need to check periodically are from different sources. There are ASTM, ISO, ISO EN, MSZ (this latter is Hungarian national std.), etc. I need to determine the date (year) of the latest issue.
Problem description:
I experimented with ASTM and ISO websites, and got their seach engines working by updating the connection string with standard numbers as parameters. It works fine, no problem. But when I try to do the same with MSZ site, it just fails. The error message is:
Run-time error '1004':
https://...(URL)
cannot be opened...(etc.)
And here's the code I'm using. (For start, I used constant parameters 'MSZ' and '11721*', in the connection string.)
Sub MSZ_qry()
Dim Conn As String
Dim LDR As Worksheet
Set LDR = ThisWorkbook.Sheets(1)
Conn = "URL;https://www.mszt.hu/mszt/portal/user/anon/page/default.psml/js_panename/msztObjectBrowser?jelzet=MSZ+11721*&tipus=S"
With LDR.QueryTables.Add(Connection:=Conn, Destination:=LDR.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
The same URL can be opened with internet browser. So what's wrong with the code? How can I overcome this problem?
Thanks in advance for any tips.
Jimmy