Excel and Databases

Does anyone know of a good tutorial on how to pull data from a database via
an ODBC connection? Is this possible in Excel 2000 or just in 2003? The basic
idea of the project I am working on is to pull data from a database and
populate our excel spreadsheet that does various calculations and then is
redistributed through out the company and partners after pasting just the
values.

Have you tried using MS Query?:
Data>Import External Data>New Database Query
....then follow the prompts.

Does that help?

***********
Regards,
Ron


"Stanley" wrote:

> Does anyone know of a good tutorial on how to pull data from a database via
> an ODBC connection? Is this possible in Excel 2000 or just in 2003? The basic
> idea of the project I am working on is to pull data from a database and
> populate our excel spreadsheet that does various calculations and then is
> redistributed through out the company and partners after pasting just the
> values.

That's one big question. The simple bit to answer is that you can do it easily with all versions of Excel from '97. (can't remember what it was like in 95 and it was a bit of a sod in V5)

As to how need to be a bit clearer as to what you are really trying to do : pulling in lots of data, a few choice numbers where is the data held etc.

The data is in an AS400 system right now but our I.T. dept has it being
trasferred to an Oracle system because apparently Java can't talk to the
AS400. Or at least that is my understanding of why we have the Oracle dB.
(that is a whole other issue) It is my understanding that you can connect to
an AS400 system with ODBC so I was not sure what Excel used to make it's
database connections. The data will not be large, at least not large in the
sense of megs of data. It is merely about 20-100 dates that are used to
populate the beginning of the spreadsheet. Everything else is simply
calculations based on other information provided.

Thanks for the version info that is definetly one of the pieces that was
needed.

"tony h" wrote:

>
> That's one big question. The simple bit to answer is that you can do it
> easily with all versions of Excel from '97. (can't remember what it was
> like in 95 and it was a bit of a sod in V5)
>
> As to how need to be a bit clearer as to what you are really trying to
> do : pulling in lots of data, a few choice numbers where is the data
> held etc.
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=490768
>
>

Stanley,

I'm not sure if you're still looking for an answer. But, it is definitely
possible to get your data from the 400 to Excel. There are a number of ways.
Either through a data transfer, which would create a file that could then be
read by excel, or through an ODBC connection which would let you access the
file on the 400 directly.

I have been working with this recently myself. There are a number of
websites with info out there. Basically, set up an ODBC connection to the 400
system (I did this using the ISeries library). This is fairly simple.

You can then open the connection in Excel via VBA, and use a Select statement.

Something like below...

Dim Con As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim Cmd2 As New ADODB.Command
Dim Rs As ADODB.Recordset

Con.Open "DSN=AS400;"
Set Cmd.ActiveConnection = Con
Cmd.CommandText = "SELECT * FROM F4311 WHERE PDDOCO = 1843318"

However, I am currently having a problem with the dates that this operation
gives to excel. They are not converting properly. They appear to come over in
numeric format (number of seconds since benchmark) but when converting this
number to a date format, it is not correct. I have not yet figured out why.
Perhaps you won't have the same problem.

Maybe someone reading this can give an insight into why...



"Stanley" wrote:

> The data is in an AS400 system right now but our I.T. dept has it being
> trasferred to an Oracle system because apparently Java can't talk to the
> AS400. Or at least that is my understanding of why we have the Oracle dB.
> (that is a whole other issue) It is my understanding that you can connect to
> an AS400 system with ODBC so I was not sure what Excel used to make it's
> database connections. The data will not be large, at least not large in the
> sense of megs of data. It is merely about 20-100 dates that are used to
> populate the beginning of the spreadsheet. Everything else is simply
> calculations based on other information provided.
>
> Thanks for the version info that is definetly one of the pieces that was
> needed.
>
> "tony h" wrote:
>
> >
> > That's one big question. The simple bit to answer is that you can do it
> > easily with all versions of Excel from '97. (can't remember what it was
> > like in 95 and it was a bit of a sod in V5)
> >
> > As to how need to be a bit clearer as to what you are really trying to
> > do : pulling in lots of data, a few choice numbers where is the data
> > held etc.
> >
> >
> > --
> > tony h
> > ------------------------------------------------------------------------
> > tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> > View this thread: http://www.excelforum.com/showthread...hreadid=490768
> >
> >