Private Sub Command75_Click()
[COLOR=SeaGreen]'Step 1: Declare your variables[/COLOR]
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
[COLOR=Red]Dim strSQL As String[/COLOR]
Dim i As Integer
strSQL = "SELECT DivingActivitiesAir1T.ProjectNumber, DivingActivitiesAir1T.DPRDate, DivingActivitiesAir1T.DPRNumber, DivingActivitiesAir1T.DiveNr, " & _
"Format([From],'Short Time') AS [Start time], Format([To],'Short Time') AS [End time], Sum(TimeValue([Duration])*1440) AS [Duration (min)], " & _
"Format([From]-1-[To],'Short Time') AS Duration, DivingActivitiesAir1T.Acitivity AS Activity, DivingActivitiesAir1T.Details, DivingActivitiesAir1T.Code, " & _
"DivingActivitiesAir1T.DPRCode, DivingActivitiesAir1T.Diver1, DivingActivitiesAir1T.DiveTime1M AS DiveTime1, DivingActivitiesAir1T.Depth1, " & _
"DivingActivitiesAir1T.DecoTime1, DivingActivitiesAir1T.Diver2, DivingActivitiesAir1T.DiveTime2M AS DiveTime2, DivingActivitiesAir1T.Depth2, " & _
"DivingActivitiesAir1T.DecoTime2, DivingActivitiesAir1T.Diver3, DivingActivitiesAir1T.DiveTime3M AS DiveTime3, DivingActivitiesAir1T.Depth3, " & _
"DivingActivitiesAir1T.DecoTime3, DivingActivitiesAir1T.StbDiver, DivingActivitiesAir1T.DiveTimeStM AS DiveTimeStb, DivingActivitiesAir1T.DepthStb, " & _
"DivingActivitiesAir1T.DecoTimeStb, DivingActivitiesAir1T.SOLnr, DivingActivitiesAir1T.Gas, DivingActivitiesAir1T.StorageDepthBell, " & _
"DivingActivitiesAir1T.SignWaveHeight, DivingActivitiesAir1T.Location, DivingActivitiesAir1T.SOW, DivingActivitiesAir1T.DeckDive AS [Deck/Dive], " & _
"DivingActivitiesAir1T.State, DivingActivitiesAir1T.NameOrCode FROM DivingActivitiesAir1T WHERE (((DivingActivitiesAir1T.ProjectNumber) = " & _
[Forms]![Airdive1ExportF]![projectnumber] & ")) GROUP BY DivingActivitiesAir1T.ProjectNumber, DivingActivitiesAir1T.DPRDate, " & _
"DivingActivitiesAir1T.DPRNumber, DivingActivitiesAir1T.DiveNr, DivingActivitiesAir1T.Acitivity, DivingActivitiesAir1T.Details, DivingActivitiesAir1T.Code, " & _
"DivingActivitiesAir1T.DPRCode, DivingActivitiesAir1T.Diver1, DivingActivitiesAir1T.DiveTime1M, DivingActivitiesAir1T.Depth1, DivingActivitiesAir1T.DecoTime1, " & _
"DivingActivitiesAir1T.Diver2, DivingActivitiesAir1T.DiveTime2M, DivingActivitiesAir1T.Depth2, DivingActivitiesAir1T.DecoTime2, DivingActivitiesAir1T.Diver3, " & _
"DivingActivitiesAir1T.DiveTime3M, DivingActivitiesAir1T.Depth3, DivingActivitiesAir1T.DecoTime3, DivingActivitiesAir1T.StbDiver, " & _
"DivingActivitiesAir1T.DiveTimeStM, DivingActivitiesAir1T.DepthStb, DivingActivitiesAir1T.DecoTimeStb, DivingActivitiesAir1T.SOLnr, " & _
"DivingActivitiesAir1T.Gas, DivingActivitiesAir1T.StorageDepthBell, DivingActivitiesAir1T.SignWaveHeight, DivingActivitiesAir1T.Location, " & _
"DivingActivitiesAir1T.SOW, DivingActivitiesAir1T.DeckDive, DivingActivitiesAir1T.State, DivingActivitiesAir1T.NameOrCode, DivingActivitiesAir1T.From, " & _
"DivingActivitiesAir1T.To ORDER BY DivingActivitiesAir1T.DPRDate, DivingActivitiesAir1T.From;"
[COLOR=Green]'Step 2: Identify the database and query[/COLOR]
Set MyDatabase = CurrentDb
[COLOR=Red]On Error Resume Next[/COLOR]
[COLOR=Red]With MyDatabase
.QueryDefs.Delete ("tmpOutQry")
Set MyQueryDef = .CreateQueryDef("tmpOutQry", strSQL)
.Close
End With[/COLOR]
[COLOR=Green]'Step 3: Open the query[/COLOR]
Set MyRecordset = MyQueryDef.OpenRecordset
[COLOR=Green]'Step 4: Clear previous contents[/COLOR]
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add
.Sheets("Sheet1").Select
[COLOR=Green]'Step 5: Copy the recordset to Excel[/COLOR]
.ActiveSheet.Range("A2").CopyFromRecordset MyRecordset
[COLOR=Green]'Step 6: Add column heading names to the spreadsheet[/COLOR]
For i = 1 To MyRecordset.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
End Sub