Using Worksheet as RecordSet

[Jamie Jackson]

Hi Folks,

I have lots of occasions to use worksheets as database tables. As far as I know, the natural way to do this (correct me if I'm wrong) is to open an ADODB connection to the workbook, using the workbook's path.

Please Login or Register  to view this content.

However, this has a side effect, in that changes to the "table" worksheet aren't available until the workbook is saved down. I would like to query the worksheet and get the new data without having to save down first.

So, the question is: Is there a way to use a worksheet as a table in ThisWorksheet so that it doesn't need to open an extra file connection (to an undesirably stale version of the workbook)?

Thanks,
Jamie

[Leith Ross]

Hello Jamie,

I am not sure you can get around this. The ADODB connects to the saved workbook. When Excel opens, it creates a mirror copy that contains the current workbook changes. These changes aren't realized until the workbook is saved.

[TimK]

Sorry - but I don't have a clear understanding of the situation.

Are you calling this code from another Excel workbook?
Is the datasource data potentially being changed by others?
Are your changes what makes the data 'stale'?

[Jamie Jackson]

Hello Jamie,

I am not sure you can get around this. The ADODB connects to the saved workbook. When Excel opens, it creates a mirror copy that contains the current workbook changes. These changes aren't realized until the workbook is saved.

Okay, then I guess that settles it for ADODB. However, is there some other way that I don't know about to access a worksheet as a table in ThisWorkbook (i.e., the workbook being edited).

Thanks,
Jamie

[Jamie Jackson]

Sorry - but I don't have a clear understanding of the situation.

Are you calling this code from another Excel workbook?

No, they are one and the same. (Caller=Called Workbook)

Is the datasource data potentially being changed by others?

No--not that I care about, anyway.

Are your changes what makes the data 'stale'?

Yes, exactly. *My* unsaved changes are what makes the (saved) data "stale."

Thanks for asking,
Jamie

[T-J]

Jamie - Maybe you are doing something different, but using VBA with an ADODB connection and simple SQL to create a recordset, I'm able to query a worksheet (table) of the current workbook and return the current data, without saving the workbook between changes to the data.

Another way is to use Data > Import External Data, which creates a Querytable. This also doesn't need the workbook to be saved between data changes - just click the Refresh Data button on the External Data Toolbar.

[Jamie Jackson]

Jamie - Maybe you are doing something different, but using VBA with an ADODB connection and simple SQL to create a recordset, I'm able to query a worksheet (table) of the current workbook and return the current data, without saving the workbook between changes to the data.

Another way is to use Data > Import External Data, which creates a Querytable. This also doesn't need the workbook to be saved between data changes - just click the Refresh Data button on the External Data Toolbar.

How does your code differ from the code in my first post? Mine only gets saved data.

For instance, if I add a few "records" to the worksheet, they are not reflected in my query until I save down the workbook.

Thanks,
Jamie