Accessing Data in an Open Excel Spreadsheet

[Dave1500]

Hi,

I have an Excel spreadsheet which is refreshed on a minute by minute basis.
To ensure that the data is up to date the spreadsheet must remain open at all
times. I need to access the spreadsheet and extract the data and load it into
a database (could be Access, SQL Server 2000 or Oracle). However the method I
am using to access the data (linked server from SQL Server) only works on
non-open files.

Is there any way of extracting data from an open Excel spreadsheet and
loading it into a database?

Thanks in advance for any help.

Dave.

 

[Jim Rech]

A workbook can be open in an instance of Excel and yet accessible by other
processes if it is open read-only. Whether this helps in your situation I
have no idea. In code:

Workbooks("WB.XLS").ChangeFileAccess Mode:=xlReadOnly

In order for that workbook to be saved its file access would have to be
changed to xlReadWrite and that could only happen if another process did not
have it open with read/write access.

--
Jim
| Hi,
|
| I have an Excel spreadsheet which is refreshed on a minute by minute
basis.
| To ensure that the data is up to date the spreadsheet must remain open at
all
| times. I need to access the spreadsheet and extract the data and load it
into
| a database (could be Access, SQL Server 2000 or Oracle). However the
method I
| am using to access the data (linked server from SQL Server) only works on
| non-open files.
|
| Is there any way of extracting data from an open Excel spreadsheet and
| loading it into a database?
|
| Thanks in advance for any help.
|
| Dave.

 

[Erik Veldkamp]

In Access you can link a table in Excel, which is accessible even if the
spreadsheet is open.