Opening a Workbook in the Background

[Guest]

I have a main "front end" workbook that people will be opening to interact
with. Whenever this main workbook opens, I would like it to open another
"data" workbook in the BACKGROUND, so I can copy data back and forth between
them using various scripts. I don't want the front end user to be able to
see the data sheet, or (ideally) even know that it has been opened in the
background.

In the code for "ThisWorkbook" in the main front end workbook, I have:

Dim objApp As Excel.Application
Private Sub Workbook_Open()
Set objApp = CreateObject("Excel.Application")
objApp.Workbooks.Open ("X:\pathto\Data.xls")
objApp.Visible = False
End Sub

In the code for the main worksheet in the main front end workbook, I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DataSheet As Worksheet
Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1)
[.....]
End Sub

So, with the code as I have shown above, I usually get an "Object
Expected" error on my Set Datasheet line, which makes me think it isn't
recognizing my public objApp variable that I declared in the code for
ThisWorkbook. Should I be declaring it differently?

If I simply remove the "objApp." prefix in that line, of course it says
subscript out of range. If I use a regular Worksheets.Open (instead of
creating a new app object), it brings the Data.xls workbook to the
foreground and I can't figure out how to hide it.... I want the opening of
the Data workbook to be stealthy and hidden, if that is at all possible....

Any help at all would be most appreciated. Thanks,

---G

 

[STEVE BELL]

Enclosing your code with:

Application.Screenupdating = False

Application.Screenupdating = True

This prevents the screen from being updated and the user will not see what
is happening.  This also speeds up the process.
Prevent using activate or select within your code to run it faster and more
efficiently

Workbooks("A").Activate
Sheets(1).Select
Range("A1").Select
Selection.Copy

Workbooks("B").Activate
Sheets(1).Select
Range("A1").Select
Selection.Paste

is very messy.

Use something like:
Workbooks("B").Sheets(1).Range("A1") =
Workbooks("A").Sheets(1).Range("A1")
You can also append .Value, or .Formula, or .....

Than there are contructs using:
With

End With

But with screenupdating turned off you can do all the selecting you want as
long as you select your primary workbook at the end and the user
won't see what is happing.

rand451

 

[George Nicholson]

Dim acts much like Private. If you want the variable to be recognized by
another module, use Public:
Public objApp As Excel.Application

HTH,

 

[Guest]

Enclosing your code with:

Application.Screenupdating = False

Application.Screenupdating = True

This prevents the screen from being updated and the user will not see what
is happening.  This also speeds up the process.[/QUOTE]

Wow... this is interesting, and I didn't know about it.  So thank you for
the tip.

However, in this particular case, I would like (if possible) to be able to
LEAVE the workbook open in the background, so I can reference it "on demand"
as it were with scripts, but I would like it to be invisible.

The screenupdating thing doesn't fly for this, because I would like the user
to be able to do things and interact with the "front end" workbook, while the
data workbook is open (but hidden).

Any thoughts?

 

[Peter T]

Looks like you have declared objApp as a modular variable in the
ThisWorkbook module which will not be visible elsewhere in your project,
such as your sheet modules. In a normal module try -
Public objApp As Excel.Application

To make things easier why not save your Data file as a hidden workbook and
open in the normal way in same instance as your main workbook. Though of
course user might see it in the Window > Unhide.

Regards,
Peter T

 

[Guest]

Dim acts much like Private. If you want the variable to be recognized by
another module, use Public:
Public objApp As Excel.Application

Thank you... I didn't know that. I figured "dim" was generic, with "Public"
and "Private" as special cases.

However, unfortunately *just* changing the declaration line to

Public objApp As Excel.Application

in the ThisWorkbook declaration didn't fix the problem... I still get an
"Object Required" error on the line:

Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1)

in the code for the worksheet.

Could this be because one is declarated in the code for the workbook, but
the subroutine that uses the variable is in the code for the worksheet? Do I
need to do something to explicitly declare the thing as "global" or something?

 

[STEVE BELL]

Another thing to keep in mind:
In your code you can hide and unhide any workbook. This way you can unhide
the workbook and workwith it and than rehide it when finished. This way the
user will never see it or have access to it. And you might be able to use
xlVeryHidden to make sure that the user can't find it.