Problems with Range when using Excel Automation

[offie]

I have two lines that give me an error

1).Selection.AutoFill Destination:=Range("I2:I" & cells(rows.count,"I").end(xlup).row), Type:=xlFillDefault
2).ActiveChart.SetSourceData Source:=Range("'CnstSurvey'!$I:$N")

I have this code in 2 files, one that I play with and the other final one. Then 2nd line did not give an error when I was playing aroung with it. The 1st line did, not stating what the error was.

However, when I moved the code to my final file the 2nd one gave an error "Sub or Function not defined" and selected Range. So I'm assuming that the error with the 1st one is also range since these are the only 2 places that have Range in it.

I'm not sure why this is. All the other Excel Object work fine,

Here is my code:

Option Compare Database
Private Sub Command19_Click()
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Set dbs = CurrentDb

Dim strSQL As String, strQDF As String
strSQL = "SELECT ConstructionSurveyFYSearchGraphData.* FROM ConstructionSurveyFYSearchGraphData;"
strQDF = "CnstSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF, "C:\Users\Kyles\Desktop\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls"
dbs.QueryDefs.Delete strQDF

'Start Here, Makes Sure that File Name stays the same.
'100Survey
Dim strSQL2 As String, strQDF2 As String
strSQL2 = "SELECT [FiscalYear] & "" Q"" & [Quarter] AS FiscalYearAndQuarter, Count([100DocumentSurveryNumbers].Quarter) AS Count,Avg([100DocumentSurveryNumbers].Timeliness) " & _
        "AS AvgOfTimeliness, Avg([100DocumentSurveryNumbers].Quality) AS AvgOfQuality, Avg([100DocumentSurveryNumbers].Cost) " & _
        "AS AvgOfCost, Avg([100DocumentSurveryNumbers].Professionalism) AS AvgOfProfessionalism, " & _
        "Avg([100DocumentSurveryNumbers].Overall) AS AvgOfOverall FROM 100DocumentSurveryNumbers " & _
        "WHERE ((([100DocumentSurveryNumbers].FiscalYear) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![TextGraphFrom] " & _
        "And [Forms]![Navigation Form]![NavigationSubform].[Form]![TextGraphTo])) GROUP BY [FiscalYear] & "" Q"" & [Quarter];" & _
strQDF2 = "100PctSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF2, strSQL2)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF2, "C:\Users\Kyles\Desktop\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls"
dbs.QueryDefs.Delete strQDF2
'End Here
dbs.Close
'Edit Excel
Set xl = CreateObject("Excel.Application")
   xl.Workbooks.Open ("C:\Users\Kyles\Desktop\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls")
xl.Visible = True
With xl
    'Construction Survey
    .Sheets("CnstSurvey").Select
    .Range("I2").Select
    .ActiveCell.FormulaR1C1 = "=RC[-8] & "" ("" & RC[-7] & "")"""
    .Range("I2").Select
    .Selection.AutoFill Destination:=Range("I2:I16"), Type:=xlFillDefault
    .Columns("C:G").Select
    .Selection.Copy
    .Columns("J:N").Select
    .ActiveSheet.Paste
    .Columns("I:N").Select
    .ActiveSheet.Shapes.AddChart.Select
    .ActiveChart.ChartType = xlLineMarkers
    .ActiveChart.SetSourceData Source:=Range("'CnstSurvey'!$I:$N")
    '100PctSurvey
    .Sheets("100PctSurvey").Select
    .Range("I2").Select
    .ActiveCell.FormulaR1C1 = "=RC[-8] & "" ("" & RC[-7] & "")"""
    .Range("I2").Select
    .Selection.AutoFill Destination:=Range("I2:I16"), Type:=xlFillDefault
    .Columns("C:G").Select
    .Selection.Copy
    .Columns("J:N").Select
    .ActiveSheet.Paste
    .Columns("I:N").Select
    .ActiveSheet.Shapes.AddChart.Select
    .ActiveChart.ChartType = xlLineMarkers
    .ActiveChart.SetSourceData Source:=Range("'100PctSurvey'!$I$1:$N$8")
End With
xl.UserControl = True
Set dbs = Nothing
End Sub

Thanks!

[Rawb]

.Range needs to be used from inside a Sheet and you're trying to use it from inside a Workbook.

Try replacing "With xl" with "With xl.Sheets(1)".

[offie]

Thanks, after playing with it for a while I realzied that I never added the Excel References to my current db (I made this code in another one) and that was the issue (oops...).

".Selection.AutoFill Destination:=Range("I2:I16"), Type:=xlFillDefault" Works but
".Selection.AutoFill Destination:=Range("I2:I" & cells(rows.count,"I").end(xlup).row), Type:=xlFillDefault" Still does not work, does that format have to change?

edit: row.count,"I" should have been row.count, "A" It works perfect now