PDA

View Full Version : [SOLVED] import a .csv generated from a php website link



Donny
09-01-2016, 12:15 PM
Hey everybody,

There is a website from which I can download a .csv file. I think the .csv file is not actually on the server but is generated with a php script.
At the moment I can not post the link but I can say that it ends with .php?... and because of that I think the .csv is created on the fly.

I have tried to import it with 'Data' -> 'From site' and 'From text' but it doesn't work. It only works with a direct link to a .csv file, I guess?

At the moment I Always delete the old file in 'my downloads' folder, go to the link, download and save the .csv and then do my thing with VBA.

My question now: is it possible to download and open the .csv automatically with VBA code (or just plain Excel)?

Many thanks

offthelip
09-02-2016, 03:53 PM
Yes it is,
Firstly I would try it using an excel webquery, it is very likely that that will work,
if that doesn't work then you can download from the site using the xmlHttp obkect with code such as this:
Obviously this needs to be modified to work with the url that you are using
Dim outarr As Variant
Dim FinRows, FinCols
Set FinHTTP = CreateObject("Microsoft.xmlHTTP")


FinHTTP.Open "GET", urlstring, False
FinHTTP.send

textmess = FinHTTP.responseText
' MsgBox textmess
FinRows = Split(FinHTTP.responseText, Chr(10))
For j = 0 To UBound(FinRows) - 1
FinCols = Split(FinRows(j), ",")
For k = 0 To UBound(FinCols)
outarr(j + 2, 1 + k) = FinCols(k)
Next k
Next j

Donny
09-06-2016, 12:05 PM
It seems that I already tried the excel webquery. For some reason it gives me a "page can not be displayed" for every url that I try, even google...

The code doesn't work either and I also think that I tried similar code found with google.

The code stops at 'FinHTTP.send' with error:

run-time error -2146697211 (800c0005)
the system cannot locate the resource specified

edit: whatever url I try in the code, it throws the error above.

snb
09-06-2016, 12:16 PM
Is it a Flemish link ?

If it's impossible for you to get through, there's no harm in telling us the URL here (that the raison d'etre of a forum: all is public.)

Donny
09-06-2016, 12:27 PM
It's a dutch URL ;-). I want to share the URL here but my postcount is still too low.

I hadn't tried other urls in the past but now I found out that no url works for both the web query and the code.

Coincidence? Am I missing something like a library or something else?

snb
09-06-2016, 12:59 PM
You can describe the url without formatting it as a link.

Some websites are software-requestprotected.

Donny
09-06-2016, 01:36 PM
I feel really stupid now. I had already deactivated my AV and firewall (for 5min) just to make sure that was not the issue.

I gave it another shot and looked at the firewall permissions and BAM, Excel was beeing blocked for god knows why.

The web query is working now.

The code seems to be the perfect solution, except it throws a 'type mismatch' at 'outarr(j + 2, 1 + k) = FinCols(k)'

The link is "lotto.uitslagencentrale" and .nl/download.php?src=BE&typ=uit&"

edit: the delimiter is ";" in this case. But it still gives me the error

offthelip
09-06-2016, 03:21 PM
In my original the definition of outarr is just a simple variant, it needs to be defined as a two dimensional array. If the return size is fixed you can put the size in as fixed size at the top:
Dim outarr( 1 to 1000, 1 to 2000) as variant

if the return size is variable then you need to redimension the outarr variant array before you write to it, to the size finrows and fincols.
So put this at the top
Dim outarr() as variant
firstimeflag=True

Put this just before the output to outarr is written, where your error occurs
if firsttimeflag then
redim outarr( 1 to finrows, 1 to fincols)
firstimeflag = false
end if

snb
09-06-2016, 11:25 PM
Why so complicated ?


Sub M_snb()
Workbooks.Open "http://lotto.uitslagencentrale.nl/download.php?src=BE&typ=uit&"
End Sub

Donny
09-07-2016, 12:50 PM
Workbooks.open was one of the first things that I tried but it didn't work at that time.
I guessed it had something to do with the url.
Now I know it didn't work because Excel was blocked by my firewall but it does work. So this is a solution

The first code with the xmlHTTP object also works now.

A big thank you to all!

Topic is solved