Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

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

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
Noono
Asked:
Noono
1 Solution
 
Howler_FishCommented:
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
 
NoonoAuthor Commented:
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
 
criCommented:
Why not using the Open _Statement_ (VBA) ? Write Open in a module, put cursor on it and press F1, select the VBA variety.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NoonoAuthor Commented:
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
 
NoonoAuthor Commented:
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
 
criCommented:
What has to be done with the text files ? Perhaps it can be done directly by a string operation.
0
 
bruintjeCommented:
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
 
dbase118Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now