How ti modify Output to function to send a table to an Excel file ? 1

[GarHeard]

I would like to send a table which I know has records in it to an Excel spreadsheet and open the spreadsheet for the user. The user could then save the spreadsheet if desired. I tried using the following sub procedure and functions. The Excel file does open but no records are displayed. Instead of cells I see the menu choices at the top of the Excel file and the body of the spreadsheet is a gray background.

I could use either OutputTo function or the TransferSpreadsheet function.

Do you know how I could go about modifying this sub procedure or functions shown below to open the spreadsheet with the table values once the FallReport function is executed ?

Private Function StartDocFallGroup(FileName)
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

'open excel template
Set xlApp = New Excel.Application
xlApp.Visible = True

xlApp.ScreenUpdating = True

End Function

Private Function isFileExist(filePath As String) As Boolean
isFileExist = (filePath <> "" And Dir$(filePath) <> "")
End Function

Private Sub FallReport()
Dim rstQueryFS As ADODB.Recordset, ExportedFile As String
ExportedFile = "C:\SUMMARYDOLLAR.xls"
Dim cn As ADODB.Connection
Dim P1 As New Parameter
Set cn = New ADODB.Connection
Dim com As ADODB.Command
Set rstQueryFS = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=LEDB.1;" & _
"Data Source=BKPEPRJCTDB01; Initial Catalog=U;" & _
"User ID =UD;PWD=C"
Set com = New ADODB.Command
With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procFGAllStates"
.Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
.Parameters.Append .CreateParameter("Prop", adVarChar, adParamInput, 5, strProp)
.Parameters.Append .CreateParameter("Agg", adVarChar, adParamInput, 5, strAgg)
.Parameters.Append .CreateParameter("IRA", adVarChar, adParamInput, 5, strIRA)
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With
DoCmd.OutputTo acOutputTable, "tblFGAllStates", acFormatXLS, ExportedFile, AutoStart
If isFileExist(ExportedFile) Then StartDocFallGroup ExportedFile
End Sub

 

[PHV]

Private Function StartDocFallGroup(FileName)
Dim xlWB
Set xlWB = GetObject(FileName)
xlWB.Application.Visible = True
Set xlWB = Nothing
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GarHeard]

If the Excel file is not open, would I use CreatObject.
If so, how would I revise the function ?