Excel 2010 executing SQL Server SP throws 445 'Object doesn't support this action.'

I am trying an example but running into trouble getting excel 2010 to run a SQL server 2014 SP, passing parameters of date and category.

Here is the code:

==============================
Sub btn_go_Click()

'Refresh the table
'Call fn_execute(Range("month_value").Value, Range("category_value").Value)
'The following refers to cells A3 for Date and A4 for category
Call fn_execute(Worksheets(1).Cells(3, 1).Value, Worksheets(1).Cells(4, 1).Value)

ex:
    On Error Resume Next
    Exit Sub

eh:
    MsgBox "An error occured: " & Err.Number & ", " & Err.Description
    Resume ex

End Sub

Public Function fn_execute(dtMonth As Date, sCategoryName As String)

'Execute the SQL Server Stored Proc xrpt_fuel_burn with parameters entered by the user
'@dtMonth - YYYY-MM-01 of the month
'@sCategoryName

'05-22-14  jim.horn  Original

Application.Cursor = xlWait
Application.DisplayAlerts = False

'Dim cn As WorkbookConnection
Dim myCon As OLEDBConnection

Set myCon = ActiveWorkbook.Connections("xrpt_sales_by_category_by_month").OLEDBConnection

Dim sCommandText As String
sCommandText = "xrpt_sales_by_category_by_month "
sCommandText = sCommandText & "@dt='" & Format(dtMonth, Text) & "', "
sCommandText = sCommandText & "@category_name='" & sCategoryName & "'"

With myCon
    .CommandType = xlxmdsql
    .CommandText = sCommandText
    .BackgroundQuery = False
End With

ActiveWorkbook.Connections("xrpt_sales_by_category_by_month").Refresh

Application.Cursor = xlDefault

'ActiveWorkbook.RefreshAll

'Refresh all pivot tables
Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        pt.RefreshTable
        pt.Update
    Next
Next

ex:
    On Error Resume Next
    Application.DisplayAlerts = True
    Application.Cursor = xlDefault
    Set myCon = Nothing
'    Set cn = Nothing
    Exit Function

eh:
    MsgBox "An error occured: " & Err.Number & ", " & Err.Description
    Resume ex

End Function
===============================

I get the error on line Set myCon = ActiveWorkbook.Connections("xrpt_sales_by_category_by_month").OLEDBConnection

What am I doing wrong?
LVL 16
Richard OlutolaConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Are you sure your connection uses OLEDB and not ODBC? For the latter you want:
Set myCon = ActiveWorkbook.Connections("xrpt_sales_by_category_by_month").ODBConnection

Open in new window

and you would need to declare the myCon variable as either Object or ODBCConnection instead.
0
Richard OlutolaConsultantAuthor Commented:
Rory,

I was hoping you would respond because I've noticed you're the master on this topic.
I have changed to ODBC and now I get compile error:  user-defined type not defined.

The line it highlights is:
Dim myCon As ODBConnection.

Thanks.
0
Rory ArchibaldCommented:
It's ODBCConnection, not ODBConnection.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richard OlutolaConsultantAuthor Commented:
I corrected that and now I get the previous error 445 object doesn't  support this action.

Richard
0
Rory ArchibaldCommented:
Can you post a workbook, just so I can check a few things?

BTW, this:
 .CommandType = xlxmdsql

Open in new window

should be this:
 .CommandType = xlCmdSql

Open in new window

0
Richard OlutolaConsultantAuthor Commented:
Sorry I should have told you. I spotted that earlier and changed it. Problem still persists.
Workbook attached.

R.
0
Rory ArchibaldCommented:
No workbook that I can see...
0
Richard OlutolaConsultantAuthor Commented:
Rory,

I don't know what happened. I'll try it again.
It's called Sales By Category By Month.xlsm. It is 201KB so it should upload without issues.

Regards
Sales-By-Category-By-Month.xlsm
0
Rory ArchibaldCommented:
OK. The issue is that the connection isn't actually in use anywhere in the workbook - why are you trying to alter it?
0
Richard OlutolaConsultantAuthor Commented:
Rory,

