Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel and Databases
Have you tried using MS Query?:
DataImport External DataNew 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel and Databases
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel and Databases
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel and Databases
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Am I able to use the dialing feature from numbers in Excel? | Excel Discussion (Misc queries) | |||
How to load Template Wizard /tracking to operate in Excel 2003? | Excel Discussion (Misc queries) | |||
Need One Excel Template to copy to TWO Databases | Excel Discussion (Misc queries) | |||
Excel databases. I'm lost. Curses. | Excel Discussion (Misc queries) | |||
How do you toggle back and forth between worksheets in Excel? | Excel Worksheet Functions |