Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 18th, 2006, 11:31 PM
Registered User
Join Date: Jan 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default transferspreadsheet

I am trying to export a query upon exit from a form into excel. I have a macro set up to execute upon exit from the form. The transfespreadsheet action will work. However, I am exporting the same query with multiple variables. For instance, I will enter customer information into the form, and upon exit, would like to export a query based on the form to excel, either a new worksheet or simply adding to the end of the data in the main sheet. Any suggestions on how to accomplish this.

Thanks for any suggestions.

  #2 (permalink)  
Old January 19th, 2006, 01:58 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

You could do the whole thing through the Jet OLE DB Provider which gives you alot of coding flexibility. The following includes a Form event and a sub in a standard module.

Sub ExportoExcel(intFormData As Integer)

    Dim cnn As ADODB.Connection
    Dim rstExcelSheet As ADODB.Recordset
    Dim rstExportData As ADODB.Recordset
    Dim strSQL As String

    ' Open a connection to the Excel workbook using
    ' the Jet OLE DB Provider
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnn.ConnectionString = "Data Source=C:\Book1.xls;" & _
                           "Extended Properties=""Excel 8.0"""
    cnn.Mode = adModeReadWrite

    ' Open a recordset based on the target
    ' Excel Sheet (use one with or without pre-existing
    ' data). Field names in SELECT list map to column
    ' headings in Excel sheet.
    Set rstExcelSheet = New ADODB.Recordset
    rstExcelSheet.Open "SELECT [ID], [Field1], [Field2] " & _
                   "FROM [Sheet1$]", cnn, adOpenDynamic, adLockOptimistic

    ' Open recordset based on coded SQL string (your Query) that recieves its
    ' parameters from the Form.
    Set rstExportData = New ADODB.Recordset
    strSQL = "SELECT ID, Field1, Field2 FROM tblRecords WHERE ID = " & intFormData
    rstExportData.Open strSQL, CurrentProject.Connection

    ' Loop through data recordset and copy values into
    ' Excel recordset. This will add data to the bottom of
    ' the Excel Sheet if it already contains data.
    Do While Not rstExportData.EOF
        rstExcelSheet![ID] = rstExportData!ID
        rstExcelSheet![Field1] = rstExportData!Field1
        rstExcelSheet![Field2] = rstExportData!Field2

    rstExcelSheet.Update  '<--Excel Sheet populated here.

    Set rstExcelSheet = Nothing
    Set rstExportData = Nothing
    Set cnn = Nothing

End Sub
Just one of many routes from parameterized Jet queries to Excel.



  #3 (permalink)  
Old January 19th, 2006, 02:05 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Oops...forgot the Form event that calls the sub:

Private Sub Form_Unload(Cancel As Integer)
   'The Unload event allows you to capture the values
   'of Form controls before the Deactivate event
   'resets them to the first record in the Form's

   'Call Export sub, passing Form control values
   'as arguments.
   Call ExportoExcel(CInt(Me!txtID))
End Sub
  #4 (permalink)  
Old January 19th, 2006, 03:42 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Or you could build a "just-in-tim" query, as they say, with something like:

Private Sub Form_Unload(Cancel As Integer)

   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef

   Set db = CurrentDb()
   'Delete the existing JIT query;
   'trap the error if the query does not exist.
   On Error Resume Next
   db.QueryDefs.Delete ("qryFormData")
   On Error GoTo 0

   Set db = CurrentDb()
   Set qd = db.CreateQueryDef("qryFormData")
   qd.SQL = "SELECT ID, " _
              & "Field1, " _
              & "Field2 " _
              & "FROM tblRecords " _
              & "WHERE ID = " & Me.txtID  '<-- Use form control as parameter
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFormData", "C:\Book1.xls"
   db.QueryDefs.Delete ("qryFormData")
End Sub
Reason I prefer the code approach I posted first is it gives me more control over where my export data ends up. It works with an existing workbook, accounts for coulmn headings, appends data to pre-existing data, and lets you easily select the sheet to append to. And if I want to use a template I can just add a little more code, etc. The DoCmd methods like TransferSpreadsheet and OutputTo acQuery all seem to overwrite existing workbooks or data, or open new workbooks, etc. I could never get them to get my data where I wanted it, so I don't use them. Maybe someone else has had better luck with them.


Similar Threads
Thread Thread Starter Forum Replies Last Post
TransferSpreadsheet darkhalf Access VBA 2 February 15th, 2008 04:23 PM
TransferSpreadsheet stealthdevil Access VBA 2 March 16th, 2007 02:45 PM
TransferSpreadsheet kaleb0521 Access VBA 1 August 28th, 2006 12:59 PM
transferspreadsheet command Vince_421 Access VBA 2 May 3rd, 2006 11:29 AM
TransferSpreadsheet ajmil11 Access VBA 1 January 19th, 2006 02:07 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.