Questions with using another workbook for data source
 

Greetings,
I have two workbooks that utilize the same data. It has become troublesome
to update the data in both workbooks when there is a change. I know it is
possible to create a third workbook and put the data there and then open it
as a hidden workbook. I am using the WB_open and WB_before close events to
help. I have been testing this process and have come across several
challenges:

1) when one WB is open and the user opens the other WB (or could open the
same one again), there is a message telling the user the datasource is
already open, do you want to reopen it.
2) Since the datasource closes using WB_before close, if the user hits CLOSE
and the save dialog comes up and they hit CANCEL, the datasource is closed
and no longer available.
3) If the user has both WB's open, and they close one, the datasource closes
and is no longer available.

What is the best way to accomplish using a hidden workbook for data? I have
played with the worksheet change event to check for the data source, but
there has got to be a better way than to constantly check for the data...
Please help, I am pulling my hair out with this one...

Questions with using another workbook for data source
 

What kind of data source you are using?
What engine are you using for the data?

Sharad

"Mike R." wrote in message
...
Greetings,
I have two workbooks that utilize the same data. It has become
troublesome
to update the data in both workbooks when there is a change. I know it is
possible to create a third workbook and put the data there and then open
it
as a hidden workbook. I am using the WB_open and WB_before close events
to
help. I have been testing this process and have come across several
challenges:

1) when one WB is open and the user opens the other WB (or could open the
same one again), there is a message telling the user the datasource is
already open, do you want to reopen it.
2) Since the datasource closes using WB_before close, if the user hits
CLOSE
and the save dialog comes up and they hit CANCEL, the datasource is closed
and no longer available.
3) If the user has both WB's open, and they close one, the datasource
closes
and is no longer available.

What is the best way to accomplish using a hidden workbook for data? I
have
played with the worksheet change event to check for the data source, but
there has got to be a better way than to constantly check for the data...
Please help, I am pulling my hair out with this one...

Questions with using another workbook for data source
 

I am using Excel for the data...just another workbook with a lot of defined
names. No engines... help please. thanks,
Mike

"Mike R." wrote:

Greetings,
I have two workbooks that utilize the same data. It has become troublesome
to update the data in both workbooks when there is a change. I know it is
possible to create a third workbook and put the data there and then open it
as a hidden workbook. I am using the WB_open and WB_before close events to
help. I have been testing this process and have come across several
challenges:

1) when one WB is open and the user opens the other WB (or could open the
same one again), there is a message telling the user the datasource is
already open, do you want to reopen it.
2) Since the datasource closes using WB_before close, if the user hits CLOSE
and the save dialog comes up and they hit CANCEL, the datasource is closed
and no longer available.
3) If the user has both WB's open, and they close one, the datasource closes
and is no longer available.

What is the best way to accomplish using a hidden workbook for data? I have
played with the worksheet change event to check for the data source, but
there has got to be a better way than to constantly check for the data...
Please help, I am pulling my hair out with this one...

Questions with using another workbook for data source
 

Oh OK. So by opening/closing datasource you meant opening closing the
workbook containing data.

Let us assume:
Name of Workbok containing data = data.xls
Name of 1 workbook utilizing data = book1.xls
Name of 2nd workbook utilizing data = book2.xls

This will go in to the Workbook_Open event of both book1 and book2

Private Sub Workbook_Open()
Dim dataOpen As Boolean, w As Workbook
For Each w In Application.Workbooks
If w.Name = "data.xls" Then
dataOpen = True
Exit For
End If
Next w
If Not dataOpen Then
Workbooks.Open "C:\data.xls"
End If
Windows("data.xls").Visible = False
End Sub

Following will go in to Workbook_BeforeClose event of book1:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim book2Open As Boolean, w As Workbook
For Each w In Application.Workbooks
If w.Name = "book2.xls" Then
book2Open = True
Exit For
End If
Next w
If Not book2Open Then
Workbooks("data.xls").Close SaveChanges:=False
End If
End Sub

And following in to Workbook_BeforeClose event of book2:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim book1Open As Boolean, w As Workbook
For Each w In Application.Workbooks
If w.Name = "book1.xls" Then
book1Open = True
Exit For
End If
Next w

If Not book1Open Then
Workbooks("data.xls").Close SaveChanges:=False
End If
End Sub

Thus the workbooks will try to not open data.xls if it already open
and will not try to close if otherworkbook is open.

Sharad

"Mike R." wrote in message
...
I am using Excel for the data...just another workbook with a lot of defined
names. No engines... help please. thanks,
Mike

"Mike R." wrote:

Greetings,
I have two workbooks that utilize the same data. It has become
troublesome
to update the data in both workbooks when there is a change. I know it
is
possible to create a third workbook and put the data there and then open
it
as a hidden workbook. I am using the WB_open and WB_before close events
to
help. I have been testing this process and have come across several
challenges:

1) when one WB is open and the user opens the other WB (or could open the
same one again), there is a message telling the user the datasource is
already open, do you want to reopen it.
2) Since the datasource closes using WB_before close, if the user hits
CLOSE
and the save dialog comes up and they hit CANCEL, the datasource is
closed
and no longer available.
3) If the user has both WB's open, and they close one, the datasource
closes
and is no longer available.

What is the best way to accomplish using a hidden workbook for data? I
have
played with the worksheet change event to check for the data source, but
there has got to be a better way than to constantly check for the data...
Please help, I am pulling my hair out with this one...