Code Runs Perfectly... Every other time.

[offie]

I have no idea why, but it only runs every other time.

In my code:
Get file path
Create Excel file with spreadsheets from SQL's
Open and Edit that file

On the time that it doesn't work I get the error on this line:


.Selection.AutoFill Destination:=Range("I2:I" & cells(rows.Count, "A").End(xlUp).row), Type:=xlFillDefault
Which I have in three instances but the error is on the first instance.
The error states "Method 'Row' of object '_Global' failed"

Any Ideas?
Thanks!

Option Compare Database
Private Sub Command19_Click()
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Set dbs = CurrentDb
'Get file path from user
Dim F As Office.FileDialog
    Set F = Application.FileDialog(msoFileDialogFolderPicker)
    F.Show
Dim FolderPath As String
    FolderPath = F.SelectedItems.Item(1)
MsgBox FolderPath
Dim path As String
    path = FolderPath & "\Graphs_" & Format(Now(), "mm-dd-yy_hh-mm") & ".xls"
    
    
'Set SQL for all Graphs
Dim strSQL As String, strQDF As String
strSQL = "SELECT IIf(DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted]))=4," & _
        "DatePart(""yyyy"",[DateSubmitted])-1,DatePart(""yyyy"",[DateSubmitted])) & " & _
        """ Q"" & DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted])) AS FiscalYearAndQuarter, " & _
        "Count(DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted]))) AS [CountOf], " & _
        "Round(Avg(Val((IIf([Timeliness5]=True,5,"""") & IIf([Timeliness4]=True,4,"""") & " & _
        "IIf([Timeliness3]=True,3,"""") & IIf([Timeliness2]=True,2,"""") & " & _
        "IIf([Timeliness1]=True,1,"""")))),2) AS Timeliness, Round(Avg(Val((IIf([Quality5]=True,5,"""") & " & _
        "IIf([Quality4]=True,4,"""") & IIf([Quality3]=True,3,"""") & IIf([Quality2]=True,2,"""") & " & _
        "IIf([Quality1]=True,1,"""")))),2) AS Quality, Round(Avg(Val(IIf([Cost5]=True,5,"""") & " & _
        "IIf([Cost4]=True,4,"""") & IIf([Cost3]=True,3,"""") & IIf([Cost2]=True,2,"""") & " & _
        "IIf([Cost1]=True,1,""""))),2) AS Cost, Round(Avg(Val((IIf([Staff5]=True,5,"""") & " & _
        "IIf([Staff4]=True,4,"""") & IIf([Staff3]=True,3,"""") & IIf([Staff2]=True,2,"""") & " & _
        "IIf([Staff1]=True,1,"""")))),2) AS Professionalism, Round(Avg(Val((IIf([Overall5]=True,5,"""") & " & _
        "IIf([Overall4]=True,4,"""") & IIf([Overall3]=True,3,"""") & IIf([Overall2]=True,2,"""") & " & _
        "IIf([Overall1]=True,1,"""")))),2) AS Overall " & _
        "FROM ConstructionClientSurvery " & _
        "GROUP BY IIf(DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted]))=4,DatePart(""yyyy"",[DateSubmitted])-1,DatePart(""yyyy"",[DateSubmitted])) & "" Q"" & DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted])), IIf(DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted]))=4,DatePart(""yyyy"",[DateSubmitted])-1,DatePart(""yyyy"",[DateSubmitted])) " & _
        "HAVING (((IIf(DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted]))=4,DatePart(""yyyy"",[DateSubmitted])-1,DatePart(""yyyy"",[DateSubmitted]))) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![TextGraphFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![TextGraphTo])) " & _
        "ORDER BY IIf(DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted]))=4,DatePart(""yyyy"",[DateSubmitted])-1,DatePart(""yyyy"",[DateSubmitted])), Count(DatePart(""q"",DateAdd(""m"",-3,[DateSubmitted])));"
