Data Range Will Not Expand
 

Hi,

Whenever I update my query to bring in new data, the defined range will not
expand to include the new data. How do I correct this si that when the new
data is returned, the defined range expands to include the new data.

Thanx

There is a defined name that specifies the range. You must either manually
update the range or create a dynamic name using formulas in the Defined
Names. The Name you currently have is probably "Database" and the reference
will look like
=Sheet1!$A$1:$E$500

No matter how you update the query this would only look down to row 500. To
make it dynamic I use the following trick:
Select the data sheet and create a name to find the last used row
In the Defined Name box type QTY
In the Reerence box type:
=MAX(ROW(IF(ISBLANK($A$2:$A$65536),0,$A$2:$A$65536 )),1)
Add the name
Now click on the name "Database"
Change the reference to look like this:
=OFFSET($A$1,0,0,QTY,5)

QTY counts the used rows and 5 says I have 5 columns. Modify as needed to
fit your situation.

"Dmorri254" wrote:

Hi,

Whenever I update my query to bring in new data, the defined range will not
expand to include the new data. How do I correct this si that when the new
data is returned, the defined range expands to include the new data.

Thanx

Thanx Frank...will try this....do you know of a way to use a excel to update
a record in a database? I have a posting on this that no one seems to know
the answer.

Thanx again

"Frank T" wrote:

There is a defined name that specifies the range. You must either manually
update the range or create a dynamic name using formulas in the Defined
Names. The Name you currently have is probably "Database" and the reference
will look like
=Sheet1!$A$1:$E$500

No matter how you update the query this would only look down to row 500. To
make it dynamic I use the following trick:
Select the data sheet and create a name to find the last used row
In the Defined Name box type QTY
In the Reerence box type:
=MAX(ROW(IF(ISBLANK($A$2:$A$65536),0,$A$2:$A$65536 )),1)
Add the name
Now click on the name "Database"
Change the reference to look like this:
=OFFSET($A$1,0,0,QTY,5)

QTY counts the used rows and 5 says I have 5 columns. Modify as needed to
fit your situation.

"Dmorri254" wrote:

Hi,

Whenever I update my query to bring in new data, the defined range will not
expand to include the new data. How do I correct this si that when the new
data is returned, the defined range expands to include the new data.

Thanx

"Dmorri254" wrote in message
...
Hi,

Whenever I update my query to bring in new data, the defined range will
not
expand to include the new data. How do I correct this si that when the new
data is returned, the defined range expands to include the new data.

Thanx

An alterantive approach is to use the combination of VBA and ADO to get data
from your datbase into Excel. With this approach, you don't have to worry
about range problems. Here's a link
http://www.exceltip.com/st/Import_data_from_Access_to_Excel_(ADO)_using_VBA_i n_Microsoft_Excel/427.html

/Fredrik

"Dmorri254" wrote in message
...
Thanx Frank...will try this....do you know of a way to use a excel to
update
a record in a database? I have a posting on this that no one seems to know
the answer.

Thanx again


I have answered that question.

/fredrik