May 13, 2009 at 12:41 pm
Hi there,
My apologies for posting an excel related question in an Access forum but I just couldn't find an Excel forum.
Anyway,
I'm hoping other newbies like me might benefit from this thread.
Background:
Has anyone tried executing SQL 2005 stored procedures in Excel using the VB editor? At first it seems pretty easy.
I did the following in the 'view code' screen for sheet1 within the vb editor:
SUB blabla()
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring1 = "Execute stored procedure located on sql server"
' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
"ODBC;DSN=database;UID=sql authenticated login;PWD=password;Database=my database"
' Now implement the connection, run the query, and add' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring1)
End With
END SUB
--------Done---------
Ok, the above works great!
My problem arises when I try multiple executes for the same worksheet.
Yes, values are returned to the spreadsheet but it's like excel has a mind of its own and throws the values anywhere it wants. And it does this even when I hardcode specific destinations like: Destination:=Range("A5") for one query and Destination:=Range("A10"), for another... and so on.
Is this making sense to anyone? :crazy:
Here's a more accurate example of what I'm trying:
-------------------------
SUB mysub()
Dim qt As QueryTable
sqlstring1 = "Execute stored procedure located on sql server"
connstring = _
"ODBC;DSN=database;UID=sql authenticated login;PWD=password;Database=my database"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring1)
End With
sqlstring2 = "Execute stored procedure located on sql server"
connstring = _
"ODBC;DSN=database;UID=sql authenticated login;PWD=password;Database=my database"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A10"), Sql:=sqlstring2)
End With
sqlstring3 = "Execute stored procedure located on sql server"
connstring = _
"ODBC;DSN=database;UID=sql authenticated login;PWD=password;Database=my database"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A15"), Sql:=sqlstring3)
End With
sqlstring4 = "Execute stored procedure located on sql server"
connstring = _
"ODBC;DSN=database;UID=sql authenticated login;PWD=password;Database=my database"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A20"), Sql:=sqlstring4)
End With
sqlstring5 = "Execute stored procedure located on sql server"
connstring = _
"ODBC;DSN=database;UID=sql authenticated login;PWD=password;Database=my database"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A25"), Sql:=sqlstring1)
End With
End Sub
-----
I also posted a picture of the of results im getting.
Any help would really be appreciated.
~Chris
May 15, 2009 at 10:59 am
Ok,
At least people are looking at this.
Thanks for dropping by!!
😀
I've been thinking that I may be approaching my problem the wrong way so I created one worksheet for each subroutine. Now each worksheet produces the data I want but there are several drawbacks.
Drawback 1: I have to kick off each subroutine manually because they are all in different worksheets
Drawback 2: I really wanted all the data that is being returned from these subroutines to be displayed on one main worksheet called EnrollmentStats.
Drawback 3: I don't know how to make the subroutines run on each page automatically when I open the spreadsheet. Then I wouldn't have to kick them off manually.
The good news is that I've been able to resolve "Drawback 2" by building a macro that copies the subroutine output to the main worksheet.
It looks like this:
' Keyboard Shortcut: Ctrl+g
'---- 1 Copies cells from HEADCOUNT worksheet
Sheets("EnrollmentStats").Select
Range("A1:A2").Select
Selection.ClearContents
Sheets("HEADCOUNT").Select
Range("A1:A2").Select
Selection.Copy
'--- paste the HEADCOUNT cells to sheet called EnrollmentStats
Sheets("EnrollmentStats").Select
Range("A1:A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'----- 2 Copies cells from RETURN STUDENTS worksheet
Sheets("EnrollmentStats").Select
Range("A5:A6").Select
Selection.ClearContents
Sheets("RETURN_STUDENTS").Select
Range("A1:A2").Select
Selection.Copy
'----- Paste the RETURN STUDENTS cells to sheet called EnrollmentStats
Sheets("EnrollmentStats").Select
Range("A5:A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Now if I could just figure out how to tell all the sub's to dump fresh output to the worksheet that they're assigned to when the spreadsheet is opened.... At that point all I would have to do is run the above macro to copy all the data from the other worksheets into my main worksheet.
May 21, 2009 at 10:53 am
--- Problem Resolved ---
I was able to write all of my code in one MS Excel VB editor module (aka module1) instead of one sql statement for each workshet. This way I was able to execute all of my stored procedures and present all of my results on the first sheet.
'--Here was my broken code:
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
'--Here is how I fixed it:
With Sheet1.QueryTables.Add(Connection:=connstring, Destination:=Sheet1.Range("A1"), Sql:=sqlstring)
'Hardcoding the sheet that had the cell I wanted the data to be placed in was the key.
Yay!!!!!
:-D:-D:-D
Chris
May 22, 2009 at 8:35 am
Well done Chris!:-)
Could you post the excel file containing the code?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply