Querying data source from Excel?

eggtimer

Board Regular
Joined
Apr 16, 2003
Messages
125
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

eggtimer

Board Regular
Joined
Apr 16, 2003
Messages
125
Cheers Guy's

Both of those look like a real good start, at first glance the parameter query looks best as none of my maxro's contain the SQL queries. I write all of them into views.

But I will try and attempt both methods, out of curiousity if nothing else, and let you know how I get on

Thanks

Wayne
 

eggtimer

Board Regular
Joined
Apr 16, 2003
Messages
125

ADVERTISEMENT

Andrew

I have had a look at the parameter query, as I said before I am using 2007, so to access the connections I am using the route Data >> Connections > Properties >> Definition, which I found in excel help. But when I get to the edit query button its greyed out. I’ve tried all the options I can think of, but no joy.

I’ve had a look at the data source file in notepad and nothing stand out to me there, any suggestions?

I seem to remember in 2003 I used to just be able to open Edit Query straight from the menu??

Thanks
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

Hi, Wayne.

I can't test out Excel 2007 & SQL data source. I'm guessing that if it is like previous versions the parameterised query as already suggested should be OK.

However, another way to do the same thing is via VBA. So you have code to do the work instead of using the built in auto refresh functionality. Such as have a worksheet change event and if the cell with the user's name changes, the SQL text is amended and then the query refreshed. Like below, untested.

HTH, Fazza

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  'I don't know about ListObjects - new in Excel 2007 so I'm guessing about that bit.
  'I've assumed name entered in cell B1. Please adjust to suit.
  If Target.Address = "$B$1" Then
 
    Application.EnableEvents = False
 
    With YourSheetCodename.ListObject.QueryTable
    'In Excel 2003 the above line might be
    'With YourSheetCodename.QueryTables(1)
      .Sql = "SELECT * FROM History WHERE user ='" & Range("B1").Value & "'"
      .Refresh BackgroundQuery:=False
    End With
 
    Application.EnableEvents = True
  End If
End Sub
 

eggtimer

Board Regular
Joined
Apr 16, 2003
Messages
125
Hi
Thanks Fazza, I shall have a look at this.
I've had 2007 a few months now and following Andrews link, I have just discovered, how something I had thought was a change is really and addition.
When connecting to external data sources it now gives me the option "SQL Server" which I have been using and is great and because it worked I have ignored all of the other options on the list.

looking again now and using the bottom option I can also connect via MS Query as before, so I am now going to set that back up and see if I can do it as Andrew suggested.

I love this program, every time I think it’s wrong, after a little investigation, it always manages to prove to me that I am.

Wayne
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
OK, Wayne.

(Excel 2003) I prefer to use VBA & ADO though for a quick & dirty - or explaining to newbies on thread posts - I use MS Query. Sounds like it might have improved in Excel 2007.

One handy 'trick' that is sometime useful is to bypass the MS Query interface. Just go to the VBE's immediate window. In Excel 2003, to see the connection & SQL,
Code:
? activesheet.querytables(1).connection
? activesheet.querytables(1).SQL
These are read/write. So the returned strings can be edited in the immediate window, then go back to the debug.print line, delete the leading "? " and the trailing carriage return, add some quotes, etc to create
Code:
activesheet.querytables(1).sql = "the amended SQL string"
Enter this to change the SQL. Can be done quickly without going through MS Query. And then
Code:
activesheet.querytables(1).refresh
, or refresh from the worksheet is probably easier.

This method can be used to set up a parameterised query, too. Such as,
Code:
SELECT * FROM History WHERE user = ?

Please post back on how you go with MS Query. It'll be interesting to learn if Excel 2007 can readily do what you want.

regards, Fazza
 

eggtimer

Board Regular
Joined
Apr 16, 2003
Messages
125
Thanks Fazza

Typically, as soon as something interesting like this comes along, I get hit with something else thats important/urgent.

But I will be back on this tomorrow and I will keep you informed

Thanks for all your help so far

Wayne
 

Watch MrExcel Video

Forum statistics

Threads
1,127,464
Messages
5,624,885
Members
416,064
Latest member
PaulBr2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top