Solved Show progress during export to Word

[Rene vK]

I have created a popup and very rigid routine for creating a dossier in MS Word with data from my application. Because creating the file is taking sometime (not extremely) I would like to keep the user entertained. my question, is there a better way to show progress?

Everything works but I would like to 'progress' my skills.

Sub createClientdossier(FolderAndFile As String, CDtemplate As String)
'' export code by Steve Bishop
'  PrBar = Rectangle, width= 4500 ticks

Dim wDoc As Word.Document
Dim wApp As Word.Application
    PrBar.Width = 90 '(2 * 45)
    prText = "0%"

Set wApp = New Word.Application
    PrBar.Width = 1125 '(25 * 45)
    prText = "25%"
Set wDoc = wApp.Documents.Open(CDtemplate)
    PrBar.Width = 2250 '(50 * 45)
    prText = "50%"
  wDoc.Bookmarks("Name").Range.Text = Nz(Klant.Naam, "")
  wDoc.Bookmarks("Adres").Range.Text = Nz(Klant.Adres, "")
  wDoc.Bookmarks("City").Range.Text = Nz(Klant.Postcode & " " & Klant.Plaats, "")
  wDoc.Bookmarks("DateOfBirth").Range.Text = Nz(Klant.GeboorteDatum, "")
  wDoc.SaveAs2 FolderAndFile
 
  wDoc.Bookmarks("Name").Range.Delete wdCharacter, Len(Nz(Klant.Naam, ""))
  wDoc.Bookmarks("Adres").Range.Delete wdCharacter, Len(Nz(Klant.Adres, ""))
  wDoc.Bookmarks("City").Range.Delete wdCharacter, Len(Nz(Klant.Postcode & " " & Klant.Plaats, ""))
  wDoc.Bookmarks("DateOfBirth").Range.Delete wdCharacter, Len(Nz(Klant.GeboorteDatum, ""))
    PrBar.Width = 4500 '(100 * 45)
    prText = "100%"

wDoc.Close False
wApp.Quit

Set wDoc = Nothing
Set wApp = Nothing
    
Forms![MainScreen].MakeList (Klant.Folder)
DoCmd.Hourglass (False)
DoCmd.Close acForm, "Dossier"

End Sub

 

[MajP]

There are several custom progress bars you can find on this site. @isladogs has one here

Progress Bar..

Hi, so i am looking for some code to run a progress bar form at the start of my code and then finish when the code as completed any ideas?

www.access-programmers.co.uk

However, most of these require a loop of activities that occur and a known amount of total activities.
You may be able to try this one and update N (the activity) after each call to word. So N would be about 11 and update n after each call to Word.

 

[The_Doc_Man]

How long does this process take? Putting a progress bar is OK, but unless you have a really slow computer OR you are doing something over a network that is slow, I would think that working four fields from the template should take vary little time. Further, whatever you think you are doing with prText, you aren't doing anything visible.

And by the way, if using positional arguments, wDoc.Close wants an enumerated constant in argument slot 1, not true or false.

Document.Close method (Word)

Office VBA reference topic

docs.microsoft.com

Then there is the matter of overkill. You appear to be erasing what you stored in certain fields in the template. But when you did the SaveAs2, you (of course) saved it in an alternate file. If you just do a wDoc.Close wdDoNotSaveChanges, your template wouldn't be updated anyway. Which might be fast enough that your users wouldn't even see the progress bar.

 

[MajP]

I forgot to ask the same question. Why would this take long?

 

[Rene vK]

Thx guys, appreciate your comments

Offcourse it does not take a minute, but a couple of seconds. I was just trying to entertain the user. The time taken must have something to do with a usb-drive or because I'm working from via a cloudservice.

@The_Doc_Man: About the export-cycle; I just copied from a video lesson, it worked and I was satisfied. The thing about wDoc.Close wdDoNotSaveChanges is a valuable one, thx for that. PrText is made visible with code behind the, calling, button

@MajP: I have a working progressBar bu thx for the link.

 

[MajP]

I have a working progressBar bu thx for the link

