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

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.