Solved

ProgressBar update when opening an Excel File

Posted on 2001-08-02
16
172 Views
Last Modified: 2012-08-13
Does anyone know of a way to update the progressbar (increase the value) when opening an Excel file. The code I am using to open the file is: Workbooks.Open ('the name of the file')

Since the file takes about 20-30 seconds to open, I want to add a progress bar on the form. But, since the code can only be read one line a time, the progress bar's value will not change until the Excel file is open. I would like the progress bars value to change DURING opening of the file. Please let me know if you have an answer to this question. If you have some sample code that will even be better! Thanks!

PS - the Timer does not work because code can only be read one line at a time and during run time the code will stop at Workbooks.Open (the name of file) until the workbook open procedure is complete. Is there a way to get around this?
0
Comment
Question by:97luder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +2
16 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 20 total points
ID: 6346864
Because Excel does not trigger any events giving you the progress, it can't be done!
0
 
LVL 2

Expert Comment

by:smkkaleem
ID: 6347019
Use a timer control originally diaabled. Enable it right after the Workbooks.Open ('the name of the file') code.

Write code in the timer event to increase the progress bar value using the approximate time as the max value that your Excel file takes to load.

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6347032
I really think that the timer won't trigger the event while the Open will execute.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Expert Comment

by:akkalam
ID: 6347709
It is better to use Time Control.
--------------------------------------
Dim i as integer
dim flag as boolean

Private sub Command_click()

Opening Excel.....
flag=false

End sub

Private sub Timer1_Time()
if flag=true then
ProgressBar1.Value=<var>
<var>=<var>+10
else
ProgressBar1.value=100 'if it's Maxlimit is 100
endif
End sub

0
 
LVL 2

Expert Comment

by:smkkaleem
ID: 6350341
oops!!!Use a timer control originally disabled. Enable it right after the "DoEvents" line that should be inserted right after the Workbooks.Open ('the name of the file') code.

Write code in the timer event to increase the progress bar value using the approximate time as the max
value that your Excel file takes to load.

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6357977
Do you have your progress bar?
0
 

Author Comment

by:97luder
ID: 6361828
smkkaleem: The Timer control does not work because code can only be read one line at a time and during run time the code will stop at Workbooks.Open (the name of file) until the workbook open procedure is complete, then it will call the timer control which is already too late.

emoreau: You asked "Do you have your progress bar?". Im not sure what you mean. But, yes I have added the control 'Microsoft ProgressBar Control,  Version (6.0)' to VB.
0
 

Author Comment

by:97luder
ID: 6361829
smkkaleem: The Timer control does not work because code can only be read one line at a time and during run time the code will stop at Workbooks.Open (the name of file) until the workbook open procedure is complete, then it will call the timer control which is already too late.

emoreau: You asked "Do you have your progress bar?". Im not sure what you mean. But, yes I have added the control 'Microsoft ProgressBar Control,  Version (6.0)' to VB.
0
 

Author Comment

by:97luder
ID: 6361833
smkkaleem: The Timer control does not work because code can only be read one line at a time and during run time the code will stop at Workbooks.Open (the name of file) until the workbook open procedure is complete, then it will call the timer control which is already too late.

emoreau: You asked "Do you have your progress bar?". Im not sure what you mean. But, yes I have added the control 'Microsoft ProgressBar Control,  Version (6.0)' to VB.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6361999
What I meant is that we didn't have your news for a couples of days.

<The Timer control does not work>

I said right at the beginning!!!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6396914
97luder, you now have to do something with this question. Accept someone comment or delete it.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6449515
Please maintain:

Questions Asked 1
Last 10 Grades Given  
Question Grading Record 0 Answers Graded / 0 Answers Received
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6521946
Please comment or close the question.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6626530
Anybody here ?
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6767013
There has been no activity in this question in quite some time, and it looks like it has been abandoned. As part of our ongoing mission to clean up the topic areas, a Moderator will finalize this question within the next seven (7) days. At that time, either I or one of the other Moderators will force/accept the comment of emoreau.

DO NOT ACCEPT THIS COMMENT AS AN ANSWER. If you have further comments on this question or the recommendation, please leave them here.

Netminder
Community Support Moderator
Experts Exchange
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6789840
Per recommendation comment force/accepted by

Netminder
Community Support Moderator
Experts Exchange
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question