Exporting table to excel template with filter

[samg2013]

Hi everyone,

First post so go easy.

Originally, I was attempting to get the output of a query in access to export to an excel template. This beat me.

So I decided to try and use output the table the query referenced and filter it in VBA using a combo box. This is beating me now.

This is my code, and its driving me CRAZY!!

Private Sub Command69_Click()
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Const sFileNameTemplate As String = "K:\template.xls"
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "SELECT * FROM table1 " & _
        "WHERE [Assigned To] = " & [Forms]![form1].[name] & ";"
With oExcel
    .Visible = True
               'Create new workbook from the template file
                Set WB = .Workbooks.Add(sFileNameTemplate)
                        With WB
                             Set WS = WB.Worksheets("Sheet1")
                             With WS
                                      objRs.Open sSQL, objConn, adOpenStatic, adLockReadOnly
 
                                      Set rng = .range("A1") 'Starting point of the data range
                                      rng.CopyFromRecordset objRs
                                      objRs.Close
                             End With
 
                        End With
    '.Quit
End With
 
Set objConn = Nothing
Set objRs = Nothing
End Sub

The table is 'table1' and the combo box is called 'name' on 'form1'.


Any help or pointers would be amazing.

Thanks

 

[samg2013]

I should mention that at the moment this code opens my template succesfully but then leaves an error message in access saying ' Run-time error '-2147217904 (80040e10)': No value given for one or more required parameters.'