Progress bar - copying a file

[yoko]

Hi,

I've got this code which copies a file from one location to another. Would it be possible to include a progress bar to show how far its complete as the file is quite large.

The code is...

Private Sub CommandButton1_Click()

If FileExists("C:\Archive.pst") Then
    cpy_email.Caption = "Copying..."
    CommandButton1.Caption = "Please wait"
    CommandButton1.Enabled = False
    CommandButton2.Enabled = False
    Application.Wait Now + TimeValue("00:00:01")
    On Error GoTo Err_FileInUse
    FileCopy "C:\Archive.pst", "M:\email\Archive.pst"
    Application.Wait Now + TimeValue("00:00:01")
    cpy_email.Caption = "Done"
    MsgBox "Done!", vbOKOnly, "Finished"
    CommandButton2.Enabled = True
    GoTo End_Sub:

Else
     MsgBox "File not found!", vbOKOnly, "Error!"
End If

Err_FileInUse:
            MsgBox "File is in use!", vbOKOnly, "Error"
            CommandButton2.Enabled = True
            CommandButton1.Enabled = True
            CommandButton1.Caption = "Backup!"
End_Sub:
End Sub

Many thanks,

 

[tusharm]

I realize this is over 2 weeks old but...

No, you cannot show a progress bar for the FileCopy statement. As far as VBA is concerned this is a single indivisible act and one cannot show a progress bar for a single VBA statement. Of course, if FileCopy itself shows a progress bar you will see one. Even if that is the case, you will have no control over the display of the progress bar.

 

[Ivan F Moala]

Whilst you can't get the detailed progress with the FileCopy statement
you can invoke Windows shell to give you this progress.

I won't go into great detail with this code but incorporate it into your routine.

Sub ShellCopy()

Set objShell = CreateObject("Shell.Application")
'//The source Folder to CopyFrom:
Set objFolder = objShell.NameSpace("C:\B")

'//The source Folder to CopyTo:
objFolder.CopyHere "C:\A_Book", &H0&

Set objShell = Nothing
Set objFolder = Nothing


MsgBox "The rest of my code is running NOW!!"


End Sub

 

[dforgacs]

Progress Bar - Copy File

Hi,
When you copy large files in windows a dialogue box come up that shows a progress bar. Is there a way to call that box up when using the copyfolder command?

Also in Ivan's code:
Set objFolder = objShell.NameSpace("C:\B")
I used my folder and path after namespace but objFolder = nothing, but I know the folder in there, is using namespace correct?

dforgacs

 

[Ivan F Moala]

What is the exact code you used

 

[yoko]

The problem is that the program is just to copy a file. I actually hide excel when the file is opened so that just a form with two buttons is displayed. Copy and Exit. When I click copy it just freezes until the files copied.

I just wanted something to come up and display that it was doing something and give an idea of how longs its going to take.

Cheers

 

[dforgacs]

Progress Bar

Hi,
I figured out what was wrong, I used a string variable set to the file name with path, once I typed in the exact path in namespace it worked.

Is it possible to have the windows copy message box with progress bar come up while the file is being copied?

Thanks!
dforgacs

 

[Ivan F Moala]

The problem is that the program is just to copy a file. I actually hide excel when the file is opened so that just a form with two buttons is displayed. Copy and Exit. When I click copy it just freezes until the files copied.

I just wanted something to come up and display that it was doing something and give an idea of how longs its going to take.

Cheers

Yoko, when you say "freezes" does it do this using the code I gave ?
It shouldn't freeze using the above code, it should display the Windows copy dialog. What OS are you using ?

 

[Ivan F Moala]

Re: Progress Bar

Hi,
I figured out what was wrong, I used a string variable set to the file name with path, once I typed in the exact path in namespace it worked.

Is it possible to have the windows copy message box with progress bar come up while the file is being copied?

Thanks!
dforgacs

I thought as much.

You cannot use string variables when using this Method.
The variables need to be Variant.

 

[dforgacs]

Progress Bar

Ivan,
I thought I had it working but the code runs and the message box comes up but it doesn't copy anything, I'll attach my code. I'm copying from My I:\ drive to my C:\ drive. I've used variants. Maybe you could take a look and tell me where I'm off. Thanks!
dforgacs

Option Explicit

Public Sub Workbook_Open()
Dim mypath As Variant
Dim newpath As Variant
Dim newpath2 As Variant
Dim mypath2 As Variant
Dim objshell As Object
Dim objfolder As Object


mypath = Application.ActiveWorkbook.Path
newpath = Windows.Application.DefaultFilePath
newpath2 = Left(newpath, 3) & "SLTS Exttract Database"
mypath2 = mypath & "\SLTS Extract Database"

Set objshell = CreateObject("Shell.Application")

Set objfolder = objshell.NameSpace(mypath2)

objfolder.CopyHere newpath2, &H0&

Set objshell = Nothing
Set objfolder = Nothing

MsgBox "The rest of my code is running now!!"

End Sub