Solved

ProgressBar update when opening an Excel File

Posted on 2001-08-02
16
170 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

786 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