Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    13

    Unanswered: Downloading Zip files and importing

    I have a MS Access which I regularly need to import text files contained in a zip file. The zip file is hosted on a website and updated on a regular basis. The zip file url remains the same.

    I have written the vba to automatically import all the zipped text files. However what I cannot understand is how to automatically download the zip file and then unzip into a specified folder.

    Has anyone ever done anything similar? If possible the code should check the saved date on zip file and only import if this is a new version.

    I am running Access 97 and Winzip 8.1 SR-1

  2. #2
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    I have seen something similar in a Microsoft publication - "Running Excel 2000" where:

    1. Application logs onto a specific url at specified times
    2. Checks for any updates
    3. Downloads the data and imports into Excel.

    Unfortunately, number one son has made off with my book, so, I cannot offer any immediate help.

    I will contact him and get him to email the relevant code - it should not pose too much of a problem to convert to your requirements.

    Jumper

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    13
    Thanks Jumper - look forward to your reply. I assumed there would be an easy way of doing this. I think I need to do something like (1) check file date (2) copy (3) extract using Winzip to same or specified location on my HDD (4) import to a specified table.

    I'm able to OPEN the webpage using the following code. Your post reminded me of John Walkenbach's excellent Excel webpage;
    _______________________________________________
    Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Sub OpenWebPage()
    Dim URL As String
    URL = "http://www.myurl.com/file.zip"
    ShellExecute 0&, vbNullString, URL, vbNullString, _
    vbNullString, vbNormalFocus
    End Sub
    __________________________________


    Regards,

    siom
    __________________________________________________ __

  4. #4
    Join Date
    Sep 2003
    Location
    UK
    Posts
    13
    Hi,

    has anyone got any ideas for this .... its still driving me mad.


    rgds
    sioma

  5. #5
    Join Date
    Oct 2003
    Posts
    4
    You've downloaded the .zip file, after that for Unzipping it through the VBA code, probably you need the WinZip Command line utility, or Unzip.exe from pkzip or RAR commandline where you can pass the .zip file name and output directory etc. and it'll be unzipped.

    After that you can go ahead with loading the txt file.

    Hope it helps..

  6. #6
    Join Date
    Oct 2003
    Posts
    4
    Also these unzipping utils have option which you can mention so as to unzip only if the contents inside the zip is newer or changed.

    As far as i remember unzip.exe returns some values which you can capture and see if you need to do the loading of the database (if changed) of skip.

    cheers!

  7. #7
    Join Date
    Dec 2003
    Location
    Bucharest
    Posts
    11
    Hi,

    I have the same problem...did you figure out which is the command line?

    Is this code correct?

    mypath="D:\"
    command_shell = unzip mypath & "destinationfolder " & mypath & zipfile
    zipvar = Shell(command_shell)

    I am running Access 2000 on Win XP.

    thanx

Posting Permissions

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