Results did not write to Excel timely.

[jeff06]

After I hit OK button the code can run, But the result does not write to Excel unless I close tee form.
I want the results to be written to Excel right after the program finishing running. How can I acheive this goal.
Thanks



Private Sub CommandButtonOK_Click()
Call Query
End Sub
Private Sub CommandButtonCancel_Click()
Unload Me
End Sub
Private Sub Query()
' Declare the QueryTable object
Dim qt As QueryTable

' Set up the SQL Statement
sqlstring = " select sum(FundingBalanceAtDefaultedDate) as totalDefaultedD, "
sqlstring = sqlstring & " sum(fundedamount) as TotalFundedD,"
sqlstring = sqlstring & " sum(FundingBalanceAtDefaultedDate)/ sum(fundedamount) as RateD,"
sqlstring = sqlstring & " sum(isdefaulted) as totalDefauledN, "
sqlstring = sqlstring & " count(*) as totalfundedD, "
sqlstring = sqlstring & " 1.0*sum(isdefaulted)/count(*) as rateN"
sqlstring = sqlstring & " from "
sqlstring = sqlstring & " ( "
sqlstring = sqlstring & " Select *, "
sqlstring = sqlstring & " case when FundingBalanceAtDefaultedDate>0 then 1 else 0 end as isdefaulted"
sqlstring = sqlstring & " from rpt_contractdetails"
sqlstring = sqlstring & " where firstfundingdate>'1/1/2004' and firstfundingdate<'1/1/2007' "
sqlstring = sqlstring & " and fundedamount>0 "
sqlstring = sqlstring & " ) as a"

' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
"ODBC;DRIVER=SQL Server;SERVER=SGA007;UID=; APP=Microsoft Office XP;WSID=GA0236B;DATABASE=advancemetoday"


' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
End With

End Sub

[unmarkedhelicopter]

This says it does, try stepping through the code and see what happens when you get to the end.

[RichardSchollar]

Forms are modal by default, so however you invoke the form, you need it to be modeless so that Excel can be updated before the form is closed (anyway, this is what I presume).

Richard

[jeff06]

Thanks