Thread: Embed SQL Drill Results in Excel

Hi Andrea

Sorry for delay there.
You should be able to see that up easily enough. Here is one way you could check out.

First thing would be to setup the query and embed it into Excel.
When you have the SQL Drill main form open, and have a query setup, you should see an "Embed" button. The button will only get enabled when you have a name in the text box.. You can hit the button and embed the query into an excel sheet.

After that you could add say a button to the sheet.

Then, a good thing to do would be to setup an Excel "Named Range" and associate with the range where you have the embedded query. When I do this I typically use the top left hand corner of the range. Not necessary but it makes it a bit simpler to maintain the sheet I think.

Lastly you could add in a VBA macro and associate it with the button.
A simple macro is shown below. This will the embedded query that is in the same range area as the "QUERY1_RANGE".. So the "QUERY1_RANGE" is just a named range that is (say) in the top left hand corner of the area/range where you have your embedded query..

[highlight=VBA]
Sub RefreshQuery1Data()
Range("QUERY1_RANGE").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub
[/highlight]

Hope this will be of some help to you. There are a bunch of ways to do this but this is one way that can be very good.
Have a nice night
Al

Hi Al,
Thanks! I was able to embed the data into the worksheet.
Now I am having trouble with the button and the macro.
It is giving me errors, I am sure I am not doing it correctly, the names or something.
Another thing, the results that I get when I update the query will vary, for example, I can have 24 rows one time and then when I update it in 20 min. I can have 54 rows. Does this matter when selecting the Range for the button?
Can you help me a little more with the button and macro?? I really have no idea.

Thanks again,
Andrea.

Hi
Hope all is good.

Is the refresh working fine when you manually do it ? I mean if you right click within the table area and select "Refresh" from the short cut menu..

If this is working fine then you could just record a macro to do it. So turn on the macro recorder, run the manual refresh and then stop the recorder. Then you could take a look at the VBA to see how to do it. The macro recorder is great for this type of situation. Once you have macro working fine you can attach it to the button..

For the number of rows being returned. That should not be a problem as there are some settings there that can let you control how the data gets pasted in (if you clear out previously pasted data etc..). If you right click in a table area that has an embedded query, you will see a "Data Range Properties" entry that will load the form show below. Towards the end of the form you have some options that let you specify an option that will "Overwrite existing cells with new data, clear unused cells".. This will do what you want I think (in Excel 2007, I think the "Data Range Properties" is visible within the "Table" short cut item..)

Hope this will get you going. Let me know how it goes when you get a chance. Good luck on that. Al

Hey Al, thanks for the info!
I was able to record the macro and made sure it woked fine. However, when I add a button, it does not work. Here's what I'm doing:
Developer -> Insert -> Form Control & choose button ( i don't know if I should be choosing the button from the "Form Controls" menu or the "ActiveX Controls" (I don't really know the difference or when to use each one. So I chose the Forms Control. When I draw the button, a list of the macros come up & I select the one I just created. When I click on the button to test it, I get an error, I tryed to add a file with a pic. of the error screen but couldn't.
This is the error I get:
"Run-time error '1004':
Application-defined or object defined error.
When I click in the debug option, I get the following in the VBA window.

Sub Update_Current_Query()
'
' Update_Current_Query Macro

Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

With the Selction. ..... line highlighted.

Thanks again for your time & help!
-Andrea