Querying data source from Excel?

[eggtimer]

Hi
I have an SQL data source (view) feeding my spreadsheet
The view selects activities completed that day by a particular user. As we have many users, I have written a number of different views for each user
i.e - Select * from History where user = ‘Wayne’
As you know, staff come and go, and I am getting a bit bored with doing this.

I was wondering would it be possible to have just a single field form where the User name can be entered.
So my query would be “Select * from History where user =” and whatever is entered in the box completes the statement.
I know this can be done manually using “edit query”, but the reports are supplied to others as management tools.
At present each individual report has a refresh button with below code attached

Range("C1").Select
Selection.ListObject.QueryTable.refresh BackgroundQuery:=False

I was hoping that if the refresh button was pressed, it would bring up a one field form that asks for the userid, once that is entered the user presses “OK” on the form and it disappears, and the above code is run, returning only the data for the specific user.

Is that asking a bit much? Do I make sense, is there a far easier way of doing this?


Using Excel 2007,


Cheers

Wayne

 

[eggtimer]

Hi Guy’s

After following Andrew’s link to ****’s Clicks (what a great site, loads of useful/interesting data there) it took some time locating the relevant functions in 2007 but I have managed to create what I wanted using parameter query. Great stuff, thanks

But Fazza, your method intrigued and then stumped me.

I copied the code you gave me into a module

Private Sub Worksheet_Change(ByVal Target As Range)
'I don't know about ListObjects - new in Excel 2007 so I'm guessing about that…etc etc

Btw – you are right about ListObjects this is a new addition for 2007 and another that stumped me for a while. Anything that was previously written in 2003 worked a treat but any new workbooks created in 2007, the code would not run (even when saved in 97-2003) this was particularly odd as with simple snippets like this I literally drag modules from one project to another. So when dragging a module from a 2003 to 2007 I had to change the code for it to work for me, but as a lot of my colleagues still had 2003, the addition of ListObjects meant it did not work for them.

So with the help of another great guy on this site “Hatman” I discovered “Conditional Compilation”

Anyway, I digress but because it bugged me so much thought I would mention it.

When I copy your above code into a module, it does not appear on my macro’s list, I have also tried copying it into worksheet and workbook, still no joy. Just to check I have copied a number of other macros in and they all appear.

I’m sure I am overlooking something obvious, but I don’t know what, I have never come across this before. I could well be something simple as I am a simple self taught guy

Cheers

Wayne

 

[Fazza]

Wayne,

I think we are nearly all self taught. Seems like it anyway.

The code I posted is for an event code. OK? These 'fire' when events occur - unless a setting is such that they are off. Looks like they don't appear in the macros list you might see from Excel (via ALT-F8).

They must be located in the correct place to work. A workbook event code, like on workbook open/close/etc must go in a workbook code module. The worksheet event code like I posted must go in the code module for the worksheet that you want it to effect.

This can be done from the VBE. Activate the code module for the particular sheet. At the top of the code screen is a dropdown showing general, change that to worksheet. This will immediately provide come code for an event - maybe not the one you want. So delete it if that is the case. In the adjacent (on the RHS) dropdown select which worksheet code event you want. And again, this will provide the first & last lines of a sub for that event. If you have a VBA book it should discuss this. VBA help will. I'm sure a site like Chip Pearson's will too. I'll post a link.

http://www.cpearson.com/excel/topic.aspx

Look at events. (I didn't check it is OK.)

If no events are firing, check via the immediate window. Type in "? application.enableevents" and return. If it isn't true, set it to be true with "application.enableevents=true"

OK? Regards, Fazza