export data from discoverer to excel using vba - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

export data from discoverer to excel using vba

shodan1shodan1 Posts: 1Member
Hi experts out there,

I would like to export data from an external database query into excel. I have been reading through different forums and posts, but I stll don't really understand things. Here is my problem:
I have an external database query program called discoverer. It uses SQL statements to query an oracle database. However I do not know anything about SQL I can use this program because it is like a user friendly interface for non programmers.
What I have so far in excel vba is the following:

Set DiscoApp = CreateObject("Discoverer.Application")
DiscoApp.Visible = True
DiscoApp.Connect ("USERNAME/password@database")
Set Workbook = DiscoApp.documents.Open("X:credepDTADISCOITEM & ITEM COST REPORTSJME_itemcost_testv1.DIS")
Set Sheet = Workbook.Sheets("Crosstab_IC")


This code perfectly starts the external application called discoverer, runs the query and returns the data. BUT of course this data is still in the external application. How can I now get the data from this query directly imported in excel? Discoverer even has a button to import data in excel so the program must know the command somehow. Why is it than so complicated? I appreciate all help I can get. Please be easy on me, I'm just a beginner and experimenting with VBA

Comments

  • PavlinIIPavlinII Posts: 404Member
    : Hi experts out there,
    :
    : I would like to export data from an external database query into excel. I have been reading through different forums and posts, but I stll don't really understand things. Here is my problem:
    : I have an external database query program called discoverer. It uses SQL statements to query an oracle database. However I do not know anything about SQL I can use this program because it is like a user friendly interface for non programmers.
    : What I have so far in excel vba is the following:
    :
    : Set DiscoApp = CreateObject("Discoverer.Application")
    : DiscoApp.Visible = True
    : DiscoApp.Connect ("USERNAME/password@database")
    : Set Workbook = DiscoApp.documents.Open("X:credepDTADISCOITEM & ITEM COST REPORTSJME_itemcost_testv1.DIS")
    : Set [blue]dSheet[/blue] = Workbook.Sheets("Crosstab_IC")
    :
    :
    : This code perfectly starts the external application called discoverer, runs the query and returns the data. BUT of course this data is still in the external application. How can I now get the data from this query directly imported in excel? Discoverer even has a button to import data in excel so the program must know the command somehow. Why is it than so complicated? I appreciate all help I can get. Please be easy on me, I'm just a beginner and experimenting with VBA
    :
    :
    ..:: oh, NO! I've just closed this window and lost my answer, so let's say I have good mood and I'm not lazy to write it again :) ::..

    Hi,
    let's take a look at it.. I don't know Discoverer but it's object model looks very similar to Excel's one. So your task could be solvable.. Ideal situation would be when discoverer is using Excel's object, but the world would be too much ping :) Let's say your discoverer sheet is called dSheet.
    [code]Dim eWB As Excel.Workbook
    Set eWB = Excel.Workbooks.Add()
    eWB.Worksheets.Add(dSheet)
    eWB.SaveAs(..)
    ..
    eWB.Close(True)[/code]But I guess .Add(dSheet) will cause error because of incompatible object types.
    But if the structure is really similar, there must exist dSheet.Range(..) or dSheet.Cells(.., ..) which provides you access to certain cells of result of your SQL query. Now, you can copy all the content manually
    [code]Dim eSheet As Excel.Worksheet, r As Integer, c As Integer
    Set eSheet=eWB.Worksheets(1)
    For r=1 To dSheet.Rows.Count
    For c=1 to dSheet.Columns.Count
    eSheet.Cells(r, c) = dSheet.Cells(r, c)
    Next c
    Next r
    eWB.Application.Visible=True
    ...
    eWB.Close(True)[/code]I hope you don't have too large datasets because this would be quite slow process with great amount of data..
    Hope this helps...

    [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]

    [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]


  • marschmrkmarschmrk Posts: 4Member
    Hello!
    It is my problem too. I would like opening a *.dis form the excel/vba.
    Your code is opening the discoverer frame, but it doesnt make any other.

    I am just a client, and i dont need any "USERNAME/password@database" for useing the discoverer.

    I tried this, in the VBA:
    [code]
    Set DiscoApp = CreateObject("Discoverer.Application")
    DiscoApp.Visible = True

    Dim user As String
    Dim pswd As String
    Dim dtb As String
    Dim connct As String

    user = ""
    pswd = ""
    dtb = "teszt" 'gondolom ez m
  • marschmrkmarschmrk Posts: 4Member
    This post has been deleted.
Sign In or Register to comment.