Automate .txt file import from web

[Rzrbkpk]

I have .txt file on a web page that use in Access. What I've been doing is opening the page and saving it as a .txt file. I save it to the same location and this file is linked to Access. Is there any way to automate this instead of opening the web page each time and saving? Maybe a vbscript I can run on task schedular or a batch file to FTP the file. If it's a batch process. I will already warn you that I do not have knowledge of batch/FTP process, so it will have to be spelled out.

I'm not sure if this is the place to post this, but I figured someone would know.

 

[ByteMyzer]

The following sub will retrieve http://www.mywebsite.com/mytext.txt from the web and save it to C:\mytext.txt:

Public Sub GetTXT()

    Dim fileURL As String
    Dim hdLocation As String
    Dim xmlhttp
    Dim myStream
    Dim fso

    fileURL = [b]"[URL unfurl="true"]http://www.mywebsite.com/mytext.txt"[/URL][/b]
    hdLocation = [b]"C:\mytext.txt"[/b]

    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

    xmlhttp.Open "GET", fileURL, False
    xmlhttp.send


    Set myStream = CreateObject("ADODB.Stream")
    myStream.Open
    myStream.Type = 1

    myStream.Write xmlhttp.ResponseBody
    myStream.position = 0

    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.Fileexists(hdLocation) Then _
        fso.DeleteFile hdLocation
    Set fso = Nothing

    myStream.SaveToFile hdLocation
    myStream.Close
    Set myStream = Nothing

    Set xmlhttp = Nothing

End Sub

See if this works for you.

 

[mp9]

Once you've got the code for the import (as per previous post), create a macro to run ro run the function. Then check out the freeware Handy Access Launcher (HAL) at

http://www22.brinkster.com/accessory/resources/001.shtml

to schedule running the macro.