Solved: VBA to copy net file to local disk

[LorenH]

I use the following macro to scan a Word document for hyperlinks which point to JPG photos on the net. The maco opens the file in a separate window after which I manually copy the photo to a disk drive. The macro also changes the hyperlink to point to the local disk.
My Question: Is there code that will copy the file to the disk directly? This would be similar to "save target as" in a htlm application.
[VBA]
Sub savetarget()
For Each aHL In ActiveDocument.Hyperlinks
aa = aHL.Address
sp = Len(aa): While Mid$(aa, sp, 1) <> "/"
sp = sp - 1: Wend
aa1 = Mid$(aa, sp + 1)
ActiveDocument.FollowHyperlink Address:=aa
aHL.Address = aa1
Next aHL

End Sub
[/VBA]

[jamescol]

Copying the file is fairly straightforward. The default behavior for copying is to overwrite an existing file with the same name. Use the optional argument "overwrite" to change this behavior to False.
[vba]
Sub TestCopyFile()

Dim strSourceDir, strDestDir, strSourceFile as String

strSourceDir = "h:\home\"
strSourceFile = "myown.file"

strDestDir = "c:\mydata\"

Set myFile = CreateObject("Scripting.FileSystemObject")

myFile.CopyFile strSourceDir & strSourceFile, strDestDir & strSourceFile

'Optional behavior to prevent overwriting an existing file with the same name
'myFile.CopyFile strSourceDir & strSourceFile, strDestDir & strSourceFile, False


End Sub
[/vba]

Or if you want to move the file: (If there is an existing file or directory in the destination location with the same name as the source file, the move will fail)
[vba]
Sub TestMoveFile()

Dim strSourceDir, strDestDir, strSourceFile as String

strSourceDir = "h:\home\"
strSourceFile = "myown.file"

strDestDir = "c:\mydata\"

Set myFile = CreateObject("Scripting.FileSystemObject")

myFile.MoveFile strSourceDir & strSourceFile, strDestDir & strSourceFile

End Sub
[/vba]

Make sure you have a reference to the Microsoft Scripting Runtime:

1. From VBE Click Tools | References
2. Scroll to Microsoft Scripting Runtime and check the box
3. Click OK

You may need to install Microsoft Scripting Runtime on your own or client computers. The download is at http://msdn.microsoft.com/library/de...ist/webdev.asp

Cheers,
James

[jamescol]

Alternately, you can also use the built-in Word function. Example from Word Help:

[vba]
Dim SourceFile, DestinationFile
SourceFile = "SRCFILE"
DestinationFile = "DESTFILE"
FileCopy SourceFile, DestinationFile

[/vba]

James

[LorenH]

James--
Thank you for your reply. I have tries both of these without success.
It works copying from one file on my computer to another, but the file I want to copy is on a website. (maybe down load is the correct term)
Should this work using the internet address as the source file? Can it be copied from the window opened by hyperlink.follow ? (error recieved is Runtime error '52' Bad file name or number)

[jamescol]

Loren,
The samples I provided earlier will not work when the original file resides on a web site. I never had to use code to copy a file form a web site before, so this took some research. I wasn't able to find anything in the native Office/Word object models. There is an Activex control you can use, though, to accomplish the download.

For this procedure to wrok, you need to reference the WinHTTP services:

1. From VBE select Tools | References
2. Scroll down to Microsoft WinHTTP Service, version 5.x and check its box
3. Click OK

The following code sample will copy a binary file from a URL to the local drive.

Let us know how this works for you.

James
[vba]
Sub CopyHTTPFile()

' Create an array to hold the response data.
Dim arrDownloadedBytes() As Byte
Dim WinHttpReq as WinHttpRequest
Dim strURL, strLocalPath, strLocalFileName as String


Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

'In case you have V5.0 instead of v5.1 - use this line
'Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.0")

' create the HTTP Request
WinHttpReq.Open "GET", strURL, False

' Send the request
WinHttpReq.Send


' copy the response body to a local file
Open strLocalPath & strLocalFileName For Binary As #1
arrDownloadedBytes() = WinHttpReq.ResponseBody
Put #1, 1, arrDownloadedBytes()
Close

End Sub
[/vba]

[LorenH]

Works Great!! I don't pretend to understand exactly what the code is doing buy I do know it will save me a coulple hours a week of clicking and waiting. Thank you so much.

[jamescol]

Loren,
Glad it works! It's always nice to shave a couple of hours from a work week with automation!! Just don't let your boss in on the secret

Hope you become a regular visitor.

Cheers,
James