Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

Excel - Pulling data from database and excel file

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.
0
holemania
Asked:
holemania
  • 8
  • 5
1 Solution
 
reb73Commented:
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..

0
 
holemaniaAuthor 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
0
 
Saurabh Singh TeotiaCommented:
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...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
holemaniaAuthor 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.
0
 
holemaniaAuthor 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.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
holemaniaAuthor 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)
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
holemaniaAuthor 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.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
holemaniaAuthor 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;
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
holemaniaAuthor 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")
0
 
holemaniaAuthor 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?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now