Questions with using another workbook for data source

[Mike R.]

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...

[Sharad Naik]

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

Sharad

"Mike R." <[email protected]> wrote in message
news:[email protected]...
> 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...
>

[Mike R.]

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...
>

[Sharad Naik]

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." <[email protected]> wrote in message
news:[email protected]...
>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...
>>