Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel DAO to Oracle

Posted on 1998-11-20
4
Medium Priority
?
371 Views
Last Modified: 2012-06-21
Part 1. I have Excel 97 SP2, I want to query data from Oracle database and populate sheet and/or particular cells. Part 2. I tried to create procedure but "Dim db as database", "Database" was not recognize as a data type, Why?
0
Comment
Question by:gmoore
  • 2
4 Comments
 

Expert Comment

by:timjc
ID: 1614571
Have you tried recording a macro to do this. As far as I know to do this, under data menu, choose Get External Data... where the following steps should be self explanatory.

Hope this helps.

The reason why it doesn't recognise  Database as an object is you might not have all the add-ins installed. Look for ODBC and Oracle add-ins.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1614572
...some words from Excel Help: "To use DAO, you must first create a reference from your workbook to the DAO library. To create this reference, activate a Visual Basic module, click References on the Tools menu, and then select Microsoft DAO 3.0 Object Library in the Available References box." - after this Database will be recognized datatype. And you can get data from external database with code like that:
Sub MyQ()
  Dim dbs As database, qDef As QueryDef, rs As Recordset
  Set dbs = Workspaces(0).OpenDatabase("d:\my1.mdb")
  MsgBox "The " & dbs.Name & " database is now open"
  Set qDef = dbs.CreateQueryDef("TEST")
  qDef.SQL = "SELECT * FROM DIVISION;" ' any your SQL query
  Set rs = dbs.OpenRecordset("TEST")
  numberOfRows = Sheets("Sheet1").Cells(1, 1).CopyFromRecordset(rs)
  dbs.Close
End Sub
I've test it and it works (Excel 95 - I hope, '97 too). Check it, maybe for Oracle and '97 will be some differences. Good luck!
0
 
LVL 2

Accepted Solution

by:
pjmaze earned 600 total points
ID: 1614573
Hi,
here it is :

Sub OpenConnectionX()
    Dim work_ODBC As Workspace
    Dim con_oracle As Connection

    Set work_ODBC = CreateWorkspace("NewODBCWorkspace", _
        "username", "password", dbUseODBC)
       
    work_ODBC.DefaultCursorDriver = dbUseODBCCursor

    Set con_oracle = work_ODBC.OpenConnection("con_oracle", _
        dbDriverComplete, False, "ODBC;DSN=IN_OR7")

    con_oracle.Close
    work_ODBC.Close
End Sub

If you dont suply the fields 'username' and 'password' ODBC will prompt them.
The DSN is the name of the ODBC driver that you use, see Control Panel->ODBC

if you need some more help ask, and i will comment it !

see ya
0
 
LVL 2

Expert Comment

by:pjmaze
ID: 1614574
I forgot to say that you must open VBA editor (alt+F11) go to Tools->references and Check Microsoft DAO 3.5

see ya...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question