Excel as a database

[Michael]

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

 

[urkec]

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