 Re: Export to Excel and open the worksheet
Jim wrote:[color=blue]
> This should really be simple, but I can't figure it out. I have some
> VB that exports a table in an Excel format ("C:\NewReport.xls"). After
> the export is done, I simply want to have some code that launches
> NewReport.xls in Excel.
>
> How?[/color]
This is more than what U asked for, but it does include code on how to
open a worksheet - and past data into it.
Private Sub btnGenerateData_Click()
'-------------------------------------------------------------
' Purpose : Inserts query data into existing Excel worksheet.
' Author :
' Phone:
' E-Mail:
' Notes : Must not use a 'WITH' statement when using Excel automation
object. MSKB 199219.
' Must have Reference to Microsoft Excel Library selected.
' Excel File:
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' Tuesday, October 21, 2003 RGO:
'================================================= ============
' End Code Header block
Dim MyApp As New Excel.Application
Dim dbe As DAO.DBEngine
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim sql$
Dim strSQL As String
Dim rst As Recordset
Dim DQ As String
On Error GoTo HandleErr
DQ = """"
DoCmd.Hourglass True
Set MyApp = New Excel.Application
Set dbe = CreateObject("DAO.DBEngine.36")
Set db = CurrentDb()
strSQL = "SELECT Rpt10.Maname, Count(Rpt10.Maname) AS [Total
Enroll], Count(Rpt10.Hosp20) AS Hosp20, Count(Rpt10.PCM10) AS PCM10,
Count(Rpt10.PCM20) AS PCM20, Count(Rpt10.Spec20) AS Spec20,
Count(Rpt10.Spec40) AS Spec40 " _
& "FROM Rpt10 " _
& "GROUP BY Rpt10.Maname " _
& "HAVING (((Rpt10.Maname) Is Not Null));"
Set rst = CurrentDb.OpenRecordset(strSQL) '
Pointer to Recordset
MyApp.Visible = True ' Make
Excel visible
MyApp.Workbooks.Open ("C:\Documents and Settings\A072411\My
Documents\Databases\Network Sizing\template.xls") ' Open an existing
workbook, Local testing
' MyApp.Workbooks.Open ("\\smb\labshare\databases\GenLab_3100.xls")
' Open an existing workbook
MyApp.Worksheets("Data").Activate ' Activate
the proper Worksheet
MyApp.Range("A1").Select '
Activate the proper cell
MyApp.ActiveCell.CopyFromRecordset rst ' Use
Excel's CopyFromRecordset Method
Set rst = Nothing
Set MyApp = Nothing
DoCmd.Hourglass False
DoCmd.Beep
ExitHere:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frm3100SampShts.btnGenerateData_Click"
Set MyApp = Nothing
MyApp.Quit
DoCmd.Hourglass False
End Select
End Sub
| 
