Conditional Opening of Excel file

[Jon]

Hi,

I have a complex workbook that relies on various SUMIF's from othe
reference workbooks. If these aren't open, then obviously the formula
don't work.

As it's for someone else; I want a piece of VB so that when they *open
the main workbook, it checks to see if the ref file is open, and i
not, it automatically opens it for them. A bit like this

sub worksheetopen '(Not sure where this should go in parent book)

if workbook("Referencefile.xls") is NOT open then
workbook("referencefile.xls").open
endif

endsub

Any ideas folks?:confused

 

[Frank Kabel]

Hi
if your problem are the SUMIF formulas you may cvhange
them to SUMPRODUCTformulas (which will work for closed
files also). e.g. change
=SUMIF(lookup_range,condition,sum_range)
to
=SUMPRODUCT(--(lookup_range=condition),sum_range)

 

[Jon]

Fair enough; I'm kinda scuppered though as I've done this in LOADS o
files .

Any chance of a solution all the same though; it'd be useful for load
of instances anyway (such as to support a powerpoint presentatio
load-up).

Thank

 

[Frank Kabel]

Hi
you can use the workbook_open event of your work´book
(this is automatically executed when opening your parent
workbook). Within this event procedure use a code like the
following:
Dim wbk As Workbook
Dim old_book as workbook
set old_book=activeworkbook
On Error Resume Next
Set wbk = Workbooks("DATA.xls")
On Error GoTo 0
If wbk Is Nothing Then
Workbooks.Open Filename:= _
"C:\DATA.xls"
end if
old_book.activate

 

[Jon]

Thanks Frank; actually I modified a piece of code you designed for
previous query of mine (similar to your post above). Hope the followin
is useful to other people too;



Sub Isitopen()

Dim wkb As Workbook

Set wkb = Nothing
On Error Resume Next
Set wkb = Workbooks("The name of the file.xls")
On Error GoTo 0

If wkb Is Nothing Then
MsgBox ("File not open")
Else
MsgBox ("File is open")
End If


End Sub


Thanks once agai