Export form data to a excel spreadsheet

[Spookarino]

Continuation from my previous help request.... I have a form which shows various calculations on screen (average, percentage, totals etc) from a table's data. I want to export all data from this form to a excel spreadsheet for further calculations and graphing... if anyone can help it would be appreciated.

 

[B827]

Do you do the calculations on the form or in a query? Sandy

 

[Spookarino]

Bit of both... Averages, record count, min's and max's are from a query, percentage calculations are done on the form... and some date parameters are take from another form

 

[smuckers]

Good evening Spookarino!

Below is a scenario for exporting values from an MS Access form into MS Excel. Please forgive the length, but this code does work.

Setup first:
1. MS Excel 2000
2. MS Access 2000

3. MS Access table named "tblShipIt"
3a. 5 table fields to the right of the primary field.
3b. Field names are Field1, Field2, etc...
3b. Fields 1-4 are text data type.
3c. Field 5 is numeric.

4. MS Access Form needed
4a. 5 text boxes, named "txtText1, txtText2, etc..."
4b. 1 commandbutton
4c. Name of commandbutton is "cmdTransfer_Excel"

5. MS Excel File
5a. File name is "XFer_A2K.xls"
5b. Path is "C:\Access\XFer_A2K.xls"

The key is "TransferSpreadsheet". With this command in MS Access 2000 you can also link your Access forms directly to Excel. As you change the forms values, supposedly your Excel file will automaticall update. I used a simple transfer of a data table for this example.

The code belows follows this sequence of events:
1. Deletes old values from transfer table, tblShipIt.
2. Updates the MS Access transfer table, tblShipIt
with the form values.
3. Transfer the MS Access table to the MS Excel file.

Here is the code:
**********************************************
[Option Compare Database

Private Sub cmdTransfer_Excel_Click()
' **** This Code transfers the form values into Excel.
Dim strShipIt As String
Dim strSQL As String
Dim strDelete_Ship_It As String

' *** Delete the old values in the tblShipIt table
DoCmd.SetWarnings False

strDelete_Ship_It = "DELETE tblShipIt.* FROM tblShipIt;"
DoCmd.RunSQL strDelete_Ship_It


' **** Setting up the SQL string to input values in tblShipIt transfer table ****

' Copy the entire string, the single and double quotes are murder!

' If the data type is numeric then just delete the set of single and double quotes.
' txtText5 is a numeric type, as defined by the tblShipIt table.

strSQL = "INSERT INTO tblShipIT ( " & _
"Field_1, Field_2, Field_3, Field_4, Field_5)" & _
"SELECT '" & txtText1 & "', '" & txtText2 & "', '" & txtText3 & "', " & _
"'" & txtText4 & "', " & txtText5 & ";"

DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' *** Now that the transfer table, tblShipIt has the form values
' Transfer the tblShipIt table to Excel directly with this command
' Make sure the file XFer_A2K.xls is closed.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblShipIt", "C:\Access\XFer_A2K.xls", True

End Sub]
*******************************************************

Good luck and let me know if this works for you.

Smuckers