|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi there.
I'm attempting to use a VB Macro to retrieve a recordset from a SQL Server
stored procedure which takes in two parameters - and then paste the results
into a worksheet.
although I seem to be successful in retrieving the recordset (I can print
out the column names OK) - I'm having trouble with the CopyFromRecordSet
function.
Any suggestions anyone?......
Code below:
Dim con As Connection
Dim wsp As Workspace
Dim rst As Recordset
Dim qry As QueryDef
Dim ReportQuery As String
Set wsp = CreateWorkspace("", "admin", "", dbUseODBC)
Set con = wsp.OpenConnection("", dbDriverNoPrompt, True,
"ODBC;DATABASE=mydb;UID=myusr;PWD=mypwd;DSN=MyServer")
ReportQuery = "{call p_MyStoredProc (?,?)}"
Set qry = con.CreateQueryDef("RunStoredProc", ReportQuery)
qry.Parameters(0).Type = dbText
qry.Parameters(0) = 'TestParam'
qry.Parameters(1).Type = dbInteger
qry.Parameters(1) = 1
For nCols = 0 To rst.Fields.Count - 1
Worksheets("worksheet1").Cells(1, nCols + 1).Value =
rst.Fields(nCols).Name
If nCols <> 0 Then
Worksheets("worksheet1").Columns(nCols + 1).EntireColumn.AutoFit
End If
Next
'The following causes "Method CopyFromRecordSet" of Object Range failed
Worksheets("worksheet1").Range("A1").CopyFromRecordset rst,
rst.RecordCount, rst.Fields.Count
|
|
You probably want to copy to A2 rather than A1 to avoid overwriting the
headings...but that is probably not the problem.
CopyFromRecordset can give problems on some combinations of Excel and
Windows versions.
If you replace the CopyFromRecordset line with
lRow = 2
Do Until rst.EOF
For nCols = 0 To rst.Fields.Count - 1
Worksheets("worksheet1").Cells(lRow, nCols+1) = rst(nCols)
Next
rst.MoveNext
lRow = lRow+1
Loop
does that work?
Bill Manville
MVP - Microsoft Excel, Oxford, England
|
|
|