472,374 Members | 1,457 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

Export to Excel and open the worksheet

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.


Nov 13 '05 #1
1 2783

Jim wrote:
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.


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
Exit Sub
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frm3100SampShts.btnGenerateData_Click"
Set MyApp = Nothing
DoCmd.Hourglass False
End Select

End Sub

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Anthony Cuttitta Jr. | last post by:
I'm working on some procedures where Access queries are exported to Excel, and then later on, those same workbooks are openned, and I need to target a specific original sheet. Sometimes there will...
by: Hans [DiaGraphIT] | last post by:
Hi! I want to export a dataset to an excel file. I found following code on the net... ( http://www.codeproject.com/csharp/Export.asp ) Excel.ApplicationClass excel = new ApplicationClass();...
by: DC Gringo | last post by:
I have a simple button that should open another window and export a datagrid to an Excel file. I'm getting: "Name 'window' is not declared." What do I need to declare or import? <INPUT...
by: Agnes | last post by:
Dim dsExcelExport As New System.Data.DataSet Dim daExcelExport As New System.Data.SqlClient.SqlDataAdapter Dim Excel As New Excel.Application Dim strExcelFile As String Dim strFileName As...
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
by: devolper | last post by:
I am devolping a web application which which .net reports to a excel work book .each report in one excel sheet.i am using the following code.......... Dim objDestinationExcel As New...
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
by: jmarcrum | last post by:
I want to export a report (that contains two separate queries, 1. Current year data, and 2. split-year data) from access into excel, but everytime I run my code and export the data to excel, it looks...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.