: 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:\cre\dep\DTADISCO\ITEM & ITEM COST REPORTS\JME_itemcost_testv1.DIS")
: Set
dSheet = 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.
Dim eWB As Excel.Workbook
Set eWB = Excel.Workbooks.Add()
eWB.Worksheets.Add(dSheet)
eWB.SaveAs(..)
..
eWB.Close(True)
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
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)I hope you don't have too large datasets because this would be quite slow process with great amount of data..
Hope this helps...
Pavlin II[/size]
Don't take life too seriously anyway you won't escape alive from it!
