transferspreadsheet

ajmil11 · January 18th, 2006, 11:31 PM

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.

Bob Bedell · January 19th, 2006, 01:58 AM

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.

Code:

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
    cnn.Open

    ' 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.AddNew
        rstExcelSheet![ID] = rstExportData!ID
        rstExcelSheet![Field1] = rstExportData!Field1
        rstExcelSheet![Field2] = rstExportData!Field2
        rstExportData.MoveNext
    Loop

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

    rstExcelSheet.Close
    Set rstExcelSheet = Nothing
    rstExportData.Close
    Set rstExportData = Nothing
    cnn.Close
    Set cnn = Nothing

End Sub

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

HTH,

Bob

Bob Bedell · January 19th, 2006, 02:05 AM

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

Code:

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
   'recordsource.

   'Call Export sub, passing Form control values
   'as arguments.
   Call ExportoExcel(CInt(Me!txtID))
End Sub

Bob Bedell · January 19th, 2006, 03:42 AM

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

Code:

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.

Bob