Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import a .txt from the Internet (Office98/Access97)

    I am trying to import a txt file from the internet into an Access table. I would like to accomplish this by clicking once on a command button. When using a command button, I place the hyperlink address in the properties box and it opens the net page, but then I have to manually save the file using File Save because I can't get the hyperlink to run before a DoCmd. I also tried to set a Spec but can't figure out how to set one directly to the net. I have to save the file first on my computer before I am able to import it into an Access table. Does anyone have the code to accomplish this import in one step?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Import a .txt from the Internet (Office98/Access97)

    You can use the URLDownloadToFile API function to download text file, then import file into an Access table, using a predefined import specification. Example:

    Option Compare Database
    Option Explicit

    Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
    "URLDownloadToFileA" _
    (ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) As Long

    Public Sub TestDownloadAndImportTextFile(ByRef strURL As String, _
    ByRef strFile As String, _
    ByRef strTbl As String)

    ' strFile = full path & file name to download to & import from
    ' will overwrite existing file if file exists

    Dim lngRtn As Long
    lngRtn = URLDownloadToFile(0, strURL, strFile, 0, 0)
    DoCmd.TransferText TransferType:=acImportDelim, _
    SpecificationName:="Download TextFile Import Spec", _
    TableName:=strTbl, Filename:=strFile, _
    HasFieldNames:=False

    End Sub

    I just posted a dummy text file (ASCII codes and characters) in the Test Area, then successfully imported file using above code with this instruction:

    TestDownloadAndImportTextFile "http://www.wopr.com/w3tfiles/26-319025-TextFile.txt","C:AccessTextFile.txt", "TextFile"

    The 1st argument is URL for text file, 2nd is file path/name on local PC, 3rd arg is name of table. To test this you would have create table & define an import spec for the text file you intend to download, which I assume has a standard, tabular structure that would enable importing it into Access in automated fashion. If not delimited text, modify TransferText command accordingly. For more info on URLDownloadToFile function see MSKB 244757:

    HOWTO: Download a File Without Prompting

    According to article, info applies to IE 4.0 or later, and VB 5.0 & 6.0, so this function should be available unless your computer is a relic.

    HTH

  3. #3
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import a .txt from the Internet (Office98/Access97)

    Many, Many Thanks!!!!! And many more. This is invaluable information to me!!! It will help streamline many of our automated systems here. Thank you again!!!!! I am very appreciative and will always be!

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import a .txt from the Internet (Office98/Access97)

    RE: URLDownloadToFile API function

    Hi Mark

    I

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Import a .txt from the Internet (Office98/Access97)

    I don't know what an .ARP file is supposed to represent, but if the button used to download file has a link with an URL then you should be able to download file using the URLDownloadToFile function. I assume you have to log into site with password, etc., and that these files are likely generated dynamically by the web site, using JavaScript or whatever, and do not have fixed URL's -- then don't think there is any easy way to automate process since the process is controlled by the web site. The file has to have an URL that can be specified for the function to work.

  6. #6
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import a .txt from the Internet (Office98/Access97)

    Approximately a year ago you assisted me with the URLDownloadToFile API. It works beautifully. I am creating another database that also pulls in information, however, the information is protected. Once the web page is opened the user has to Accept a license agreement by clicking on an accept button. This acceptance is followed by user filling out a username and password and clicking on a Login button. I understand that a direct URL must be specified for this function to work, but I was hoping you may have additional thoughts on this. I understand these additional steps the user has to go through are to protect the information; but have you heard of away to get around it?

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Import a .txt from the Internet (Office98/Access97)

    Excuse the delayed response, but sorry no, I don't have any brainstorms on how to get around the login/password restrictions. At work I need to export data from various "official" (government) web sites for use in our local databases, all of these sites require some sort of login authentication, passwords, etc, as the data is often sensitive in nature. I'm not smart enough to figure out a good way to automate downloading/exporting data from these sites. (Even if I were, not sure it would be a good idea in view of the information security policies in effect in my organization.) So I usually do the downloading "manually" then at least can somewhat semi-automate the import-into-Access process by using standard download folder locations, file names, etc for each information system/web site used as a data source. Maybe someone more knowledgeable on "web stuff", posting HTML forms, etc would have some ideas.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •