how to get on an MS excel worksheet data from an access database based on data on a cell in excel

hi

how can i import on a excel worksheet data from an access database, not simple information like importing a table, i need more Complex info to import info left join two table with  a Criterion to match a field in excel.

somethis like this:
SELECT Customers.*,CusHistory,CusRating
FROM Customers LEFT JOIN CusHistory on  Customers .CusID = CusHistory.CustomerID WHERE Customers.CusName)=" & excel (a1) & "
 
and another point that is important to mention that this access database is locked by a password.
 
and another point that is important to mention that this access database is locked by a password for example "TEST"

thanks a lot.
bill201Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If the spreadsheet is table-like, you can link to it and use it in queries as you would a table.  The only issue would be that a query that includes the linked spreadsheet would not be updateable.

Or, are you saying that you need to pick data from specific cells in a report-like spreadsheet?  In that case, you need to use VBA and OLE automation which is much more difficult.
0
bill201Author Commented:
what you mean table-like or report-like?
0
PatHartmanCommented:
Does it look like a table?  Are all the rows the same format.  Does each column have consistent data types in each row?

This is what a table looks like.  Except that it may or may not have a column header row.
col1, col2, col3
abc, def, 123
syz, rrr, 456

A report-like spreadsheet has data spread everywhere with something you need in A22 and something else in z41 and something else in HH287
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bill201Author Commented:
thanks for your explanation, yes it's looking like a table alll the rows has the same format.
0
PatHartmanCommented:
So did you try linking to the spreadsheet and working with it as if it were a table?
0
bill201Author Commented:
i don't know how to do it, do you win with a pivot table? i prefer to connect the spreadsheet with dao connection and get the data
0
PatHartmanCommented:
Why do you want to write code to do something you can do with a query?
0
bill201Author Commented:
I Prefer to see the data on the excel  worksheet when im working on this spreadsheeed
0
PatHartmanCommented:
You said you want to import the data into Access so I don't understand the statement.
0
bill201Author Commented:
Sorry this is not what I Mean. I want to  Get in excel data from access
0
bill201Author Commented:
Probably my English is not good enough, and I don't explaining myself right, but like they say that A picture is worth a thousand words so i'm uploading an example to explain exactly what i mean,there is an example of a ms excel and ms access file Book1.xlsDatabase3.mdb i want that when i write on the  excel file  a customer name it's  will look on the database (like query1 on the database) if there is a   customer with this name,  and if so it's will export the  rating number to excel , is this possible ?

i have  a lot situations that a code for this will help me and make things go easier.
0
PatHartmanCommented:
Sorry, I work with Access so I can't help you with this.  Even though you are using an "Access" database, this is really an Excel question.   Someone who works with Excel can explain how to do what you need.
0
bill201Author Commented:
is there someone with Ms access skills that can help me

thanks
0
JimFiveCommented:
1. Create a query in your access database with a parameter.
2. In Excel, go to the Data menu and select From Access.
3. Choose your database and select the query you made in step 1.
4. Right click on the data table and go down to Table -> Parameters.
5. Select the Excel location for the data that you want to use for each parameter.
0
Helen FeddemaCommented:
Make a query in Access to gather the data you want (a select query, or if an action query is needed,create a make-table query to output a table for the export).  Then you can use the CopyFromRecordset method in either Access or Excel VBA to place the data into a specified range of the workbook.

Here is some that does this from Excel:

   Public Sub GetDataFromAccess()
      
      Dim strDatabaseNameAndPath As String
      Dim dbe As DAO.DBEngine
      Dim dbs As DAO.Database
      Dim strRange As String
      Dim rst As DAO.Recordset
      Dim sht As Excel.Worksheet
      Dim rng As Excel.Range
      Dim strRecordSource As String
      
      strDatabaseNameAndPath = "C:\Users\Helen Feddema\Documents\ExpertsExchange\ExpertsExchange Sample Code.mdb"
      strRecordSource = "qryContacts"
      
      Set dbe = DAO.DBEngine
      
      'Connect to database with password
      'Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
         Options:=False, _
         ReadOnly:=True, _
         Connect:=";pwd=xyxyxyx")
      
      'Connect to database without password
      Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
         Options:=False, _
         ReadOnly:=True)
      Set rst = dbs.OpenRecordset(strRecordSource, dbOpenDynaset)
      strRange = "A3"
      Set sht = Application.Sheets(1)
      Set rng = sht.Range(strRange)
      rng.CopyFromRecordset rst
      rst.Close

   End Sub

Open in new window

0
bill201Author Commented:
thanks for you for the answers but Unfortunately the two solution don't work for me...

JimFive:  i have not succeeded to make the "A" column (the column that contains the customer names) to be the parameter for the query. i just succeeded to type manually type a name of a customer for a parameter and then i get his rate.

Helen_Feddema: i don't can compile this code excel 2013 don't recognize DAO.DBEngine.
0
JimFiveCommented:
Bill,
OK, I have just gone through the exercise and it's not quite as easy as I remembered it.

You need to use MS Query to do it:
In Excel, go to the data tab and click on "From Other Sources" and Select "Microsoft Query"
In the window select "MS Access Database"

The wizard is annoying so you may want to just select a column so you can get to the last step where you can "View data or edit query in Microsoft Query"

Now you are in a query designer, similar, but not the same as the Access Query Designer.
Make the query you need here.
view the criteria by clicking on the Filter with glasses.
Set your criteria value to [ParamName] A window will pop up asking for ParamName, enter something valid.
When your Query is how you want it click on File | Return Data to Microsoft Excel
Place your result where you want it to be.
Now, Right click on the Result Table and select Table -> Parameters from the context menu
You can select "Get the value from the following Cell" to select your source.


Now, after all of that:  Your first post says you want to use a cell as the parameter which the above does, the last post says you want to use a Column as the parameter which you can't do this way.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bill201Author Commented:
thanks a lot jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.