Problem keeping data synchronised with database query.

[RB]

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.

 

[Billy Liddel]

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