Excel VBA Code for creating a temp table and fetch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Excel VBA Code for creating a temp table and fetch

Author

Topic

Venu@wfis
Starting Member

16 Posts

Posted - 2009-03-25 : 12:30:26

I am trying to create an excel report. I got the query in SQL, however not having much luck in creating a VBA code to fetch data from Excel

Here is my code:
I am receiving Application defined or Object defined error any help. Thank you.

Sub ExtractDatafrSQL()

Dim Cn As ADODB.Connection

Dim SQLStr As String
Dim mySQl As Object
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim oQt As QueryTable

SQLStr = "use [migration_mtg_bau_db]"
SQLStr = SQLStr & " create table #ByProject ([ProjectNo] varchar(50), ApplicationName varchar(50), Priority varchar(50), [ExecutionStatus] varchar(50), Number int)"
SQLStr = SQLStr & " insert into #ByProject ([ProjectNo],ApplicationName, Priority, [ExecutionStatus], Number)"
SQLStr = SQLStr & " select ts_User_07,ts_user_05, ts_user_09, ts_exec_status, count(ts_exec_status)from td.Test"
SQLStr = SQLStr & " where ts_user_07 in ('P0018075', 'P0019812', 'P0019922', 'P0019947', 'P0019974', 'P0019975', 'P0020085', 'P0020275', 'P0020291','P0020380', 'P0020545', 'P0020620', 'P0020679') and ts_user_01 in ('R2.09', '2.09')"
SQLStr = SQLStr & " group by ts_User_07, ts_user_05, ts_user_09, ts_exec_status"
'Filter By Project No
SQLStr = SQLStr & " SELECT ProjectNo, Priority,"
SQLStr = SQLStr & " SUM(Number) AS Total,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='Passed' THEN Number ELSE 0 END) AS Passed,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='Failed' THEN Number ELSE 0 END) AS Failed,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='No Run' THEN Number ELSE 0 END) AS Pending,"
SQLStr = SQLStr & " sum(CASE WHEN ExecutionStatus='Not Completed' THEN Number ELSE 0 END) AS NotCompleted"
SQLStr = SQLStr & " FROM #ByProject"
SQLStr = SQLStr & " GROUP BY ProjectNo,Priority"
SQLStr = SQLStr & " order by ProjectNo"
'SQLStr = SQLStr & "Where ts_user_09 in ('1-High','2-Medium','3-Low')"
Set Cn = New ADODB.Connection
'Print Cn
'Cn.Open "Provider=SQLOLEDB; Datasource=t01sql01; Initial Catalogue=migration_mtg_bau_db;User Id=vpatlol;Password=Venu@0681"

Cn.Open "Provider=sqloledb;" & _
"Data Source=t01sql01;" & _
"Initial Catalog=migration_mtg_bau_db;" & _
"Integrated Security=SSPI"

' Set oQt = ActiveSheet.QueryTables.Add(Connection:=Cn, Destination:=Range("a1"), Sql:=SQLStr)

' oQt.Refresh

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z1000") ' Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-30 : 09:07:04

SQL Server to Excel:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Create a stored procedure which exports to excel. Then just call stored procedure from your application.

Venu@wfis
Starting Member

16 Posts

Posted - 2009-04-03 : 06:13:50

Hi

As I am working on a production system (without any dev/test environment) it is not possible for me to create a procedure. all I have to do is to create a temp table and then fetch from that table and close that. Everything through VBA code: I got uptil now

Sub ADOExcelSQLServer()

Dim Cn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Cmd1 As ADODB.Command

Dim SQLStr As String
Dim sqlstr1 As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cmd = New ADODB.Command
Set Cmd1 = New ADODB.Command

'Cn.ConnectionString = "Provider=sqloledb; Data Source=t01sql01; Initial Catalog=migration_mtg_bau_db; Integrated Security=SSPI"
Cn.ConnectionString = "Provider=sqloledb; Data Source=t01sql01; Initial Catalog=tempdb; Integrated Security=SSPI"

Cn.Open Cn.ConnectionString
MsgBox "Connection Opened"

With Cmd
.CommandText = SQLStr
.CommandType = adCmdUnknown
.ActiveConnection = Cn.ConnectionString
End With

SQLStr = SQLStr & " if object_id('tempdb.#ByProject') is not null"
SQLStr = SQLStr & " drop table tempdb.#ByProject"
SQLStr = SQLStr & " create table tempdb.#ByProject ([ProjectNo] varchar(50), ApplicationName varchar(50), Priority varchar(50), [ExecutionStatus] varchar(50), Number int)"
SQLStr = SQLStr & " insert into tempdb.#ByProject ([ProjectNo],ApplicationName, Priority, [ExecutionStatus], Number)"
SQLStr = SQLStr & " select ts_User_07,ts_user_05, ts_user_09, ts_exec_status, count(ts_exec_status)from migration_mtg_bau_db.td.Test"
SQLStr = SQLStr & " where ts_user_07 in ('P0018075', 'P0019812', 'P0019922', 'P0019947', 'P0019974', 'P0019975', 'P0020085', 'P0020275', 'P0020291','P0020380', 'P0020545', 'P0020620', 'P0020679') and ts_user_01 in ('R2.09', '2.09')"
SQLStr = SQLStr & " group by ts_User_07, ts_user_05, ts_user_09, ts_exec_status"
sqlstr1 = sqlstr1 & " SELECT ProjectNo, Priority,"
sqlstr1 = sqlstr1 & " SUM(Number) AS Total,"
sqlstr1 = sqlstr1 & " sum(CASE WHEN ExecutionStatus='Passed' THEN Number ELSE 0 END) AS Passed,"
sqlstr1 = sqlstr1 & " sum(CASE WHEN ExecutionStatus='Failed' THEN Number ELSE 0 END) AS Failed,"
sqlstr1 = sqlstr1 & " sum(CASE WHEN ExecutionStatus='No Run' THEN Number ELSE 0 END) AS Pending,"
sqlstr1 = sqlstr1 & " sum(CASE WHEN ExecutionStatus='Not Completed' THEN Number ELSE 0 END) AS NotCompleted"
sqlstr1 = sqlstr1 & " FROM tempdb.#ByProject"
sqlstr1 = sqlstr1 & " GROUP BY ProjectNo, Priority"
sqlstr1 = sqlstr1 & " order by ProjectNo"
sqlstr1 = sqlstr1 & " if object_id('tempdb.#ByProject') is not null"
sqlstr1 = sqlstr1 & " drop table tempdb.#ByProject"

