Full Version: Tough one for a rookie like me
UtterAccess Forums > Microsoft® Access > Access Forms
murga_wilddog
this one is over my head,I have an idea,but.... frown.gif
How can I import data from this url : http://www.nbs.yu/kl/today.php?vrsta=3 ,as a matter of fact I need
value under column "Srednji Kurs" row 978 Emu,the first row.
Oneed to update that value daily in my database ,how do I do that,hyperlink maybe ,I dunno?
tnx guys ,need help on this one
adaytay
Hmmm...
know you can do it in Excel... so it must be possible in Access, too.
Does the position of the data stay constant (does it always appear in the second row?)
Cheers,
Ad
murga_wilddog
Yes ,I think so ,but it`s in first data row .Its official site of national bank of serbia.
Oneed the current exact daily euro value in my form its vital for the data entry.
nx
murga_wilddog
One more thing, when you right click it(the page I mean) ,you have an option to export it in excel,maybe using soma VBA code is possible
murga_wilddog
adaytay ,somebody ?
Maybe the situation is to import the excel table every day and then from excel onto access.But still I don't have a clue how to make this work :(
murga_wilddog
Anybody?
murga_wilddog
Ok.I have html code for the page,the question now is ,how to extract the thing I want from that html using VBA,does access "see" the html automaticly or not?
LittleViews
The software that pushes data to webpages, like .NET, .ASP and ColdFusion, to name a few, parce databases and surround the information with HTML-required codes.
hese same software packages have the ability to evaluate HTML pages if certain constants are known in the tags and extract information. XML tagging also creates constants which can be grabbed.
Olooked at the page you want and found that I could not see its source (View, Source) when on the web, but if I did a Save As, and provide .htm as the file extension, I was able to read the source locally. That said, I did not see anything that you could use as hooks.
SOOOO, one way to approach the situation is simply go to the page and by clicking and dragging, copy the row or rows you need into Excel, save the Excel file, then import it into Access.
That should work like a charm.
The Excel version the site supplies may cause you problems -- that is, you might have to do more fooling around than necessary.
The CSV (comma delimited) is a real winner that Access would love.
The XML version you'll need to study a bit.
How much you want to automate is up to you. You have your best chance with CSV, then XML. Otherwise, copy and pasting into Excel is the way to go.
adaytay
Thanks for stepping in Karen - murga, sorry for the delay I got held up having to do my "proper" job hence me not being able to get back to the thread in a timely manner.
aren, thanks again - I think you've hit the nail on the head there that's exactly what I was thinking as a manual solution - although it may be possible to refine the solution a little further in VBA, getting the value automatically.
I'm thinking it must be possible - I've got a little excel spreadsheet that downloads the stats from my webserver so I can see who my referrers are, so it should be possible to adapt and set to run from within Access.
Ad
murga_wilddog
Ok ,people thanks ,but what about making a script in windows that automaticly saves the page and extract its html to a txt file.Anybody idea how to do it.From there I will menage to do something in C to extract the value needed.
So the win script only,I dunno anything about win automatics.
murga_wilddog
There is a CSV save link there on the site,so what I really need is a scrip that saves that table in designated folder.Later I would do on open script to import the values in my DB.
nx again people
LittleViews
I do not believe that you want the HTML txt file (or source) as it does not have the hooks you need.

Prior to XML, you could use a Perl Script to locate various tags using HTML's comment tag as a starter. The producer of the page to be copied had to create a standard and the people who extratacted data used that standard to extract code. XML tags make that easier and my understanding is that Access 2000 can interact with XML tags.

But easiest of all is a comma delimited file (perhaps because I am most familiar with it). You can acquire your data by going directly to this page:

http://www.nbs.yu/kl/today.php?vrsta=3

You would then need to know how to work the prompt box for saving the file to your desktop. I believe you can do this in VB, but do not ask me how as I don't have time to do the research.

Once your document is saved, you tell your system to import it and extract data. Again, that can be done in VB, you'll have to do the fingerwork.
murga_wilddog
Ok. tHanks little.Now I'll post a thread in VB section to see whether those guys know how to do that job.
nx once again
Girn13
Murqa,
Try contacting the bank and see if they also provide the infotrmation in a format you can use. If they're setting the rate they must be suppling it to other agencies. If they're just reporting the rate maybe they'll tell you where you can go to get it.
ick
murga_wilddog
tnx G. I had that idea but again I need to use it automatic,the bottom story is I do not want my end users to have anything to do with it.When they open the app. ,everything should be already there.
Its a misfortune that hyperlink cant be used better for example for data upload.
urga
Girn13
Murqa,
That's what I was trying to get to. Often an institution will make information they're responsible for publishing avaialable in media that other firms can download automatically. They might even have a "e-mailing list" you can get on and they'll send you a copy of the data whenever it changes.
ick
murga_wilddog
Tnx G ,I'll try.
murga_wilddog
The bank doesnt send it automaticly,they said that I can freely download it from the site frown.gif,dumb a.....
tdewitt274
Here's a gem that I found some time back. It's in VBScript, but I'm sure that you probably have IE on your system.
You would have to modify it slightly (especially the "sContentTypeExpected = "image/gif"" reference), but it's pretty well setup. It does work in VBA as well.
CODE
OS: XP Pro with IE 6.0
Every morning I come in, open 2 websites, and save their content as
TXT files, one is a TAB file the other is a CSV file.
Example addresses:
http://somesite/reports/date.tab
http://somesite/reports/date.csv
I then save them as:
http://somesite/reports/date.tab.txt
http://somesite/reports/date.csv.txt
I would like to write a program to automate this, I was wondering
which language would be best, and maybe get a couple of quick and
dirty examples.


Here's an example to get you started that you can easily adapt...

'===DemoGetImageStream.vbs===
'
'Requires:
'
' - XML 2.0 or higher
' - ADO 2.5+ for "adodb.stream"
'

sDestFolder = "c:\_temp\"
sSrcUrl = "http://msdn.microsoft.com/library/mnp/2/gif/"

sImageFile = "bnr_msdn_LTR.gif"
sContentTypeExpected = "image/gif"

set oHTTP = CreateObject("msxml2.XMLHTTP")

oHTTP.open "GET", sSrcUrl & sImageFile, False
oHTTP.send

if oHTTP.status <> 200 then
   msgbox "unexpected status = " _
     & oHTTP.status & vbcrlf _
     & oHTTP.statusText
   wscript.quit
end if
sContentTypeReturned = oHTTP.getResponseHeader("content-type")
if sContentTypeReturned <> sContentTypeExpected then
   msgbox "unexpected content-type = " _
     & sContentTypeReturned & vbcrlf _
     & "expected = " & sContentTypeExpected
   wscript.quit
end if


set oStream = createobject("adodb.stream")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
oStream.type = adTypeBinary
oStream.open
oStream.write oHTTP.responseBody
oStream.savetofile sDestFolder & sImageFile, adSaveCreateOverWrite

set oStream = nothing
set oHTTP = nothing

msgbox "Done..."

Next, I would set up a script that uses the FSO and basically search for a certain keyword. It's been awhile since I've done it, but basically assign the text file to a variable and look for an InStr(data,"Keyword"). You'll have to mess with it a bit, because you're working with a table.
Bad thing is you will have to revise it if they modify the keyword or add CSS to the table tags. But for the most part, you should be fine.
Odon't make it onto the site much, so good luck. I will mark it to my favorites and check back if I get the time.
Hope this helps
Todd
murga_wilddog
Tnx Todd ,I 'll try it out.
Looks scary but frown.gif...
murga_wilddog
What should i put instead of
SrcUrl = "http://msdn.microsoft.com/library/mnp/2/gif/"
sImageFile = "bnr_msdn_LTR.gif"
sContentTypeExpected = "image/gif"
This is honestly over my head , I get most of the code but....as I said the above part of the code is where solution is.
murga_wilddog
Or , at least how to save the page as txt ?
murga_wilddog
My code :
'Here's an example to get you started that you can easily adapt... '===DemoGetImageStream.vbs=== ' 'Requires: ' ' - XML 2.0 or higher ' - ADO 2.5+ for "adodb.stream" '
sDestFolder = "c:\"
sSrcUrl = "http://www.nbs.yu/kl/today.php?vrsta=3"
sImageFile = "kursna.txt"
set oHTTP = CreateObject("msxml2.XMLHTTP")
oHTTP.open "GET", sSrcUrl , False
oHTTP.send
sContentTypeReturned = oHTTP.getResponseHeader("content-type")
set oStream = createobject("adodb.stream")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
oStream.type = adTypeBinary
oStream.open
oStream.write oHTTP.responseBody
oStream.savetofile sDestFolder & sImageFile, adSaveCreateOverWrite
set oStream = nothing
set oHTTP = nothing
msgbox "Done..."
Doesnt work ,says write to file failed
murga_wilddog
It works , , < ,I've managed to save a html code in txt format !!!!!!
Tnx Todd !!!
How the question is how to automaticly call an vbs script when I open my app. in MS ACCESS,and then imort the data from txtfile.
The code for download :
XML 2.0 or higher ' - ADO 2.5+ for "adodb.stream" '
sDestFolder = "c:\Documents and Settings\Milana Milivojevic\My Documents\RazmenaDB\"
sSrcUrl = "http://www.nbs.yu/kl/today.php?vrsta=3"
sImageFile = "kursna.txt"
set oHTTP = CreateObject("msxml2.XMLHTTP")
oHTTP.open "GET", sSrcUrl , False
oHTTP.send
sContentTypeReturned = oHTTP.getResponseHeader("content-type")
set oStream = createobject("adodb.stream")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
oStream.type = adTypeBinary
oStream.open
oStream.write oHTTP.responseBody
oStream.savetofile sDestFolder & sImageFile, adSaveCreateOverWrite
set oStream = nothing
set oHTTP = nothing
msgbox "Done..."
murga_wilddog
OK. Now I've saved the page in html format auto.
When I try to import the data it imports everything fine except SREDNJI KURS column.I dunno why :(
murga_wilddog
Ok. It imports it fine now all of a sudden.
Ok the solution to this thread is very close now.
What we have now is automatic page download thru VBS script to html format ,the ACCESS imports it fine manualy(in html there are 5 tables to import only one needed).
What I need now is automation , app.opened -> script runned ->table auto imported
Thats about it
Odunno how to do it ,but I bet for you guys this should be easy
Tnx
murga_wilddog
Ok guys.
The problem is solved.
So if anyone has the problem to do the following thing automatic (save the html page from the net ,import the table from it )
the complete code is :
sDestFolder = "destination folder"
sSrcUrl = "http://...."
sImageFile = "name of the saves file"
Set oHTTP = CreateObject("msxml2.XMLHTTP")
oHTTP.Open "GET", sSrcUrl, False
oHTTP.send
sContentTypeReturned = oHTTP.getResponseHeader("content-type")
Set oStream = CreateObject("adodb.stream")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
oStream.Type = adTypeBinary
oStream.Open
oStream.write oHTTP.responseBody
oStream.savetofile sDestFolder & sImageFile, adSaveCreateOverWrite
Set oStream = Nothing
Set oHTTP = Nothing
DoCmd.TransferText acImportHTML, "specification that you save when importing table manualy", "name of the table in which you import", "complete path and file name from which you import", True - you want first fields to act as field names ,false first rows as data, "name of the table in html - can be more of them ,from which you import ,it can be also seen from the manual import"
Tnx every one
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.