jeff06
06-27-2007, 08:45 AM
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
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