save webdata to text file

[Guest]

Hi,

I have the following web link to some yahoo data. If I execute this from an
excel sheet it dupms the data into table.csv.

From Access VBA, how can I get this link to save it so a text file in the
same folder as my Access db.

http://ichart.finance.yahoo.com/table.csv?s=^DJI&a=09&b=15&c=2006&d=06&e=14&f=2007&g=d&ignore=.txt


Bruce

 

[Guest]

Even better,

Can I use transfer text to import this html table directly to a table?
The TransferText seems to indicate this but I cannot get it to work.

Bruce

 

[Douglas J. Steele]

AFAIK, TransferText will only work with a saved local copy of the html file.

To save a web page through VBA, try:

Sub SaveWebpage(URL As String)

Dim objWeb As Object
Dim intFile As Integer
Dim strFile As String
Dim strHTML As String

Set objWeb = CreateObject("Microsoft.XMLHTTP")
objWeb.Open "GET", URL, False
objWeb.send

strHTML = objWeb.responseText

strFile = CurrentProject.path & "\Saved.html"

intFile = FreeFile()
Open strFile For Output As #intFile
Write #intFile, strHTML
Close #intFile

End Sub

Pass the URL to that sub, and it will save it as a file named "Saved.html"
in the same folder as your front-end database.

 

[Guest]

Thanks Douglas,

I'll stop trying with TransferText then.

1. I want the file to save as a .csv (comma delimited). I changed a line
from your code to strFile = CurrentProject.Path & "\Saved.csv" but it loses
the comma delimitied formatting. Can you help retaining this formatting. From
here I know how to do the last part of importing a .csv into myTable.

Bruce

 

[Douglas J. Steele]

Remember that I said "TransferText will only work with a saved local copy of
the html file." That code saves the file exactly as is it on the web. In
other words, it gives you "a saved local copy of the html file".

Try using TransferText with a transfer type of acImportHTML.

 

[Guest]

Hi Douglas. I appreciate your help, but i am still having trouble.

After running your code and opening the file in Notepad, the data is
enclosed in " at the start and end of the data. If i do a file saveas from
the webpage it does not.

Is the " causing me problems?

 

[Douglas J. Steele]

Oops, guess I should have looked a little closer at the file that was
output.

Change

Write #intFile, strHTML

to

Print #intFile, strHTML

Sorry about that.

 

[David W. Fenton]

Oops, guess I should have looked a little closer at the file that
was output.

Change

Write #intFile, strHTML

to

Print #intFile, strHTML

Sorry about that.

That one always gets me, too.

I really wish Access would steal the File System Object.