• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1015
  • Last Modified:

ACCESS to EXCEL - automate query to fill spreadsheet

We have an ACCESS db but clients would find it easier to prepare the graphical reports (charts) in Excel.  Currently we assist the user by running a make-table query to select ACCESS data and then exporting this table into Excel.  Looking for a simple process to automate the process of running the query, exporting the data, and filling the Excel sheet with Access data for users unfamiliar with data structures, queries, etc.  
Is it possible to automate a process as follows: when the user opens a pre-defined  Excel sheet, it runs a query in ACCESS that brings back data to fill a new page in the Excel workbook.
Thanks for your assistance.  
0
lineonecorp
Asked:
lineonecorp
  • 7
  • 3
  • 3
5 Solutions
 
TextReportCommented:
Yes in Excel you can get the data from Access and produce the chart. Once you have done this when you open the sheet you will be prompted to update the linked data.

What version of Access and Excel are you using?

Cheers, Andrew
0
 
lineonecorpAuthor Commented:
Versions are 2002 for both products - many thanks!
0
 
TextReportCommented:
Excel 2002 can read the data directly from the query rather than having to use the Make Table and then produce a graph from there. You may need to adjust the query though to ensure it is grabbing the correct data from the query.
Cheers, Andrew
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
lineonecorpAuthor Commented:
Hi again - can I run the pre-defined Query simply by having the client open the Excel sheet or by placing a (big) button on the sheet?
Thank you!
0
 
TextReportCommented:
Either but you need to create the data link first. Cheers, Andrew
0
 
lineonecorpAuthor Commented:
Hi again Andrew: I'm new at this and not sure about 'creating the data link'  - any suggestions?  I assume once the link is done I'd run a query. My intent is to make this as easy/automatic as possible - user skill level is low. Thank You.
0
 
Jerry PaladinoCommented:
lineonecorp,

I have attached a PDF with a few screen shots on establishing a query in Excel from an MS-Access table or query.  Perhaps it will be helpful in walking you through the process of bringing your Access data into an Excel sheet.  The VBA code below will run each time the file is opened.  You may find after a while that is not exaclty what you want.  It may be better to attach the macro to a button that allows you to update the data when you want to and not everytime the file opens.  Excel will run an "Auto_Open" macro each time the file is opened.  The code assumes your query is embedded in "Sheet1".  Change that to whatever your sheet name is.

Have Fun,
Jerry
Sub Auto_Open()
Application.ScreenUpdating = False
  Sheets("Sheet1").Select
  Range("A1").Select
  Selection.QueryTable.Refresh BackgroundQuery:=False
Application.ScreenUpdating = True
End Sub

Open in new window

Access-Query-from-Excel-2000-200.pdf
0
 
lineonecorpAuthor Commented:
Thanks to both of you - I'll try this tomorrow.
0
 
lineonecorpAuthor Commented:
Hi: - trying to set this up but getting stopped on DB security permissions.  The DB is secured and not owned by Admin.  However, I do have the DB owner ID and password but I still get the permissions denied message.  Any suggestions?  Thank you!
0
 
Jerry PaladinoCommented:
Lineonecorp,

My suggestion is to set up a sample Access db file with a small table and a query and verify you can walk through the process of getting connected, refreshing the query, etc... just so you can see the process work from beginning to end.  Then tackle the security issues.  

On the db security, I am sure you have done this but I'll mention it anyway...  Do you have Caps Lock on by chance?  Are you sure of the "case" of the password you were given.  Does it start with a capital or lower case, etc...  I know... - basic stuff but always a good place to start.
   
0
 
lineonecorpAuthor Commented:
Thank you for the suggestions.  Progress!  I've now connected to the DB, extracted a data file and brought it into Excel. Now to try and automate this process for the user.  I'll keep this thread open for while in case I hit any road-blocks.
0
 
Jerry PaladinoCommented:
I don't understand....  lineonecorp said he did not know how to establish a link between Access and Excel to bring data from Access into Excel and we got him there.  Also provided VBA to automate the process.

lineonecorp comment - 23718181  - I'm new at this and not sure about 'creating the data link'  - any suggestions?

lineonecorp comment - 23730803 - Progress!  I've now connected to the DB, extracted a data file and brought it into Excel. Now to try and automate this process for the user.  I'll keep this thread open for while in case I hit any road-blocks.

I am not so concerned about the points as wanting to know if I did something wrong?  Did he request to close this post without accepting a solution or did it just time out?  I just started answering post last week and admit I am new at this.  Just curious?
0
 
lineonecorpAuthor Commented:
My apologies for any confusion.  I thought that I previously awarded the points but instead appear to have closed the question.  My error, awarding the points now.  I certainly appreciate the assistance.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now