Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel as a database
Hello everyone,
I have an Excel workbook that reads data from Application A to a workbook with the name ExcelDB in Sheet1 based on a windows timer function. The ExcelDB workbook lives on machine A (network share) and runs continuously (24x7). Data are getting updated to the workbook every 60 seconds. Note that first all data in Sheet1 are cleared out (including headers) and then data are added from Row 1 until Row XXX. My goal is to use ExcelDB workbook as a database. Note that ExcelDB is NOT a shared workbook. On machine B I have a separate workbook with the name ExcelPivotTables (Sheetname = PT). In worksheet PT I have 10 different PivotTables that all get their data from the ExcelDB Sheet1 Range (A1:IV65536). I also have a timer function in the ExcelPivotTables workbook to refresh all of the PivotTables every 75 seconds or so. The idea is never to get to a situation where the PT1 in ExcelPivotTables workbook refreshes but there are empty data in ExcelDB workbook (Sheet1). Most of the times this works however sometimes Excel crashes. Any ideas how to approach this problem? Perhaps someone else wanted to use Excel as a database (assuming the database updates frequently) ? Thank in advance Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel as a database
"Michael" wrote:
Hello everyone, I have an Excel workbook that reads data from Application A to a workbook with the name ExcelDB in Sheet1 based on a windows timer function. The ExcelDB workbook lives on machine A (network share) and runs continuously (24x7). Data are getting updated to the workbook every 60 seconds. Note that first all data in Sheet1 are cleared out (including headers) and then data are added from Row 1 until Row XXX. My goal is to use ExcelDB workbook as a database. Note that ExcelDB is NOT a shared workbook. On machine B I have a separate workbook with the name ExcelPivotTables (Sheetname = PT). In worksheet PT I have 10 different PivotTables that all get their data from the ExcelDB Sheet1 Range (A1:IV65536). I also have a timer function in the ExcelPivotTables workbook to refresh all of the PivotTables every 75 seconds or so. The idea is never to get to a situation where the PT1 in ExcelPivotTables workbook refreshes but there are empty data in ExcelDB workbook (Sheet1). Most of the times this works however sometimes Excel crashes. Any ideas how to approach this problem? Perhaps someone else wanted to use Excel as a database (assuming the database updates frequently) ? Thank in advance Michael If you have control over both Workbooks you could move all the code to one Workbook and synchronize execution 1. Load data from the application to ExcelDB 2. When finished, load data to ExcelPivotTables. Or you could load data from the application directly to ExcelPivotTables (If ExcelDb doesnt make any changes to the data). Or you could try to set flags of some kind(if ExcelDb..Cells (x,y) = "Loading from app" then wait for some time... and if ExcelDb..Cells (y,z) = "Loading to ExcelPivotTables" wait etc). -- urkec |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Convert Excel database to dBASE database? | Excel Discussion (Misc queries) | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Moved database, how do I change link to the database in excel | Excel Discussion (Misc queries) | |||
Using Excel as a database and need macro or vba to take data entered on one tab and update the database by adding to the next avail row | Excel Programming |