?
Solved

Open a workbook for processing in the background (Excel 2000)

Posted on 2003-02-23
9
Medium Priority
?
443 Views
Last Modified: 2012-05-04
Is there a way to open a workbook using VBA's Open method (or other) without it being activated?

I'm opening, processing and closing about 40 workbooks into a flat text file, but each open is jerky - even with Application.ScreenUpdating = False.

I would rather open the workbook into the background (somehow) and process it without the user seeing everything going on in the background.
0
Comment
Question by:Noono
[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
9 Comments
 
LVL 1

Expert Comment

by:Howler_Fish
ID: 8005933
Try using the Application.Windowstate = xlMinimized
I have found in the past that it offers better performance than Application.ScreenUpdating toggling.  When you are finished just set the windowstate = xlNormal.

Hope this helps,

HF
0
 

Author Comment

by:Noono
ID: 8005966
Hi Howler_Fish,

Unfortunately I want to show the user the progress of the operation using a progress bar.  I agree ScreenUpdating toggling is a bit of a nuisance and sometimes troublesome, but I do want to keep the main worksheet maximized.

Let me know if I've misinterpreted your answer

Noono
0
 
LVL 13

Expert Comment

by:cri
ID: 8006475
Why not using the Open _Statement_ (VBA) ? Write Open in a module, put cursor on it and press F1, select the VBA variety.
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:Noono
ID: 8012008
I'm using the Open_Statement_(VBA) to open the text files for output - works nice.

I'm using "Workbooks.Open WorkbookFullPath, ReadOnly:=True" where the WorkbookFullPath is the full path to the workbook I want to open.

It seems that this method always activates the workbook once it is open.  If possible, I'd love a way to open a workbook as read-only, but not activated so the user doesn't even see the workbook opened into Excel...  :)

I wonder is there an overloaded method somewhere out there?
0
 

Accepted Solution

by:
Noono earned 0 total points
ID: 8012707
I've found a solution to my problem, but with it came another.

I created a new instance of the Excel application, left it Application.Visible=False, and pulled the data from there.  This stopped the screen flickering wonderfully since I'm using an invisible instance of the application to do the processing, but ran about 10 times slower.

Here's the code snippet I used:
----------------------

Dim expApp As Excel.Application
Dim expWB As Excel.Workbook

Set expApp = New Excel.Application ' I also tried CreateObject

With expWB
' Do thousands of workbook operations
End With

expApp.Quit
Set expApp = Nothing

----------------------

As I said, since I'm using another instance of the Excel Application, the whole operation seems to run __much__ slower.  I've currently reverted back to toggling Application.ScreenUpdating = False/True.

Any suggestions as to how I can speed up the use of the second application?  Is the speed related to where the code itself is executing?  My gears are starting to churn now!

Thanks for your help so far!

Noono
0
 
LVL 13

Expert Comment

by:cri
ID: 8014415
What has to be done with the text files ? Perhaps it can be done directly by a string operation.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 9283574
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in the next round.  I would appreciate any comments by the experts that would help me in making a recommendation.


It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
EE Cleanup Volunteer
0
 
LVL 15

Expert Comment

by:dbase118
ID: 9582903
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ \ Points Refunded
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

dbase118
EE Cleanup Volunteer
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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