How to provide a file progress bar when downloading a large file

[tmd63]

I have this code that opens a workbook on a local website on our intranet and then copies the sheet into a local file.

    Workbooks.Open ("http://teamspace.****.com/sites/****/****/Shared%20Documents/Lookup.xls")

    LastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    ActiveSheet.Range("A1:M" & LastRow).Select
    Selection.Copy
    
    Application.DisplayAlerts = False
    
    ActiveWorkbook.Close

But this is a large file and the copy takes time to work. Is there some way of displaying a file copy progress bar? Note this is part of a much larger code and I need to insert the progress in here instead of putting all the code inside a progress form as I have seen done before.

 

[JackDanIce]

Instead of a progress bar, you could use the StatusBar to update progress with. It would be less coding to do and easier to insert into your existing code.

Search online for

Application.StatusBar

, you should find lots of examples.

One way to speed the above up is to make a local copy of the workbook and then export the data out of it.

I have similar problems at work, when opening files from none EMEA based locations just to export data and found copying the file first, (say to C:\Temp\) made a noticeable improvement.

 

[tmd63]

I tried searching and nothing appears to solve my issue.

 

[tmd63]

Oh. I was missing some code here.

   Workbooks.Open ("http://teamspace.****.com/sites/****/****/Shared%20Documents/Lookup.xls")

    LastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    ActiveSheet.Range("A1:M" & LastRow).Select
    Selection.Copy
    
    Application.DisplayAlerts = False
    
    ActiveWorkbook.Close
    
    Worksheets("Lookup").Select
    ActiveSheet.Range("A1").Select
    Worksheets("Lookup").Paste

I have nothing here to provide a reference to the progress of the copy to be able to code a progress bar, so how can I get a progress bar to show the code is still running?

 

[JackDanIce]

I think you missed my point, I am recommending forget the progress bar entirely and use the StatusBar (a native feature to VBA) to inform the user of the copying status.

On a separate point, if you copied the file to a local destination to where the macro is running from, it will open the file much faster, than trying to open it from a sharepoint location. You can always delete the copied file afterwards.

Combining the two above, if you copy the file to a local destination, you may find you do not need a progress bar at all, which would save you having to insert code to provide something for your end-user that may not be needed.

 

[tmd63]

Hi Jack,
Sorry, I did see the statusbar but still couldnt find how to do it without providing a percentage of the file information. There doesnt appear to be a method that generically give a status progress.

Unless I am not searching correctly. Can you provide any information?

Most of the people that will use this excel sheet have little space available to copy the file locally, so I have to make it robust for this type of situation. I dont want to get into the same issue as Google play where it refuses to download because the C drive is full despite the application being located on a D drive that has space.

 

[BobUmlas]

While the file is opening, you can't display a statusbar which changes as it's opening because the macro pauses until it's done opening. At best, you'd see 0% then 100% (which takes a statusbar message before opening and one afterwards) which is of no help. Maybe just give a "Please wait while the file opens" message.

 

[JackDanIce]

As Bob points out, VBA executes sequentially, it will open the workbook then pause and won't execute the 'next' line of code until the workbook is opened and control is handed back to VBA.

Statusbar code suggestion:

Application.StatusBar = "Please wait whilst file opens, this may take some time.

 

[tmd63]

Thanks. Not quite what I had in mind, but I understand the reason it cannot be done.
Looks like I will have to use the status bar and ask the user to wait. It is a pity there is not an windows API call or code that could be used to provide a download bar.

 

[JackDanIce]

You could look into using an ADO connection via VBA, it should read the data in faster (bypasses having to open and close the file) but I do not know enough about this yet to recommend code.

Personally though, I still think you should consider filecopy solution - depending on how quickly you want to implement this/your Users need the functionality of the full code.

You can copy to other locations, it doesn't have to be C:\Temp. D:\, E:\ anywhere you have a local place to copy the file to.

Have you tried to time the difference between opening a file from your sharepoint vs opening same file locally?