I don't understand your question. Alter what?
I started the example from scratch but it didn't work so following online solution suggestions, I've gradually implemented the supposed fixes and its now in the current state.
Should I revert to the original again?

Thanks.
R.
0
Rory ArchibaldCommented:
I mean: that connection is not being used anywhere in your workbook. That's what is causing the errors. Why are you trying to alter a connection that isn't being used for anything?
0
Richard OlutolaConsultantAuthor Commented:
Is this not using the connection?

With ocn
    .CommandText = sCommandText
    .BackgroundQuery = False
    .Refresh
End With

Sorry I am trying to learn with this example but its proving difficult. I was hoping it would work first and then I can go through it and try and understand the parts.

Thanks.
RO
0
Rory ArchibaldCommented:
What I mean is that none of the tables/pivots in the workbook are using that connection - i.e. it's not called by anything in the workbook.
0
Richard OlutolaConsultantAuthor Commented:
In the workbook, there is a command button which is linked to the VBA code (btn_go_click) which in turn calls the function causing the problem.
This whole thing is meant to run a SQL Server Stored Procedure which gets data into Excel.

How else could I use this connection in Excel?

R.
0
Rory ArchibaldCommented:
Normally you would have a Table linked to the connection. if you don't there is no point to having the connection stored - you just do it all in code.
0
Richard OlutolaConsultantAuthor Commented:
OK. Can you recommend/suggest an example (that works) of how I can achieve my objective - run a SQL Server stored a procedure from Excel and display the resultset in Excel?

Many thanks.
0
Rory ArchibaldCommented:
Here's a simple example using ADO:

Sub GetSQLData()
' Sample demonstrating how to return a recordset from a SQL Server db
' Requires reference to Microsoft ActiveX Data Objects library

   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, i As Long
   Set cn = New ADODB.Connection
   With cn
      .Provider = "sqloledb"
      .ConnectionString = "Data Source=servername;Initial Catalog=database_name;Integrated Security=SSPI;"
      .Open
   End With
   strQuery = "Orders"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic
   With rst
      'Populate field names
      For i = 1 To .Fields.Count
         ActiveSheet.Cells(1, i) = .Fields(i - 1).Name
      Next i
      ' Copy data
      ActiveSheet.Range("A2").CopyFromRecordset rst
      .Close
   End With
   Set rst = Nothing
   cn.Close

   Set cn = Nothing
End Sub

Open in new window

0
Richard OlutolaConsultantAuthor Commented:
Thanks Rory
I'll look into that example.

By the way here is the example I was following:
http://www.experts-exchange.com/articles/13675/Microsoft-Excel-SQL-Server-Self-service-BI-to-give-users-the-data-they-want.html

I believe you helped this gentleman to resolve the issue he encountered before successfully completing the example above. Here is the issue with solution:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28426591.html

What do you think?
Do I have to use ADO rather than OLEDB?

Richard
0
Rory ArchibaldCommented:
You don't have to use ADO but to use the connection, you need to actually connect something to it - a table/pivot.
0
Richard OlutolaConsultantAuthor Commented:
Rory,

Sorry I haven't had the chance to implement your recommendation but that's because I feel it's taking me in a different direction. I was following a particular process and it'll be easier for me to complete that.
would it be possible for you to see the example I was following which provides an end to end procedure and tell me where I was going wrong or where the example falls short.

That example tells me what to do in Excel, then create the macro/vb code, then modify the vb code, then execute.
I followed that but didn't succeed. Could you please just fix where it's wrong rather than steer me in a different direction which is what I feel using ADO is.

I hope you can understand this.
I'm just learning so I need to try one thing first.

Thanks.
0
Rory ArchibaldCommented:
What you didn't do was actually return any data to the Excel workbook. Hence my comments about your connection not actually being connected to anything.
0
Richard OlutolaConsultantAuthor Commented:
Rory,

So is the example I was following wrong or did I not follow it correctly?

Thanks.
0
Rory ArchibaldCommented:
It appears that you missed the part where you connect to the SQL server and return the data to a worksheet as a Table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard OlutolaConsultantAuthor Commented:
OK. I'll take a look.

R.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.