Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
Vlookup issues when source data is in another workbook? | Excel Discussion (Misc queries) | |||
Chart source data when copying from one workbook to another | Excel Programming | |||
External Data Source Questions | Excel Programming | |||
Changing data source passwords in workbook ? | Excel Programming |