April 16, 2004 at 1:12 pm
Hello,
I need to output a stored procedure (that has 1 parameter) to an Excel file. The only way I can figure out how to do this is to use a Macro, because it allows me to Output the stored procedure to an Excel file. I can't figure out how to pass a control on a form through to the stored procedure, with the Macro in between.
Here is the macro (saved as a module):
DoCmd.OutputTo acStoredProcedure, "spEventReports", "MicrosoftExcelBiff5(*.xls)", "", False, "", 0
Here is some of the stored procedure:
ALTER PROCEDURE dbo.spEventReports
(@EID int)
AS SELECT dbo.tblEvents.EventID, dbo.tblEvents.EventDate, dbo.tblEvents.EventName .......
FROM dbo.tblEvents INNER JOIN ..........
WHERE (dbo.tblEvents.EventID = @EID)
I need to pass a control on the form to @EID, when the macro runs, something like @EID = [Forms]![frmEvents]![EventID]
Hope this makes sense for someone. Thanks in advance for your help!
Thanks!
April 16, 2004 at 2:16 pm
I have started bypassing Access when my end result is getting data into an Excel spreadsheet. Here is a bit of VBA code I put into an Excel template. The template has a GetData button and textboxes for parameters and database usernames and passwords.
If you prefer/need to use Access, you will need to add lines to instatiate an Excel app and workbook.
Hope this helps.
Private Sub Get_Data()
Dim cmd As New ADODB.Command
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim par1 As New ADODB.Parameter
Dim par2 As New ADODB.Parameter
Dim par3 As New ADODB.Parameter
Dim user As String
Dim pass As String
user = Me.txtUser.Text
pass = Me.txtPass.Text
With par1
.Name = "@part"
.Direction = adParamInput
.Type = adChar
.Size = 15
.Value = Cells(2, 8).Text
End With
With par2
.Name = "@begin"
.Direction = adParamInput
.Type = adChar
.Size = 10
.Value = Cells(6, 6).Text
End With
With par3
.Name = "@end"
.Direction = adParamInput
.Type = adChar
.Size = 10
.Value = Cells(6, 10).Text
End With
cmd.CommandText = "WO_COST_FETCH"
cmd.CommandType = adCmdStoredProc
con.Provider = "sqloledb"
'con.Properties("Prompt") = adPromptAlways <---
con.Open "Data Source = SERVERNAME; Initial Catalog = DATABASE; User ID = " & user & "; Password = " & pass & ";"
cmd.ActiveConnection = con
cmd.Parameters.Append par1
cmd.Parameters.Append par2
cmd.Parameters.Append par3
Set rst = cmd.Execute
Cells(12, 1).CopyFromRecordset rst
rst.Close
con.Close
Set cmd = Nothing
Set con = Nothing
Set rst = Nothing
End Sub
Sean Wyatt
seanwyatt.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply