Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy from Internet (2003)

    Hi all,

    how can I copy a file from the Internet, e.g.

    http://www.wopr.com/w3tfiles/5-544844-test.xls

    (it's actually my file)

    to say c:Mydir

    with VBA.

    Thank you

    Wolf

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy from Internet (2003)

    Create the following function :<pre>Private Declare Function DoFileDownload Lib "shdocvw.dll" (ByVal lpszFile As String) As Long

    Function DownLoadFile(sURL As String) As Long
    On Error Resume Next
    DownLoadFile = DoFileDownload(StrConv(sURL, vbUnicode))
    On Error GoTo 0
    End Function</pre>


    You couls then use it as follows :<pre>Dim ret As logical
    ret = DownLoadFile("http://www.wopr.com/w3tfiles/5-544844-test.xls")</pre>


    You may of course need to be logged on to the appropriate site with correct username/password, if applicable

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy from Internet (2003)

    Hello Andrew,

    thank you very much, it works.
    The declaration as "logical" was not accepted, but variant works.

    One more question:
    Is it possible to copy the file directly to a specified directory.
    It is still asking where to copy and if to open or save etc.

    Best regards

    Wolf

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy from Internet (2003)

    Wolf,

    Firslty sorry about Logical, it should of course have been Boolean. I should test code before posting.

    I had a look at the API Guide and came up with the following :<pre>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 Function DownloadFile(URL As String, LocalFilename As String) As Boolean
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
    End Function</pre>

    I tested this and it works for me with the following :<pre>Sub URLToFile()
    Dim ret As Boolean
    Dim strURL As String, strDestination As String
    strURL = "http://www.wopr.com/w3tfiles/5-544844-test.xls"
    strDestination = "CataTest.xls"
    ret = DownloadFile(strURL, strDestination)
    End Sub</pre>

    Not too sure what security precautions might be applicable, or indeed desirable, on different systems.

    Andrew C

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy from Internet (2003)

    Andrew,

    thanks for your effort.

    The link is very useful.

    Wolf

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy from Internet (2003)

    Yea, it's not that we are smart: we just have lots of links stashed away. But, that was a very nice function. Thanks, Andrew! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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