Hi vgvchinna,
How do you want to call store procedure in excel? Through VBA or excel UI directly (Data)?
For the VBA code, it would be like this:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[your table];Data Source=[your server];U" _
, _
"se Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXX;Use Encryption for Data=False;Tag wi" _
, "th column collation when possible=False"), Destination:=Range("$I$4")). _
QueryTable
.CommandType = xlCmdSql
.CommandText = Array("[your store procedure]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "XXX"
.Refresh BackgroundQuery:=False
End With
If you want to achieve that through excel UI directly, please refer to these steps below (excel 2013):
- Select Data tab
- Click From Other Sources=>From SQL Server
- Type the necessary information=>Next
- Select the database and tables=>Next=>Finish
- After that the Import Data window will be displayed
- Click Properties
- Select Definition tab
- Change Command type to SQL
- Type your store procedure name in the Command text
Base on that error message, I think the reason is that you don’t specify
SQL as the Command type. If you are using Table as the Command type, it will throws that exception.
On the other hand, this thread may benefit you:
# MS Excel- Creating Dynamic SQL statement
https://social.msdn.microsoft.com/Forums/en-US/6f0a354c-d186-4d61-9fd9-074a18823424/ms-excel-creating-dynamic-sql-statement?forum=exceldev
Regards
Starain
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click
HERE to participate the survey.