Cmd.CommandText = SQLStr
Set rs = Cmd.Execute
MsgBox "File1 Created"

'rs.Open

With Worksheets("sheet1").Range("a1:K500") ' Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With

'Close the recordset
rs.Close
'Tidy up
'rs1.Close
'Set rs1 = Nothing
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub

But when I run the code it stops at '.CopyFromRecordset rs' and says "the operation can't be performed when object is closed". When I say rs. open
then it says
"Operation is not allowed when the object is closed". Thank you.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-03 : 06:40:58

There is a lot of different things happening above. Strip it down and the errors will be more obvious.
Why are you using the temporary table? You are using following stages to retrieve from SQL:
1.create temptable
2. insert into temptable the Select ....,
3. Select from temptable
4. drop temptable

- Replace with a single Select statement with column aliases to match the temptable column names.

Check if error is accessing SQL or at Excel:
Comment out everything below "Cmd.CommandText = SQLStr" and instead use:

Set rs = cmd.Execute

With rs
Do While Not .EOF
Msgbox(.Fields("ProjectNo"))

.MoveNext
Loop
End With
Set rs = Nothing
End Sub

Did this work?

Venu@wfis
Starting Member

16 Posts

Posted - 2009-04-03 : 07:26:27

I think it is a problem with Excel only. I tried executing in SQL Server and it is working fine without any issues.
And also a simple select is also working fine with VBA. But when I execute the above it points out the error at 'Do while Not .EOF' and says 'Run time error 3704: Operation is not allowed as object is closed'.

And the reason that I made creation of temp table and multiple select is because i found it very difficulat to get the result in a format as specified by the user. For this i followed this:
1) I created a temp table with the data required satisfying the filter conditions and where conditions
2) Then i created an sql select statement to fetch data from the temp table

Now that I have to do both the above activities by through VBA only, its creating a little problem for me. Please suggest if there an alternate for this. Thank you.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-03 : 07:46:57

Let's just test the connection to SQL Server from VBA.

Sub ExtractDatafrSQL()

Dim Cn As ADODB.Connection

Dim SQLStr As String
Dim mySQl As Object
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim oQt As QueryTable

SQLStr = "use [migration_mtg_bau_db]"
SQLStr = SQLStr & " select top 1 ts_User_07,ts_user_05, ts_user_09, ts_exec_status, count(ts_exec_status) from td.Test"

Set Cn = New ADODB.Connection
'Print Cn
'Cn.Open "Provider=SQLOLEDB; Datasource=t01sql01; Initial Catalogue=migration_mtg_bau_db;User Id=vpatlol;Password=Venu@0681"

Cn.Open "Provider=sqloledb;" & _
"Data Source=t01sql01;" & _
"Initial Catalog=migration_mtg_bau_db;" & _
"Integrated Security=SSPI"

Cmd.CommandText = SQLStr
Set rs = cmd.Execute

With rs
Do While Not .EOF
Msgbox(.Fields("ProjectNo"))

.MoveNext
Loop
End With
Set rs = Nothing
End Sub

Venu@wfis
Starting Member

16 Posts

Posted - 2009-04-03 : 08:07:44

I just added DIM cmd as ADODB.Command and executed the code it gives the error 'Rune-time error 3709: application defined or object defined error'. Thanks

Venu@wfis
Starting Member

16 Posts

Posted - 2009-04-03 : 08:08:20

And all the reference from Tool> References has been done

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-03 : 09:13:01

Sorry I copied some of function from first part - I left out some of the declaring variables. After adding the Dim's I left out - Is the error happening in the trimmed down code I posted or your full version?
The error seems to be an excel error: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20968421.html

cresset
Starting Member

1 Post

Posted - 2010-08-23 : 05:08:23

I had the same problem with stored procedures that use temporary tables.

The solution for me was to use a SET NOCOUNT ON at the beginning of my SQL. I have no idea why this works, but since this thread popped up on the top of my google search, you can at least try it!

itpete
Starting Member

1 Post

Posted - 2012-04-18 : 05:57:41

quote:
Originally posted by cresset

I had the same problem with stored procedures that use temporary tables.

The solution for me was to use a SET NOCOUNT ON at the beginning of my SQL. I have no idea why this works, but since this thread popped up on the top of my google search, you can at least try it!

Believe this works as Excel does not handle the SQL messages sent on number of rows created each time a temp table is created

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources