Exporting multiple queries to formatted excel template

[kadishzm]

Hello All, Thanks for the help so far. I have been able to get a file working that allows dynamic field names to be selected by the user and run through a query. The last piece of the puzzle I am dealing with is exporting the query results to Excel templates for formatting. I have attached my code below. Is there anything I can do to direct the results to the first worksheet in a formatted excel doc (see attached) that is driven by a macro? Can you spot any flaws in my querydef /loops? It does what I want for the most part but I want to improve and learn how to do this the right way for future reference.

Option Compare Database
Option Explicit
 
Private Sub btn_run_Click()
 
Dim Answer As String
Dim MyNote As String
Dim MyNote2 As String
Dim MyNote3 As String
Dim Complete As String
Dim Cancel As String
Dim Detail As String
 
MyNote = "This process may take several minutes to complete.  Would you like to proceed?"
MyNote2 = "Process complete.  Thanks for your patience."
MyNote3 = "Process cancelled."
 
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Note")
 
If Answer = vbNo Then
    Cancel = MsgBox(MyNote3, vbInformation, "Process Cancelled")
    Exit Sub
 
Else
 
Dim DB As Database
Dim Carrier As Recordset
Dim setup As Recordset
Dim QD As QueryDef
Dim qd2 As QueryDef
 
Dim QDname As String
Dim SCAC As String
Dim Splitter As String
Dim StrSQL As String
Dim ctl As Control
Dim StrName As String
Dim StrSQLFinal As String
Dim intNumSelected As Integer
Dim Templatecopy As String
Dim FileDest As String
Dim Outputfile As String
Dim SQL_SCACs As String
Dim SelectedSplit As String
Dim strMakeQD
Dim Count_Records As Long
Dim RunDate As Date
Dim i As Integer
 
 
Set DB = CurrentDb()
 
For Each ctl In Me.Controls
  If ctl.ControlType = acCheckBox Then      'Control is a Checkbox
    If ctl.Value Then                       'It is Selected?
      intNumSelected = intNumSelected + 1
        StrName = Replace(ctl.Name, "ck", "")
          StrSQL = StrSQL & "[" & StrName & "], "
    End If
  End If
Next
 
 
If intNumSelected = 0 Then Exit Sub         'No Column(s) selected
 
'Link to Template and Output files
Templatecopy = Me.txtTemplate
Outputfile = Me.txtFilename
 
 
'Create the SQL statement for checkboxed headers
StrSQL = Left(StrSQL, Len(StrSQL) - 2)
StrSQLFinal = "SELECT " & StrSQL & " FROM data WHERE " & Me.txtSplitfield & " = "
 
'Gathers SCAC codes present in the input file
SQL_SCACs = "SELECT " & Me.txtSplitfield & " as Splitter FROM data GROUP BY " & Me.txtSplitfield & " ORDER BY " & Me.txtSplitfield & ";"
Set Carrier = DB.OpenRecordset(SQL_SCACs)
 
Set QD = Nothing
 
Do
 
SelectedSplit = Carrier!Splitter
QDname = "qd_" & SelectedSplit
strMakeQD = StrSQLFinal & Chr(34) & SelectedSplit & Chr(34) & ";"
 
For Each QD In DB.QueryDefs
        If QD.Name = QDname Then
            DB.QueryDefs.Delete QDname
        End If
 
        Next
 
        DB.CreateQueryDef QDname, strMakeQD
 
template = "L:\Operations\FOS\DBs\Schedule A\Outputs\" & SelectedSplit & "-" & Outputfile
 
            FileCopy Templatecopy, template
 
            Count_Records = DCount("SCAC", QDname)
            If (Count_Records > 0) Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QDname, template, False, ""
                i = i + 1
                Call update_excel_loop(template, i)
                i = 0
            End If
            'DB.QueryDefs.Delete QDname
 
Carrier.MoveNext
 
Loop Until Carrier.EOF
 
 
End If
 
Complete = MsgBox(MyNote2, vbInformation, "Process Complete")
 
End Sub

Private Sub cboRunID2_Change()
 
 
Dim db2 As Database
Dim PopFields As Recordset
Dim DataPop As String
 
Set db2 = CurrentDb()
 
DataPop = "SELECT [RunID], [Template], [filename], [split_field], [LaneID], [RefNum], [OFacility], [OCity], [OState], [OZip], [ORegion], [OCountry], [DFacility], [DCity], [DState], [DZip], [DRegion], [DCountry], [Distance], [AnnualVol], [CarName], [CarRef], [Mode], [Equip], [EquipSize], [OrBid], [OrRateType], [OrMinChge], [OrTotalRate], [AssetType], [AllocAmount], [RateGuideSeq], [AwardType] from tbl_setup_detail where RunId = """ & Me.cboRunID2 & """"
Set PopFields = db2.OpenRecordset(DataPop)
 
Me.txtTemplate.Value = PopFields!template
Me.txtFilename.Value = PopFields!filename
Me.txtSplitfield.Value = PopFields!split_field
Me.ckLaneID = PopFields!LaneID
Me.CkRefNum = PopFields!RefNum
Me.CkOfacility = PopFields!OFacility
Me.CkOCity = PopFields!OCity
Me.CkOState = PopFields!OState
Me.ckOZip = PopFields!OZip
Me.ckOregion = PopFields!ORegion
Me.ckOCountry = PopFields!OCountry
Me.ckDFacility = PopFields!DFacility
Me.ckDCity = PopFields!DCity
Me.ckDState = PopFields!DState
Me.ckDZip = PopFields!DZip
Me.ckDRegion = PopFields!DRegion
Me.ckDCountry = PopFields!DCountry
Me.ckDistance = PopFields!Distance
Me.ckAnnualVol = PopFields!AnnualVol
Me.ckCarName = PopFields!CarName
Me.ckSCAC = PopFields!CarRef
Me.ckTransMode = PopFields!Mode
Me.ckEquip = PopFields!Equip
Me.ckEquipSize = PopFields!EquipSize
Me.ckOrBid = PopFields!OrBid
Me.ckRateType = PopFields!OrRateType
Me.ckMinChge = PopFields!OrMinChge
Me.ckTotalRate = PopFields!OrTotalRate
Me.ckAssetType = PopFields!AssetType
Me.ckAllocAmount = PopFields!AllocAmount
Me.ckRouteGuideSeq = PopFields!RateGuideSeq
Me.ckAwardType = PopFields!AwardType
 
End Sub

Private Sub update_excel_loop(template, i)
'declare variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
 
'excel application stuff
If i = 0 Then
    Set xlApp = New Excel.Application
Else
    Set xlApp = Excel.Application
End If
 
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(template)
 
'run the macro
xlApp.Run "Macro1"
 
'save file
xlBook.Save
 
'done
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
 
 

The main issue I am having is that the export creates a new worksheet instead of pasting on page 1 and calling the formatting.