I am using the following code to get data from an html page that is stored from the web in a folder on my pc. I have used basically the same code in another module where it work perfectly, so I don't get why it does not work in this separate routine. The reason why I need the separate routine is because the original code is embedded in a quite complex large routine which I cannot run just to check the data of a specific file, which BTW has been analyzed without any problem with the code in the more complex set of routines. (The original piece of code follows below for comparison).
Dim Act As Worksheet, ActSt As Worksheet
On Error GoTo errorhandler
Set Actbl = Workbooks("table.xlsm")
Set ActSt = Actbl.Worksheets("act.st.") 'query will be stored here
'some code to define path &filename
............
' Create URL
URL = path & filename
' Create Web Query & refresh it
If Len(Dir(URL)) > 0 Then 'found the file
ActSt.Activate
ActSt.Cells.Clear 'clear sheet "act.st."
'set up a table import (the URL; tells Excel that this query comes from an html file)
Set qt = ActSt.QueryTables.Add( _
Connection:="URL;" & filename, Destination:=ActSt.Range("A4")) 'save data in "act.st."
With qt
.WebConsecutiveDelimitersAsOne = False
.WebDisableDateRecognition = False
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebSelectionType = xlEntirePage
.WebSingleBlockTextImport = False
.RefreshStyle = xlOverwriteCells
.Refresh 'get the data
End With
ActSt.QueryTables.Item(1).Delete 'delete the created query, otherwise they accumulate
Else
MsgBox "File not found"
Exit Sub
End If
errorhandler:
answer = MsgBox("Error " & Err.Number & ": " & Err.Description & ". Exit?", vbYesNo)
If answer = vbYes Then Exit Sub
Resume
Although the line "If Len(Dir(URL)) > 0 Then" makes sure that the file exists, I get an error when the code arrives at the "refresh". The message is:
Error 1004: The address of this site is not valid. Check the address and try again. Exit?
(The text may be slightly different with an english language OS because in reality it is in spanish and this is just my translation)
I don't understand how the address can be "not valid" when the file exists obviously and is detected with "Dir(URL)" and how this problem can be resolved.
A second question, connected to this one, concerns another error 1004 that I got before when I tested this code. I had the file open in a browser while I ran the code and I got a 1004 error saying something like "error defined by application". I imagine that this meant something like "file in use by another user" or so. Is there a way to distinguish this kind of 1004 error so the error message could be more specific? Something like an "error sub-number"? 1004 is enormously generic.
Thank you everybody who can help me find the solution, especially for the first question.
This is the original piece of code which I copied and adapted slightly for the shorter routine:
If Len(Dir(filename)) > 0 Then 'found the file
GetFile = True
ws1.Cells.Clear 'clear sheet "act.st."
'set up a table import (the URL; tells Excel that this query comes from an html file)
Set qt = ws1.QueryTables.Add( _
Connection:="URL;" & filename, Destination:=ws1.Range("A4")) 'save data in "act.st."
With qt
.WebConsecutiveDelimitersAsOne = False
.WebDisableDateRecognition = False
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebSelectionType = xlEntirePage
.WebSingleBlockTextImport = False
.RefreshStyle = xlOverwriteCells
.Refresh 'get the data
End With
ws1.QueryTables.Item(1).Delete 'delete the created query, otherwise they accumulate
Else
GetFile = False
End If
Looks like you have not set the values of two variables i.e. path and filename which are building up the URL resulting in null value for URL. Please double check the path and filename variables and if they are set correctly. Thanks