Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export from Access to Excel
I built an Access database which exports 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 Spreadsheet. 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export from Access to Excel
Hi "tamxwell",
If I've understood correctly, it sounds like you are trying to 'push' the data from Access to Excel. Here's a suggestion ... why don't you 'pull' the data into a QueryTable on your Excel worksheet from Access? In that way, you can 'Refresh' your Excel spreadsheet with new/updated data from the Access dB as and when desired. The range on your Excel worksheet will be updated (and resized as or if necessary to accomodate new data), but importantly, it will retain the exact same range name on the exact same worksheet. Thus your links to other worksheets in the same workbook will not be broken. The Excel "Import Data Wizard" should step you through it and make it nice and simple for you. (The Excel help for QueryTables and Import Data Wizard should provide more information if you're still having problems). Trust this helps. Cheers, Sean. "tamxwell" wrote: I built an Access database which exports 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 Spreadsheet. 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export from Access to Excel
Hi Sean,
It's actually Todd Maxwell, I fat fingered my own name, image that. I do believe you are correct. Last night it click, hey just import it. I did a trial run but to no avail. The answer is there I just need to "experiment". The database UNION ALL queries I built are complex, it would have been nice if my boss would have told me he need this for his own usage. I could have built a query based on his requirements, but the DB is finished and online. I might have more questions for you I hope you don't mind. Thanks again, tamxwell "Sean Connolly" wrote: Hi "tamxwell", If I've understood correctly, it sounds like you are trying to 'push' the data from Access to Excel. Here's a suggestion ... why don't you 'pull' the data into a QueryTable on your Excel worksheet from Access? In that way, you can 'Refresh' your Excel spreadsheet with new/updated data from the Access dB as and when desired. The range on your Excel worksheet will be updated (and resized as or if necessary to accomodate new data), but importantly, it will retain the exact same range name on the exact same worksheet. Thus your links to other worksheets in the same workbook will not be broken. The Excel "Import Data Wizard" should step you through it and make it nice and simple for you. (The Excel help for QueryTables and Import Data Wizard should provide more information if you're still having problems). Trust this helps. Cheers, Sean. "tamxwell" wrote: I built an Access database which exports 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 Spreadsheet. 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export from Access to Excel
Sean,
I was not all clear on that fact that there is 36 CM (credit managers). So by pulling, it will let me have all the data for all the CM's. I need each one seperatly that would be linked to each CM worksheet, then linked to the Summary page. "Sean Connolly" wrote: Hi "tamxwell", If I've understood correctly, it sounds like you are trying to 'push' the data from Access to Excel. Here's a suggestion ... why don't you 'pull' the data into a QueryTable on your Excel worksheet from Access? In that way, you can 'Refresh' your Excel spreadsheet with new/updated data from the Access dB as and when desired. The range on your Excel worksheet will be updated (and resized as or if necessary to accomodate new data), but importantly, it will retain the exact same range name on the exact same worksheet. Thus your links to other worksheets in the same workbook will not be broken. The Excel "Import Data Wizard" should step you through it and make it nice and simple for you. (The Excel help for QueryTables and Import Data Wizard should provide more information if you're still having problems). Trust this helps. Cheers, Sean. "tamxwell" wrote: I built an Access database which exports 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 Spreadsheet. 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import to Excel from Access
Hi Todd,
(Don't we just love those last minute boss requests that have to be done 'yesterday'! :-)) Alright, it probably is a little more complex than first thought, but no major obstacle. As usual, there's a number of ways to achieve this depending on your personal skill level/preferences. (Sorry, if this gets a bit long, bear with me ...) One caveat to start with; Its a bit difficult without knowing the schema, structure, tables etc. of your Access dB, but I'll try to be as generic as I can. (Normally, I would design the dB with the end-user reports required in mind at dB design time - a lofty goal and not always possible, I know!). Tip 1: Whilst it is probably more efficient, your queries do not have to reside in the Access dB. You can build a separate/specific query for each QueryTable should you so wish. (The relevant property of the Excel QueryTable object is '.CmdText'). If you have installed MSQuery as a part of Excel, the 'Import Data Wizard' can call MSQuery to build the SQL Query statement required for each QueryTable (and automatically set the .CmdText property). MSQuery has a grid-like 'query builder' that should be very familiar to Access users. Of course, you will need to create a query connection to your Access dB (once only). Option 1: Create a single QueryTable on a single worksheet of your workbook to populate and refresh all detailed data for all credit managers. Use a 'Group By' clause in your SQL query statement to group (and sort) by credit manager. Then after the query is refreshed on your worksheet, you can use Excel's 'Group and Outline' and/or 'Subtotals' to summarize and total the refreshed data (automatically). You can then show/print totals only or detail and totals as you or your boss wish. Option 2: If you really prefer a separate worksheet for each CM, then you will have to create a separate QueryTable (with a separate, but similar query statement) for each. (The key here is the use of criteria in the 'Where' clause of each of the SQL statements built. For example, "Where [Access dB credit manager field name] = 'credit manager name' (or ID)". The downside here is that its somewhat 'hardcoded' and fixed. As soon as a new credit manager is employed or terminated, your model 'breaks' and requires your effort to fix and maintain. Tip 2: Set the option to 'Refresh on Open' to true for all QueryTables created. Then when you open this workbook, Excel will ask you if you want to refresh the QueryTable data (yes automatically, in the background too!). Tip 3: CountIf and/or SumIf Excel functions may also be useful on your summary worksheet. (Likewise DSum and/or DCount). The other option is that this can all be automated programatically by using VBA - if you're confident/experienced with VBA and a little SQL. (This can also cover and automate the problem mentioned above of new starters/leavers). Let me know how you get on. HTH and Regards, Sean. "tamxwell" wrote: Hi Sean, It's actually Todd Maxwell, I fat fingered my own name, image that. I do believe you are correct. Last night it click, hey just import it. I did a trial run but to no avail. The answer is there I just need to "experiment". The database UNION ALL queries I built are complex, it would have been nice if my boss would have told me he need this for his own usage. I could have built a query based on his requirements, but the DB is finished and online. I might have more questions for you I hope you don't mind. Thanks again, tamxwell "Sean Connolly" wrote: Hi "tamxwell", If I've understood correctly, it sounds like you are trying to 'push' the data from Access to Excel. Here's a suggestion ... why don't you 'pull' the data into a QueryTable on your Excel worksheet from Access? In that way, you can 'Refresh' your Excel spreadsheet with new/updated data from the Access dB as and when desired. The range on your Excel worksheet will be updated (and resized as or if necessary to accomodate new data), but importantly, it will retain the exact same range name on the exact same worksheet. Thus your links to other worksheets in the same workbook will not be broken. The Excel "Import Data Wizard" should step you through it and make it nice and simple for you. (The Excel help for QueryTables and Import Data Wizard should provide more information if you're still having problems). Trust this helps. Cheers, Sean. "tamxwell" wrote: I built an Access database which exports 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 Spreadsheet. 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import to Excel from Access
Oh, and one last caveat ...
Any single Excel worksheet can only display 65,536 rows of data, so be careful if any of your queries or QueryTables return more than that number of rows/records! Cheers and BFN, Sean. "Sean Connolly" wrote: Hi Todd, (Don't we just love those last minute boss requests that have to be done 'yesterday'! :-)) Alright, it probably is a little more complex than first thought, but no major obstacle. As usual, there's a number of ways to achieve this depending on your personal skill level/preferences. (Sorry, if this gets a bit long, bear with me ...) One caveat to start with; Its a bit difficult without knowing the schema, structure, tables etc. of your Access dB, but I'll try to be as generic as I can. (Normally, I would design the dB with the end-user reports required in mind at dB design time - a lofty goal and not always possible, I know!). Tip 1: Whilst it is probably more efficient, your queries do not have to reside in the Access dB. You can build a separate/specific query for each QueryTable should you so wish. (The relevant property of the Excel QueryTable object is '.CmdText'). If you have installed MSQuery as a part of Excel, the 'Import Data Wizard' can call MSQuery to build the SQL Query statement required for each QueryTable (and automatically set the .CmdText property). MSQuery has a grid-like 'query builder' that should be very familiar to Access users. Of course, you will need to create a query connection to your Access dB (once only). Option 1: Create a single QueryTable on a single worksheet of your workbook to populate and refresh all detailed data for all credit managers. Use a 'Group By' clause in your SQL query statement to group (and sort) by credit manager. Then after the query is refreshed on your worksheet, you can use Excel's 'Group and Outline' and/or 'Subtotals' to summarize and total the refreshed data (automatically). You can then show/print totals only or detail and totals as you or your boss wish. Option 2: If you really prefer a separate worksheet for each CM, then you will have to create a separate QueryTable (with a separate, but similar query statement) for each. (The key here is the use of criteria in the 'Where' clause of each of the SQL statements built. For example, "Where [Access dB credit manager field name] = 'credit manager name' (or ID)". The downside here is that its somewhat 'hardcoded' and fixed. As soon as a new credit manager is employed or terminated, your model 'breaks' and requires your effort to fix and maintain. Tip 2: Set the option to 'Refresh on Open' to true for all QueryTables created. Then when you open this workbook, Excel will ask you if you want to refresh the QueryTable data (yes automatically, in the background too!). Tip 3: CountIf and/or SumIf Excel functions may also be useful on your summary worksheet. (Likewise DSum and/or DCount). The other option is that this can all be automated programatically by using VBA - if you're confident/experienced with VBA and a little SQL. (This can also cover and automate the problem mentioned above of new starters/leavers). Let me know how you get on. HTH and Regards, Sean. "tamxwell" wrote: Hi Sean, It's actually Todd Maxwell, I fat fingered my own name, image that. I do believe you are correct. Last night it click, hey just import it. I did a trial run but to no avail. The answer is there I just need to "experiment". The database UNION ALL queries I built are complex, it would have been nice if my boss would have told me he need this for his own usage. I could have built a query based on his requirements, but the DB is finished and online. I might have more questions for you I hope you don't mind. Thanks again, tamxwell "Sean Connolly" wrote: Hi "tamxwell", If I've understood correctly, it sounds like you are trying to 'push' the data from Access to Excel. Here's a suggestion ... why don't you 'pull' the data into a QueryTable on your Excel worksheet from Access? In that way, you can 'Refresh' your Excel spreadsheet with new/updated data from the Access dB as and when desired. The range on your Excel worksheet will be updated (and resized as or if necessary to accomodate new data), but importantly, it will retain the exact same range name on the exact same worksheet. Thus your links to other worksheets in the same workbook will not be broken. The Excel "Import Data Wizard" should step you through it and make it nice and simple for you. (The Excel help for QueryTables and Import Data Wizard should provide more information if you're still having problems). Trust this helps. Cheers, Sean. "tamxwell" wrote: I built an Access database which exports 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 Spreadsheet. 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import to Excel from Access
Oh, and one last caveat ...
Any single Excel worksheet can only display 65,536 rows of data, so be careful if any of your queries or QueryTables return more than that number of rows/records! Cheers and BFN, Sean. "Sean Connolly" wrote: Hi Todd, (Don't we just love those last minute boss requests that have to be done 'yesterday'! :-)) Alright, it probably is a little more complex than first thought, but no major obstacle. As usual, there's a number of ways to achieve this depending on your personal skill level/preferences. (Sorry, if this gets a bit long, bear with me ...) One caveat to start with; Its a bit difficult without knowing the schema, structure, tables etc. of your Access dB, but I'll try to be as generic as I can. (Normally, I would design the dB with the end-user reports required in mind at dB design time - a lofty goal and not always possible, I know!). Tip 1: Whilst it is probably more efficient, your queries do not have to reside in the Access dB. You can build a separate/specific query for each QueryTable should you so wish. (The relevant property of the Excel QueryTable object is '.CmdText'). If you have installed MSQuery as a part of Excel, the 'Import Data Wizard' can call MSQuery to build the SQL Query statement required for each QueryTable (and automatically set the .CmdText property). MSQuery has a grid-like 'query builder' that should be very familiar to Access users. Of course, you will need to create a query connection to your Access dB (once only). Option 1: Create a single QueryTable on a single worksheet of your workbook to populate and refresh all detailed data for all credit managers. Use a 'Group By' clause in your SQL query statement to group (and sort) by credit manager. Then after the query is refreshed on your worksheet, you can use Excel's 'Group and Outline' and/or 'Subtotals' to summarize and total the refreshed data (automatically). You can then show/print totals only or detail and totals as you or your boss wish. Option 2: If you really prefer a separate worksheet for each CM, then you will have to create a separate QueryTable (with a separate, but similar query statement) for each. (The key here is the use of criteria in the 'Where' clause of each of the SQL statements built. For example, "Where [Access dB credit manager field name] = 'credit manager name' (or ID)". The downside here is that its somewhat 'hardcoded' and fixed. As soon as a new credit manager is employed or terminated, your model 'breaks' and requires your effort to fix and maintain. Tip 2: Set the option to 'Refresh on Open' to true for all QueryTables created. Then when you open this workbook, Excel will ask you if you want to refresh the QueryTable data (yes automatically, in the background too!). Tip 3: CountIf and/or SumIf Excel functions may also be useful on your summary worksheet. (Likewise DSum and/or DCount). The other option is that this can all be automated programatically by using VBA - if you're confident/experienced with VBA and a little SQL. (This can also cover and automate the problem mentioned above of new starters/leavers). Let me know how you get on. HTH and Regards, Sean. "tamxwell" wrote: Hi Sean, It's actually Todd Maxwell, I fat fingered my own name, image that. I do believe you are correct. Last night it click, hey just import it. I did a trial run but to no avail. The answer is there I just need to "experiment". The database UNION ALL queries I built are complex, it would have been nice if my boss would have told me he need this for his own usage. I could have built a query based on his requirements, but the DB is finished and online. I might have more questions for you I hope you don't mind. Thanks again, tamxwell "Sean Connolly" wrote: Hi "tamxwell", If I've understood correctly, it sounds like you are trying to 'push' the data from Access to Excel. Here's a suggestion ... why don't you 'pull' the data into a QueryTable on your Excel worksheet from Access? In that way, you can 'Refresh' your Excel spreadsheet with new/updated data from the Access dB as and when desired. The range on your Excel worksheet will be updated (and resized as or if necessary to accomodate new data), but importantly, it will retain the exact same range name on the exact same worksheet. Thus your links to other worksheets in the same workbook will not be broken. The Excel "Import Data Wizard" should step you through it and make it nice and simple for you. (The Excel help for QueryTables and Import Data Wizard should provide more information if you're still having problems). Trust this helps. Cheers, Sean. "tamxwell" wrote: I built an Access database which exports 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 Spreadsheet. 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export from Access to Excel
Todd,
Think more "out of the box". Create just 1 query for all CM. Use a parameter where you can filter on the data for each CM(or even select all CM). Let your *boss* select the CM and then do a refresh (you could write a macro for this). No need to put it all there at once! For the summary you can create one additional summary query, or use a pivottable(works very well) based on the excel data of your query. Some hinst & tips: Create Access queries that do "most of the work" in Access to easy query creation in Excel, but let excel do most of the filtering (on e.g. CM ). Don't use VBA in those queries, else they won't work in Excel. Hope this helps, DM Unseen |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export from Access to Excel
DM,
Pivot table... Because this DB is mainly for the CM's I just did not want to start writing queries for every little thing. I have 4 other DB's to do. I just want to reign them in. It's like buliding a DB on what they think they want/need, to find out they they don't know... Thanks again Todd "DM Unseen" wrote: Todd, Think more "out of the box". Create just 1 query for all CM. Use a parameter where you can filter on the data for each CM(or even select all CM). Let your *boss* select the CM and then do a refresh (you could write a macro for this). No need to put it all there at once! For the summary you can create one additional summary query, or use a pivottable(works very well) based on the excel data of your query. Some hinst & tips: Create Access queries that do "most of the work" in Access to easy query creation in Excel, but let excel do most of the filtering (on e.g. CM ). Don't use VBA in those queries, else they won't work in Excel. Hope this helps, DM Unseen |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export from Access to Excel
DM,
The problem is I need to link the pivot table to each CM"s worksheet individually for all the months and the 4 times a month Boss man runs it. I can create the links, but when I hit refresh it will naturally change all the cells linked. Is there anyway I can set this up so it will only change the data for the last refresh. I'll show an example of the CM's individual worksheet to see if I am making any sense. Jan Current 1-30 30-60 60-90 90-180 180-360 360+ 1/1-1/2 1/3-1/9 1/10-1/16 1/17-1/23 1/24-1/30 I can link CM01 info to each of the cells in these schemes, but everytime it's refeshed it's going to change anything with the old data link to the new refreshed info. I need to keep the info for 1/1-1/2, Current....360+ there when I refresh for 1/3-1/9 and so forth. I know without seeing it is hard, but if anyone has an idea, PLEASE help. Tmaxwell "DM Unseen" wrote: Todd, Think more "out of the box". Create just 1 query for all CM. Use a parameter where you can filter on the data for each CM(or even select all CM). Let your *boss* select the CM and then do a refresh (you could write a macro for this). No need to put it all there at once! For the summary you can create one additional summary query, or use a pivottable(works very well) based on the excel data of your query. Some hinst & tips: Create Access queries that do "most of the work" in Access to easy query creation in Excel, but let excel do most of the filtering (on e.g. CM ). Don't use VBA in those queries, else they won't work in Excel. Hope this helps, DM Unseen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export to Access from Excel | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
export data from Excel to MS Access (ADO) using VBA | Excel Programming | |||
Export from Access to Excel | Excel Discussion (Misc queries) | |||
Programming Excel to export to Access | Excel Programming |