Working with data exported from Access

A

Amanda Guenthner

I need to implement a system where I export Access queries into Excel. I am
able to successfully export the data from Access, but I am not sure how I go
about refreshing this data. I added the external data tool bar, and after
some research I am reaching the conclusion that this only works for data that
has been imported from another spreadsheet.

Is there any way that a refresh can work, or do I have to continue the
painful copy/paste process from Access to Excel?

Thanks in advance,

Amanda
 
K

Ken Wright

Instead of Exporting from Access, why not import from Excel using Data / Import
External Data / New Database Query, specifying the Access database as the source
 
A

Amanda Guenthner

You are brilliant - this works so much better!
Now that I have tried this I have another question on the matter. The data
I am importing from Access does not fit into my spreadsheet in the exact same
way (i.e. I have 15 fields in Access but 25 columns in excel b/c some of
these columns are calculated based on the values in the other 15 - make
sense?). Is there a way to import this data so that I can make the fields
skip a column.
For instance if in Access the first three fields are Rev and Prob and Net
Income, and in Excel the first 3 are Rev, Prob, and Adjusted Rev (Rev*Prob).
Therefore, I do not want the Net Income field to override my Adjusted Rev in
Excel.
Hopefully that makes sense. Thanks!
 
K

Ken Wright

Hmmm, you'll probably get a better answer from someone that knows Access, but
personally I'd probably end up either dumping the data into a feeder sheet and
then linking in from there to my report, or if not too many of them maybe you
can do more than one import. Could be a pain to refresh them all though unless
done by code. You could also dump them into a sheet as is and then run a piece
of code to copy the relevant data over to the necessary columns.

Hopefully you get a better answer from someone though.
 
K

Ken Wright

Just a thought but can you not repro the calcs in Access as extra fields and
then just pull the lot in one go? I don't really know Access so not sure.
 
A

Amanda Guenthner

Thank you Ken - that is actually great advice to have a feed sheet (also did
repro queries in Access for extra fields). This turns out to be a perfect
solution to me dilemma, so thank you!
 
Top