Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: Connect Excel and Oracle Database

    Hi,

    I need to write SQL queries and pull my results into an Excel Spreadseet.

    I have set up the server as a datasource in Excel (data) but need the VBA code in relation to opening the connection, the recordset, executing the SQL.

    If you have any code or know a good site that provides the VBA code needed then please let me know,

    Cheers!!
    Last edited by walshy06; 07-06-06 at 11:51.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Whenever I need an excel spreadsheet from a query I use TOAD.
    Seems to work pretty well and does everything for you.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    I need to be able to refresh the data when the spreadsheet is opened.


    For example on one sheet I have a list of ID's that updated everytime the spreadsheet is opened. I want to be able to use those ID's to pull back data from the server, so i will need some VBS to pass the SQL string into and run the connection..

    I'm not sure that I can do this with TOAD, or can I?

    Thanks!

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you cannot do that with TOAD.
    Good luck!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Maybe this can help.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    May 2006
    Posts
    132
    Once you have your datasource configured and working properly, you can refresh a workbook when you open it, by doing the following:

    1. Pull on the External Data toolbar by going to View --> Toolbars --> External Data
    2. Click on the Data Range Properties icon and then choose "Refresh data on file open" check box.

  7. #7
    Join Date
    May 2004
    Location
    Pune, India
    Posts
    15
    Hi,
    Is there any way to import data from .xls file into oracle? Without saving it as csv file?

    There is some problem when we try to save the xls file as csv file.
    Thanks in advance.
    regards

    Abhivyakti

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You can write a VBA macro to loop through the cells and insert them into Oracle using say ADO. There are plenty of examples in the VBA help showing you how to make the connection, insert records etc. You can also get it to pull data out aswell.

    Alan

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What's the problem when saving the file to csv ? (I believe there's more than primitive data on that excel sheet..)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •