Copying files

[Ed Handley]

I want to use vba to copy a file from the network to a local drive, and show
the progress of the copy. Is there any code I can use to do this or will I
have to fake (guestimete) the progress?

Ed

 

[Guest]

Hi Ed,

The VBA command you will need is
FileCopy FileA, FileB

e.g.:
Sub MyFileCopyProcedure()
Dim s as String
Const Drive = "C:\"
Const Directory = "C:\My files"
Const FileSource = "FileA.xls"
Const FileDestination = "FileB.xls"

On Error GoTo Err1
ChDrive Drive
On Error GoTo Err2
ChDir Directory
FileCopy FileSource, FileDestination
Exit Sub

Err1:
MsgBox "The drive" & Drive & "is not accessible"
Exit Sub

Err2:
MsgBox "The Directory" & Directory & "doesn't exist"
Exit Sub

End Sub

ProgressBar:
MS offers a specific ProgressBar which can be displayed on a form. You will find it under the icon 'more controls' and is named 'Microsoft ProgressBar Control". You can add on a form a normal command button and add some VBA to be executed when clicking the button.

e.g.
Private Sub MyCommand()
Dim i as Integer

Me!ProgressBar1.Min = 0
Me!ProgressBar1.Max = 10000

For i = Me!ProgressBar1.Min to Me!ProgressBar1.Max
Me! ProgressBar1 = i
i = i +1
Next
Me!ProgressBar1.Value = 0
End Sub

 

[Douglas J. Steele]

While it's true that there's a progress bar capability in Access, in order
to use it, you need to be able to have events that will show progress. When
you use FileCopy, I don't believe any events are exposed that you'd be able
to link to the progress bar.

If I recall correctly, the SHFileOperation API will show the progress
(Sorry: I didn't bother confirming before posting). Randy Birch has an
example of how to use it at http://vbnet.mvps.org/code/shell/shfileopadv.htm
(Obligatory warning about Randy's site: it's aimed at VB programmers, not
Access programmers. Sometimes the form-related instructions will not work in
Access, due to the differences in the Forms model between the two
applications. I did a quick check of this particular sample, though, and I
think it'll port into Access without any problems)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Ed,

The VBA command you will need is
FileCopy FileA, FileB

e.g.:
Sub MyFileCopyProcedure()
Dim s as String
Const Drive = "C:\"
Const Directory = "C:\My files"
Const FileSource = "FileA.xls"
Const FileDestination = "FileB.xls"

On Error GoTo Err1
ChDrive Drive
On Error GoTo Err2
ChDir Directory
FileCopy FileSource, FileDestination
Exit Sub

Err1:
MsgBox "The drive" & Drive & "is not accessible"
Exit Sub

Err2:
MsgBox "The Directory" & Directory & "doesn't exist"
Exit Sub

End Sub

ProgressBar:
MS offers a specific ProgressBar which can be displayed on a form. You

will find it under the icon 'more controls' and is named 'Microsoft
ProgressBar Control". You can add on a form a normal command button and add
some VBA to be executed when clicking the button.