VB with Excel problem.

[dwlerwill]

Hi All

I have a VB program that opens up several excel spread sheets one at a time and looks for specific data. The program takes about 10 minutes to run (There is loads of data every day) but the problem I have is if while the program is running the user opens another spread sheet (Any even a blank one) the program then seems to switch from the spreadsheet it is running on to the newly opened one.

How can I stop this either by:

Stoping the user from Openening any other excel spread sheets or stopping VB flipping to the new excel workbook?

Your help is appreciated.

David Lerwill
"If at first you don't succeed go to the pub"

 

[SkipVought]

...the program then seems to switch from the spreadsheet it is running on to the newly opened one.

Therefore, you do not have an unambiguous reference set to the workbook/worksheet that you are processing.

You are probably using ActiveWorkbook or ActiveSheet. Declare an object that you Set to the workbook.sheet that you are referencing.


Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[combo]

Seems that you work with visible excel application and active workbook here. Instead you can create separate instance of excel and keep it hidden. Use direct references to used objects. For instance (after referencing Excel library):

dim xlApp as Excel.Application
dim xlWbk as Excel.Workbook
dim xlWks as Excel.Worksheet
Dim A(1 to 3,1 to 5) As Double
' set values to A
Set xlApp=New Excel.Application
Set xlWbk=xlApp.Workbooks.Add ' or: Set xlWbk=xlApp.Workbooks.Open(....)
set xlWks=xlWbk.Worksheets(1)
xlWks.Range("A1")="sample text"
xlWks.Range("A2:E4")=A
xlWbk.SaveAs FileName:="...."
xlWbk.Close SaveChanges:=False
xlApp.Quit

Working with 2D arrays and assigning to proper size ranges can speed-up code. See excel VBA help file for details.

combo

 

[vladk]

dwlerwill,

I would add even more. Using implicitly created Excel related objects without the object declaration, like SkipVought correctly suggests you to introduce, and/or by not setting them to Nothing after the use, you probably leaving Excel process in the memory after your program's end. You can easily see this forgotten process by hitting CTRL+ALT+DEL once and activating Processes tab. It can cause not only wasting your PC' resources but it can also cause instability and hanging of Excel.

vladk