We help IT Professionals succeed at work.

Excel - Pulling data from database and excel file

Medium Priority
461 Views
Last Modified: 2012-05-06
I want to create an excel file that would pull data off a database.  I had created the ODBC for this and am able to pull what I need.  However, there's an excel file that has the job number for the week that I want to pull only those job numbers into this excel file.  I am not sure how I can link the 2 together and use the job number as the search criteria in the where clause.
Comment
Watch Question

Commented:
If the query is a simple SELECT statement, then you can use parameter queries from Excel.

When designing the query in MSQuery32, just add a condition like -

WHERE jobnumber = ?

This creates a parameter which can be configured to take a scalar (one value only) from a cell..

Author

Commented:
I am not sure if I understand.  This is the query that I use for pulling from the database.  Of course this is not right since I can't seem to get it to pull.

SELECT O.JOBNUM, O.OPER, O.DESC, J.PLANNED_HRS, J.ACTUAL_HRS
FROM ORDER O INNER JOIN JOB J ON O.JOBNUM = J.JOBNUM
WHERE O.JOBNUM = @EXCELJOBNUM


This is from the excel spreadsheet with 2 fields that I want to use.  User should be able to put in date range, and it will use the jobnumber to link to the database and only pull that into the excel file.  Example date range 2/9/2009 - 2/13/2009 will only pull those job number that falls within that date range.

COLUMN A             COLUMN B
2/9/2009                ABC123
2/9/2009                EFG123
2/10/2009              HIJK123
2/10/2009              LMN123
2/16/2009              OPQ123
CERTIFIED EXPERT
Top Expert 2015

Commented:
One quick question..how do you want to enter the dates that is linked to a cell or by inputbox...you can do something like this...
SELECT O.JOBNUM, O.OPER, O.DESC, J.PLANNED_HRS, J.ACTUAL_HRS
FROM ORDER O INNER JOIN JOB J ON O.JOBNUM = J.JOBNUM
WHERE ((O.JOBNUM = @EXCELJOBNUM ) and (O.Date >= cdbl(your start date)) and (o.date <=cdbl(your end date)))

Assuming in table its called by name of o.date and you can replace your start and end date with actual dates...
Saurabh...

Author

Commented:
Thanks Saurabh.  I will take a look at that.  The date entry will probably be from an inputbox.  User will enter in date range for the week and those are the jobs we want it to pull from database into Excel file.

For example, user wants to track jobs worked on 2/9/2009 through 2/13/2009.  User enter this date range which will take the jobnum from the Excel and use that in the query to pull the rest of data into another worksheet.

Looking at what you provide, I am still a bit confuse at how I can join the excel file and database into this main worksheet.

Author

Commented:
Is there a way to create like a join between a database and an excel file within another excel sheet?  I'm thinking that's what I need to do.  Something like below.

SELECT O.JOBNUM, O.OPER, O.DESC, J.PLANNED_HRS, J.ACTUAL_HRS
FROM ORDER O INNER JOIN JOB J ON O.JOBNUM = J.JOBNUM
WHERE O.JOBNUM IN (SELECT EXCEL.JOBNUM FROM WORKSHEET1 WHERE EXCEL.DATE BETWEEN @STARTDATE AND @ENDDATE)

I am familar with creating sql script, just haven't work with Excel enough to understand if some of these are doable.
CERTIFIED EXPERT
Top Expert 2015

Commented:
You can use..a command like this...to do what you want...to pick the sheet values
sheets("Your sheet name").Range("a1").value
this would give you the value of that sheet of range("a1")
Saurabh...

Author

Commented:
I think that'll work.  I guess what I'm stumped at right now is I can create separate ODBC for the database and excel.  I can pull data from either one.  I'm just not sure how I would link the 2 in the Microsoft Query window.  I can get data from either one, but I believe that to get what I'm after I need to be able to use both in a join kind of like my example.  

Treat these 2 as separate databases like if I have the database as DB1 and excel file as DB2.  This is how I would normally write my script.  With Excel, it's not something I work with regularly, so I don't know how I can link 2 databases into my query.

SELECT O.JOBNUM, O.OPER, O.DESC, J.PLANNED_HRS, J.ACTUAL_HRS
FROM DB1.DBO.ORDER O INNER JOIN DB1.DBO.JOB J ON O.JOBNUM = J.JOBNUM
WHERE O.JOBNUM IN (SELECT EXCEL.JOBNUM FROM DB2.DBO.WORKSHEET1 WHERE EXCEL.DATE BETWEEN @STARTDATE AND @ENDDATE)
CERTIFIED EXPERT
Top Expert 2015
Commented:
Okay here is a sample query for your reference which i create to access my database from excel..you can design similarly something like this...
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23882655.html
Saurabh...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
That looks very similar to what I am looking at doing.  Comparing another excel spreadsheet with my database and if record exists in Excel, then bring it to the spreadsheet.

For the connection string, if I created an ODBC, can I point that to the ODBC?  Or if you can provide me the syntax that would be awesome.  I'll test it out and let you know.
CERTIFIED EXPERT
Top Expert 2015

Commented:
if you see lines 14 and 15 over my code..these are the lines where i connect with the database and open a connection with it...

Author

Commented:
I see that you point directly to the access database.  We have a sqlbase database and only way I was able to connect to it is through ODBC.  

Would this work?

Driver=SQLBaseODBC;ServerName=myServerAddress;Database=myDataBase;LogonID=myUsername; Password=myPassword;
CERTIFIED EXPERT
Top Expert 2015

Commented:
I dont have much of expertise of connecting to sql through odbc..what i would recommend you can go ahead and ask a related question for it in the correct zones..asking for experts help who have a expertise over the subject...

Author

Commented:
Sweet thank you.  I used your example and was able to autofill in the missing data that I need of the database.

Just for future reference if anyone need to know how to connect to an ODBC using this example, here's the information:

SET db = OpenDatabase("", False, False, "DSN=YourDatasource;SRVR=TCP/IP:YourServerOrIP;UID=YourUserID;PWD=YourPassword")

Author

Commented:
BTW with your example, if I have more than one fields I want to put in there, how would I assign that?  For example you have A2:A, but with if I want B2:B and C2:C as will?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.