R
richard h
Hi folks,
I have an Access 2000 database and need to use output from queries to
populate worksheets in Excel (also 2000), to allow me to then carry out
calculations on the data. I need to be able to overwrite the data, so
built the spreadsheet formatting around the query output structure, and
put all calculations in place, before creating links to the queries - I
did this by selecting the query in Access, copying using Copy, then
switching into Excel and creating links via Edit>Paste Special>Paste links.
I initially built an empty database, i.e. built the structure, based on
an existing one that currently carries all the data, then copied and
pasted all the records in once everything was set up. Although the
address line in Excel suggests that there is a link between the
applications, and shows the address for the correct Access query, no
data is imported when the Access tables (and therefore queries) were
subsequently populated.
In Excel, I have only the "Update remote references" box checked; that's
the only change I have made to default settings.
I need to protect the spreadsheet template and not save it with the
imported data in, so that it can be used again (and again, and
again...), which is why I built it and put in the links before
populating the data tables - otherwise, Excel threw a wobbly when there
were fewer records than previously, and refused to carry out some of the
worksheet calculations.
My idea was to build the spreadsheets with all their internal links and
calculations (which all work fine with data copied and pasted in via
Copy>Paste), which would be automatically updated as changes were made
to the Access database - this is on a daily basis. But in order to do
this, I need to get these links between the applications working.
Anyone any thoughts? I am stumped, and suspect it is a small tickbox
somewhere that should be ticked and isn't, or isn't ticked and should be.
Richard
I have an Access 2000 database and need to use output from queries to
populate worksheets in Excel (also 2000), to allow me to then carry out
calculations on the data. I need to be able to overwrite the data, so
built the spreadsheet formatting around the query output structure, and
put all calculations in place, before creating links to the queries - I
did this by selecting the query in Access, copying using Copy, then
switching into Excel and creating links via Edit>Paste Special>Paste links.
I initially built an empty database, i.e. built the structure, based on
an existing one that currently carries all the data, then copied and
pasted all the records in once everything was set up. Although the
address line in Excel suggests that there is a link between the
applications, and shows the address for the correct Access query, no
data is imported when the Access tables (and therefore queries) were
subsequently populated.
In Excel, I have only the "Update remote references" box checked; that's
the only change I have made to default settings.
I need to protect the spreadsheet template and not save it with the
imported data in, so that it can be used again (and again, and
again...), which is why I built it and put in the links before
populating the data tables - otherwise, Excel threw a wobbly when there
were fewer records than previously, and refused to carry out some of the
worksheet calculations.
My idea was to build the spreadsheets with all their internal links and
calculations (which all work fine with data copied and pasted in via
Copy>Paste), which would be automatically updated as changes were made
to the Access database - this is on a daily basis. But in order to do
this, I need to get these links between the applications working.
Anyone any thoughts? I am stumped, and suspect it is a small tickbox
somewhere that should be ticked and isn't, or isn't ticked and should be.
Richard