Get data from Access db and put in a formatted Excel spreadsheet

[Simon Jester]

Hey everyone. I'm hoping someone else has had this situation and can
maybe steer me in the right direction.

Background: We use Office 2003 which means Access 2003 and Excel 2003
- I can't change that. We have several Excel workbooks that use
numeric data from an Access db. I have been using MSQuery to pull the
Access data across and populate the spreadsheets. This works,
although it seems like a lot of trouble, but some of the spreadsheets
need to pull the data into specific areas of the speadsheet. Again, I
can use MSQuery to accomplish this unless more data is brought across
than the particular area of the workbook is set up to hold, then cells
and calculations get overwritten. I have worked with Offset and named
ranges in the past to resolve the overwrite problem, but after a while
it seems like it's more trouble than the report is worth (well, to me
anyway).

My question is, has anyone had to deal with this before, and is there
a better way of getting the data automatically from the access db and
have it populate the specific areas of the spreadsheets while still
maintaining the spreadsheet's forumlas and formats? Or, do I just
need to cowboy up and live with MSQuery?
Any suggestions would be most appreciated.
Thanks much!
S

 

[Jellifish]

Can you achieve what the Excel spreadsheets do using Access reports?

 

[Simon Jester]

Can you achieve what the Excel spreadsheets do using Access reports?




Sadly, I cannot. I actually did do this in Access and it was not what they wanted. I'm told it has to be done in Excel.

 

[Paul Shapiro]

You can write VBA code to open a recordset and then put the data wherever
you want in the spreadsheet. The VBA code can be running either in Access or
Excel.

 

[Ed Ferrero]

Hi Simon,

My question is, has anyone had to deal with this before, and is there
a better way of getting the data automatically from the access db and
have it populate the specific areas of the spreadsheets while still
maintaining the spreadsheet's forumlas and formats? Or, do I just
need to cowboy up and live with MSQuery?

Normally I use one Excel sheet to hold the data query (same as you have done
with MSQuery).
Then use another sheet to hold the report.

To keep formulas simple, use a dynamic range to reference your data,
and VLOOKUP to show the right data in your report sheet.

Some help on how to do this is here,
http://www.edferrero.com/ExcelTutorials/ExcelDatabaseTutorialPart1/tabid/90/Default.aspx

Also note that you can right-click anywhere in the data query range and
select Options to see more settings for the data query. 'Refresh data on
file open' is useful, as is 'Fill down formulas adjacient to data range'.
And don't forget to rename the data range to something useful.

Ed Ferrero
www.edferrero.com

 

[a a r o n . k e m p f]

learn an ETL tool.

Spreadsheets are lame, dude-- use them only if you like building the
same report by hand every week!

 

[SmartbizAustralia]

Use another worksheet to hold your formula.

And ed did give you a good tip in vlookup, but be aware that can be
error prone as well.
That can be based on columns, so extra rows shouldn't effect this.

What are you really trying to do with the data?

You might just need a pivot table.

If you are trying to split the data or dedupe it or other fancy
things, formula can do alot, but vba code can also do things like
changing the query etc.

Tom Bizannes
http://www.smartbiz.com.au

 

[a a r o n . k e m p f]

yah-- pivotTables are best achieved through SQL Server Analysis
Services

they support drilldown-- when you use SSAS (OLAP)

 

[Ed Ferrero]

Hi Aaron,

yah-- pivotTables are best achieved through SQL Server Analysis
Services

they support drilldown-- when you use SSAS (OLAP)

I don't think this is helpful to the OP. Pivot Tables support drill-down
quite effectively if based on an Access db. Sure, a Pivot Table based on a
data cube will provide a couple of additional features, but nothing that is
likely to help the OP. BTW, it is possible to create a data cube using
nothing more than Excel and MS Query - try it sometime, you may learn
something.


Hi Simon,

Background: We use Office 2003 which means Access 2003 and Excel 2003
- I can't change that. We have several Excel workbooks that use
numeric data from an Access db. I have been using MSQuery to pull the
Access data across and populate the spreadsheets.

I think you are on the right track using data tables populated by MS Query.

A couple of tios that may help you;

- Keep each data table in its own sheet, with nothing else in the sheet
- It is then possible to write VLOOKUP formulas like this
=VLOOKUP(A7,Sheet1!A:C,3,FALSE)
This looks up the value in cell A7 in the table array Sheet1!A:C
That is, in the whole column range A:C of Sheet1 - no fiddling about with
dynamic ranges.

Would also fix your problems with overwritten data.
See
http://www.edferrero.com/ExcelTutorials/ExcelDatabaseTutorialPart1/tabid/90/Default.aspx
for a quick intro on how to handle these issues.

Ed Ferrero
www.edferrero.com

 

[a a r o n . k e m p f]

no-- they really don't support true drilldown.

drilling from year to quarter to month-- it just doesn't work unless
you use 'Analysis Services'

 

[Ed Ferrero]

Hi Aaron,

no-- they really don't support true drilldown.

drilling from year to quarter to month-- it just doesn't work unless
you use 'Analysis Services'

Or, you could group the time field in the Excel Pivot Table and acheive the
same thing

Ed Ferrero
www.edferrero.com

 

[a a r o n . k e m p f]

it doesn't work the same way-- it doesn't support true hierarchies
unless you use analysis services.

furthermore, it doesn't support _ACTIONS_ or extended properties-- or
real drillthrough (unless you store your data in Excel rofl)

 

[a a r o n . k e m p f]

the way that PivotTables work, directly against a database-- is that
it calculates at each level.

having a true multi-dimensional server as a backend-- is about 100
times faster, more powerful
connecting from DB to Excel (without going through SSAS) is
laughable.. I mean-- it doesn't work, it doesnt' really support
automation

meanwhile, I'm been writing XMLA against cubes, and it's easy to
launch on a scheduled basis (using SQL Agent)

OfflineCubes havent' had a single improvement this decade.
and SSAS is the worlds most popular OLAP tool

check out www.olapreport.com/market.htm and why don't you point out to
me where Offline Cubes show up
or -NO-CUBES- for that matter (where it takes 30 seconds every time
you move a field)