I built an Access database which export the daliy (new) figures to Excel to
the desktop. One of the queries is for pulling all the information for a
Credit Manager (CM). I then need to link this data to a summary Workbook. So
each day I want to go to my database, pick the CM's data, export and replace
the old info with the new, and keep the links I have created when I exported
the first one. When I exported the first, I linked it to a number of
spreadsheets. Any thoughts? I get an error telling me that It cannot expand
the named range. If I rename it then I would have to reset all my links.

Now let me get this straight. You want to replace the information daily with
new information <<export and replace the old info with the new BUT you want
to keep the links to the old data that you replaced <keep the links I have
created when I exported the first one???
--
Gary Brown


"tamxwell" wrote:

I built an Access database which export the daliy (new) figures to Excel to
the desktop. One of the queries is for pulling all the information for a
Credit Manager (CM). I then need to link this data to a summary Workbook. So
each day I want to go to my database, pick the CM's data, export and replace
the old info with the new, and keep the links I have created when I exported
the first one. When I exported the first, I linked it to a number of
spreadsheets. Any thoughts? I get an error telling me that It cannot expand
the named range. If I rename it then I would have to reset all my links.

Gary,
Thanks for responding. I knew that I would have to explain this in more
detail, but when I wrote it out it seemed to make sense. There are 36 Credit
Managers that collect from their clients, and in the database the fields are
Current, 1-30, 30-60, 60-90, 90-180, and 360 past due. My database has some
very comlex queries that totals the figures for all of their companies, then
the CMs can export the data to Excel. This allows each of them to pull just
the info they need and total it.

My Boss, their boss has a spreadsheet called €œSummary€� it has the main
summary sheet and all the 36 CM in separate worksheets. I have all 36
worksheets linked to the main Summary page, When the numbers are plugged in
to each of the individual CM worksheets it updates the Summary page. Right
now he has to plug in these numbers manually. When I finished the database
and exported CM01 (for example) data to the desktop, I linked this
spreadsheet to CM01 worksheet, which in turn updated the main Summary page.
The problem is that the amount of data changes daily. The first one I
exported for example had 1200 rows of data (already totaled) these are the
links I setup to the Summary spreadsheet. The next day the amount of data
(row) was only 1000. So its the link I want to keep, not the data. The
amount can range from day to day, anywhere from 1000 rows (customers) to 2000
as we collect from them. I do SQL programming, and I have work with Excel on
projects that it was easier to do some of the work in Excel, being that the
database was built solely for the CMs. This was just an idea he came up with
after the DB was finished, so had I been told this I could of included it,
but now it would be like starting over. I just thought someone might have an
idea


"Gary L Brown" wrote:

Now let me get this straight. You want to replace the information daily with
new information <<export and replace the old info with the new BUT you want
to keep the links to the old data that you replaced <keep the links I have
created when I exported the first one???
--
Gary Brown


"tamxwell" wrote:

I built an Access database which export the daliy (new) figures to Excel to
the desktop. One of the queries is for pulling all the information for a
Credit Manager (CM). I then need to link this data to a summary Workbook. So
each day I want to go to my database, pick the CM's data, export and replace
the old info with the new, and keep the links I have created when I exported
the first one. When I exported the first, I linked it to a number of
spreadsheets. Any thoughts? I get an error telling me that It cannot expand
the named range. If I rename it then I would have to reset all my links.

Hi Tamxwell.
Still not 100% sure of what you're looking for but here are a couple of
ideas depending on exactly what you're doing.

1) If you are looking for a way to give your boss a summary worksheet of
the Credit Managers and your boss doesn't need to make changes to the
worksheet, why not create a crosstab by Credit Manager and
'DoCmd.TransferSpreadsheet TransferType:=acExport' that as the Summary
worksheet?

2) If you are looking for a way to give your boss a summary worksheet of
the Credit Managers and your boss does need to make changes to the worksheet,
why not use the Sum function in the Summary worksheet?
For example: Assuming the headings in the Summary are Cell A1 'Credit
Manager Name', Cell B1 '0-30 days', Cell C1 '31-60 days', etc.
Cell A2 = "Credit Manager 1"
Cell B2 =SUM(CM01!B:B)
Cell C2 =SUM(CM01!C:C)

HTH,
--
Gary Brown

If this post was helpful, please click the ''''''''Yes'''''''' button next
to ''''''''''''''''Was this Post Helpfull to you?".


"tamxwell" wrote:

Gary,
Thanks for responding. I knew that I would have to explain this in more
detail, but when I wrote it out it seemed to make sense. There are 36 Credit
Managers that collect from their clients, and in the database the fields are
Current, 1-30, 30-60, 60-90, 90-180, and 360 past due. My database has some
very comlex queries that totals the figures for all of their companies, then
the CMs can export the data to Excel. This allows each of them to pull just
the info they need and total it.

My Boss, their boss has a spreadsheet called €œSummary€� it has the main
summary sheet and all the 36 CM in separate worksheets. I have all 36
worksheets linked to the main Summary page, When the numbers are plugged in
to each of the individual CM worksheets it updates the Summary page. Right
now he has to plug in these numbers manually. When I finished the database
and exported CM01 (for example) data to the desktop, I linked this
spreadsheet to CM01 worksheet, which in turn updated the main Summary page.
The problem is that the amount of data changes daily. The first one I
exported for example had 1200 rows of data (already totaled) these are the
links I setup to the Summary spreadsheet. The next day the amount of data
(row) was only 1000. So its the link I want to keep, not the data. The
amount can range from day to day, anywhere from 1000 rows (customers) to 2000
as we collect from them. I do SQL programming, and I have work with Excel on
projects that it was easier to do some of the work in Excel, being that the
database was built solely for the CMs. This was just an idea he came up with
after the DB was finished, so had I been told this I could of included it,
but now it would be like starting over. I just thought someone might have an
idea


"Gary L Brown" wrote:

Now let me get this straight. You want to replace the information daily with
new information <<export and replace the old info with the new BUT you want
to keep the links to the old data that you replaced <keep the links I have
created when I exported the first one???
--
Gary Brown


"tamxwell" wrote:

I built an Access database which export the daliy (new) figures to Excel to
the desktop. One of the queries is for pulling all the information for a
Credit Manager (CM). I then need to link this data to a summary Workbook. So
each day I want to go to my database, pick the CM's data, export and replace
the old info with the new, and keep the links I have created when I exported
the first one. When I exported the first, I linked it to a number of
spreadsheets. Any thoughts? I get an error telling me that It cannot expand
the named range. If I rename it then I would have to reset all my links.