Problem keeping data synchronised with database query.

Hi all,

I'm using Excel 2003.

I have a spreadsheet containing a database query (inserted using
Data->Import External Data->New Database Query) with 5 columns. The 6th
column is a notes column, in which the user can type little comments to
help them.

When clicking "Refresh Data" on the data, I have a problem if a row is
deleted, because the Notes cell that was on that line is now inline with
the next row. I would prefer it if the notes cell was deleted entirely
(or, more specifically, if the entire row was deleted). Is this possible
in Excel 2003?

I've been through the 3 options in "Data Range Properties" for "If the
number of rows in the data range changes upon refresh", but none of them
will delete entire rows - only the cells in the data set.

I currently have it set to "Insert entire rows for new data, clear
unused cells.", and am using a Dictionary object in VBA to read in the
"Notes" values, then write them back out after the refresh is completed
- is there a better way?!

Cheers,

RB.

BillyLiddel
Fri Mar 14 17:52:01 CDT 2008

Hi
I'm just guessing, what happens if you delete all the data before a refresh?
I would imagine that all the would be downloaded. If that is so you could
clear the sheet before refresh.

Record a macro to refresh the data then before the generated code put the
lines

Dim addr As String
addr = Range("A1").CurrentRegion.Address
Range(addr).ClearContents

Hope this helps
Peter

"RB" wrote:

> Hi all,
>
> I'm using Excel 2003.
>
> I have a spreadsheet containing a database query (inserted using
> Data->Import External Data->New Database Query) with 5 columns. The 6th
> column is a notes column, in which the user can type little comments to
> help them.
>
> When clicking "Refresh Data" on the data, I have a problem if a row is
> deleted, because the Notes cell that was on that line is now inline with
> the next row. I would prefer it if the notes cell was deleted entirely
> (or, more specifically, if the entire row was deleted). Is this possible
> in Excel 2003?
>
> I've been through the 3 options in "Data Range Properties" for "If the
> number of rows in the data range changes upon refresh", but none of them
> will delete entire rows - only the cells in the data set.
>
> I currently have it set to "Insert entire rows for new data, clear
> unused cells.", and am using a Dictionary object in VBA to read in the
> "Notes" values, then write them back out after the refresh is completed
> - is there a better way?!
>
> Cheers,
>
> RB.
>