Jak mogę wykonać procedurę przechowywaną w programie Microsoft Excel i uzyskać wszystkie dane, które zwraca?
Jak mogę wykonać procedurę przechowywaną w programie Microsoft Excel i uzyskać wszystkie dane, które zwraca?
Odpowiedzi:
Jest to zadanie dla połączenia ADODB w VBA. Oto link z przykładowym kodem do prostego zapytania SELECT, ale to również będzie obsługiwać procedury przechowywane.
http://www.ozgrid.com/forum/showthread.php?t=83016&page=1
Kluczowe elementy to potrzeba zadeklarowania ADODB.Connection
, ADODB.Recordset
i ciąg połączenia, który pasuje do twojej bazy danych. Po otwarciu połączenia wykonuje się instrukcję SQL przy użyciu składni podobnej do następującej (pobranej z linku):
With cnt
.CursorLocation = adUseClient
.Open stADO // stADO is the connection string.
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
Następnie przenieś dane z zestawu rekordów ( rst
, powyżej) do zakresu za pomocą Range.CopyFromRecordSet
.
Nie jestem pewien co do najnowszego wcielenia programu Excel, ale w 2000 i 2003 roku wszystko, co można zrobić, to uzyskać dostęp do widoku i wyświetlać jego dane na arkuszu programu Excel.
Główną zaletą procedury składowanej byłaby możliwość sparametryzowania wyniku, ale do tego potrzebny byłby jakiś interfejs użytkownika i potrzebny byłby sposób programowej modyfikacji definicji zapytania po pierwszym zdefiniowaniu w programie Excel. Nie znaleźliśmy sposobu, aby to zrobić, ale używanie widoków zapewniało wystarczającą funkcjonalność do tego, co musieliśmy zrobić.
Ten VBA jest bardzo podobny do odpowiedzi @ Excellll i użyłem go z dobrym skutkiem w mojej pracy.
Użyj tej małej funkcji użyteczności:
Public Function IsEmptyRecordset(rs As Recordset) As Boolean
IsEmptyRecordset = ((rs.BOF = True) And (rs.EOF = True))
End Function
I tu jest wielka funkcja (przepraszam za wyrównanie paragrafów)
Option Explicit
Public Sub OpenConnection()
Dim conn As ADODB.Connection
Dim str As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim myPath
Dim fld
Dim i As Integer
On Error GoTo errlbl
'Open database connection
Set conn = New ADODB.Connection
'First, construct the connection string.
'NOTE: YOU CAN DO THIS WITH A STRING SPELLING OUT THE ENTIRE CONNECTION...
'conn.ConnectionString = _
' "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & _
' myPath & "\ConnectionTest.mdb"
'...OR WITH AN ODBC CONNECTION YOU'VE ALREADY SET UP:
conn.ConnectionString = "DSN=myDSN"
conn.Open 'Here's where the connection is opened.
Debug.Print conn.ConnectionString 'This can be very handy to help debug!
Set rs = New ADODB.Recordset
'Construct string. This can "Select" statement constructed on-the-fly,
'str = "Select * from vwMyView order by Col1, Col2, Col3"
'or an "Execute" statement:
str = "exec uspMyStoredProc"
rs.Open str, conn, adOpenStatic, adLockReadOnly ‘recordset is opened here
If Not IsEmptyRecordset(rs) Then
rs.MoveFirst
'Populate the first row of the sheet with recordset’s field names
i = 0
For Each fld In rs.Fields
Sheet1.Cells(1, i + 1).Value = rs.Fields.Item(i).Name
i = i + 1
Next fld
'Populate the sheet with the data from the recordset
Sheet1.Range("A2").CopyFromRecordset rs
Else
MsgBox "Unable to open recordset, or unable to connect to database.", _
vbCritical, "Can't get requested records"
End If
'Cleanup
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
exitlbl:
Debug.Print "Error: " & Err.Number
If Err.Number = 0 Then
MsgBox "All data has been pulled and placed on Sheet1", vbOKOnly, "All Done."
End If
Exit Sub
errlbl:
MsgBox "Error #: " & Err.Number & ", Description: " & Err.Description, _ vbCritical, "Error in OpenConnection()"
Exit Sub
'Resume exitlbl
End Sub
Mam nadzieję że to pomoże.