Export from Access to Excel

[tamxwell]

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.

 

[Sean Connolly]

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]

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

 

[tamxwell]

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]

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.

 

[Sean Connolly]

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.

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.

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]

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.

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.

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

 

[DM Unseen]

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

 

[tamxwell]

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

 

[tamxwell]

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