strQDF = "CnstSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF, path
dbs.QueryDefs.Delete strQDF

'Start Here
'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, path
dbs.QueryDefs.Delete strQDF2
'End Here
'Start Here
'ProgramSurvey
Dim strSQL3 As String, strQDF3 As String
strSQL3 = "SELECT [FiscalYear] & "" Q"" & [Quarter] AS FiscalYearAndQuarter, Count(ProgramPhaseSurveryNumbers.Quarter) AS CountOfQuarter, " & _
        "Round(Avg(ProgramPhaseSurveryNumbers.Timeliness),2) AS AvgOfTimeliness, " & _
        "Round(Avg(ProgramPhaseSurveryNumbers.Quality),2) AS AvgOfQuality, " & _
        "Round(Avg(ProgramPhaseSurveryNumbers.Cost),2) AS AvgOfCost, " & _
        "Round(Avg(ProgramPhaseSurveryNumbers.Professionalism),2) AS AvgOfProfessionalism,  Round(Avg(ProgramPhaseSurveryNumbers.Overall),2) AS AvgOfOverall " & _
        "FROM ProgramPhaseSurveryNumbers " & _
        "WHERE (((ProgramPhaseSurveryNumbers.FiscalYear) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![TextGraphFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![TextGraphTo])) " & _
        "GROUP BY [FiscalYear] & "" Q"" & [Quarter];"
strQDF3 = "ProgSurvey"
Set qdfTemp = dbs.CreateQueryDef(strQDF3, strSQL3)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF3, path
dbs.QueryDefs.Delete strQDF3
'End Here
dbs.Close
'Edit Excel
Set xl = CreateObject("Excel.Application")
   xl.Workbooks.Open (path)
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:I" & cells(rows.Count, "A").End(xlUp).row), Type:=xlFillDefault
    '.Selection.AutoFill Destination:=Range("I2:I23"), 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")
End With
With xl
    '100PctSurvey
    .Sheets("100PctSurvey").Select
    .Range("I2").Select
    .ActiveCell.FormulaR1C1 = "=RC[-8] & "" ("" & RC[-7] & "")"""
    .Range("I2").Select
    .Selection.AutoFill Destination:=Range("I2:I" & cells(rows.Count, "A").End(xlUp).row), Type:=xlFillDefault
    '.Selection.AutoFill Destination:=Range("I2:I23"), 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:$N")
End With
With xl
    'ProgSurvey
    .Sheets("ProgSurvey").Select
    .Range("I2").Select
    .ActiveCell.FormulaR1C1 = "=RC[-8] & "" ("" & RC[-7] & "")"""
    .Range("I2").Select
    .Selection.AutoFill Destination:=Range("I2:I" & cells(rows.Count, "A").End(xlUp).row), Type:=xlFillDefault
    '.Selection.AutoFill Destination:=Range("I2:I23"), 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("'ProgSurvey'!$I:$N")
End With
xl.UserControl = True
Set dbs = Nothing
End Sub

[RuralGuy]

I have not studied what you have but it is my experience that when something only works every other time, it depends on something that is only *saved* every other time.

[offie]

I don't specifically have anything that I save but I do TransferSpreadsheet's to the same file. In the file name I have the day and hour and minute, could that have something to do with it?

Does this mean that I should save after every transfer?

[RuralGuy]

Like I said, I haven't studied what you have but I'll bet it depends on the presence of something that only exists every other time. Maybe something at the beginning of the process depends on something that is done at the end of the process. Therefore, it is not available until the process is run at least once. I'm just guessing here. Can you break your process down into smaller chunks and see if you gain more consistency?

[offie]

Wow thanks! That was really helpful. It was my "Edit Excel" part that was causing the error. I didn't have to use CreateObject(Excel.Application) but just Excel.Application because it had already been created.

Thanks!

[RuralGuy]

Excellent! Glad we could help.