Do you have a progress bar for this or just a spinning hour glass? Because I am not sure how you can figure out the progress. I Was wondering if my suggestion would even work.

 

[Rene vK]

Do you have a progress bar for this or just a spinning hour glass? Because I am not sure how you can figure out the progress. I Was wondering if my suggestion would even work.

MajP, at the start I just had an hourglas/spinner but I wanted something a little more visual.
I now use a rectangle of 4500 twips long and a textbox. I choose the values for prBox.width and prText a bit at random. Just created a couple of dossiers and made some changes to my satisfaction. The user will see a growing green line and percentage on the form, not a real progressbar.

I know it is not the most proffessional way of work but it is to my satisfaction and it does not take a lot of resources.

 

[MajP]

I now use a rectangle of 4500 twips long and a textbox. I choose the values for prBox.width and prText a bit at random. Just created a couple of dossiers and made some changes to my satisfaction. The user will see a growing green line and percentage on the form, not a real progressbar.

I see it now, and that would be the exact same approach as I was thinking. The other progress bars may be more aesthetic, but it would basically do the same.

 

[Rene vK]

I see it now, and that would be the exact same approach as I was thinking. The other progress bars may be more aesthetic, but it would basically do the same.

I use another progressbar, on a form, for not boring the user, as an alternative to 'sleep'

 

[isladogs]

Do bear in mind that running a progress bar will actually increase e time needed to complete a process due to the additional time needed to keep updating the length of the progress bar.
I would recommend a different approach for tasks that only take a few seconds - especially if these have no obvious steps that can be used.
Instead I display an information caption e.g. Updating Word document... whilst the process runs and hide it at the end.
If you want to go further, use a timer event (if feasible) which adds an extra dot at the end of the caption each time the timer runs.

 

[Rene vK]

Do bear in mind that running a progress bar will actually increase e time needed to complete a process due to the additional time needed to keep updating the length of the progress bar.
I would recommend a different approach for tasks that only take a few seconds - especially if these have no obvious steps that can be used.
Instead I display an information caption e.g. Updating Word document... whilst the process runs and hide it at the end.
If you want to go further, use a timer event (if feasible) which adds an extra dot at the end of the caption each time the timer runs.

Thank you Isladogs,

You are right! on the code shown in my post I jump from 0 via 25 & 50 to 100%. It is a bit random choosen and checked during use. Just showing a small text was my first approach, your suggestion is an in-between, nice one.
In another situation I created a timer-loop for a popup-form with a progressbar(like) context. This is pure for creating a pause before jumping to a next part of a string of subs. the popup comes up right after a Shellcommand that re-creates a pdf file in the background. That file is to be used in the next step, but is not immediatly ready for grabs.

thank you all...

 

[isladogs]

Not quite clear what your last paragraph means.
If it helps, my progress bar example in the link uses a timer and also shows updating text in a caption...
Progress Bar - Mendip Data Systems

 

[The_Doc_Man]

As far as progress bars go, I have ALWAYS "rolled my own" because the Access progress bar that is in the bottom of the Access window is just too "grainy" (or perhaps "blocky" is a better description) to show what is REALLY going on. My own preference was two rectangles that were very short in height but very long in width. One of them had a border but no background. The other (the one that changed size) had a background but no border. Worked just fine for what I was doing, which took about 20-30 seconds if I was exporting based on a list rather than a single item.

 

[Rene vK]

Not qui clear what your last paragraph means.
If it helps, my progress bar example in the link uses a timer and also shows updating text in a caption...
Progress Bar - Mendip Data Systems

in the first Sub I combine two pdf files to one with 'PDFtk server' and that takes some time.
That new, combined, file is needed in the next Sub but Access is to quick and can't find that new file.
For that I popup a form, with an progressbar, that is controlled by the Timer. I let it run for 4 seconds and the the combined file is found in the sequence of Subs

Chain of events: pdf from Access > background stamp with PDFtk server > copy the new file > etc. etc. > original file is deleted!

Great example on your website!

 

[isladogs]

Thanks for clarifying ...and thanks for feedback