Hi todd,
Use a querytable object, setting the connection string property and SQL
statement property. Add the QT to the worksheet's QueryTables collection,
specifying the connection string, destination range and SQL string.
Then use the QT.Refresh method to run the query and drag the data back.
Here's an example:
Sub CreateQT()
Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
sConn = "ODBC;DSN=MS Access 97 Database;"
sConn = sConn & "DBQ=C:\Program Files\Microsoft Office\"
sConn = sConn & "Office\Samples\Northwind.mdb;"
sSql = "SELECT Customers.CustomerID, Customers.CompanyName "
sSql = sSql & "FROM `C:\Program Files\Microsoft
Office\Office\Samples\Northwind`"
sSql = sSql & ".Customers Customers "
sSql = sSql & "WHERE (Customers.City='Berlin') "
Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)
oQt.Refresh
End Sub
--
Kevin Ciccone
"(E-Mail Removed)" wrote:
> Hey
>
> I need to make the following-working SQL query run from a VBA module
> and then export all the reults to an Excel file/tab. I dont know if
> this involves allot of conversion or simply setting this SQL equal to
> a string and then running it... Any input what-so-ever is
> appreciated.
>
> Select T1."SLS_OUTLET_ID", T2."SLS_OUTLET_NM",
> T3."SLS_DIST_CHNL_TYPE_DESC", T1."CUST_ID", T4."ACCESS_AMT",
> T1."PPLAN_CD", T4."PPLAN_DESC", T1."ACCT_NUM", T1."MTN", T1."MKT_CD",
> T5."MKT_NAME", T6."BUS_NM", T1."NM_FIRST", T1."NM_LAST",
> T1."STATE_CD", T1."SLS_PRSN_ID", T7."SLS_PRSN_NM", T8."ACTIVITY_CD",
> T1."LINE_ACT_DT", T1."LINE_TERM_DT", T1."EQP_PROD_ID", T1."PROD_NM",
> T1."CNTRCT_TERM_DT", T1."PREPAID_IND", T1."DEACT_CHANGE_REAS_CD",
> T9."CHANGE_REAS_DESC", T10."RLTD_ACCT_ID", T11."AREA_CD",
> T11."REGION_CD", T12."PPLAN_SHARE_DESC"
> From SDW_PRD_ALLVM."REGION_V" T11,
> (((((((((("SDW_PRD_ALLVM"."CUST_ACCT_LINE_V" T1 LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."CUST_ACCT_V" T6
> On T1."SOR_ID" = T6."SOR_ID"
> And T1."CUST_ID" = T6."CUST_ID"
> And T1."ACCT_NUM" = T6."ACCT_NUM") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."DLY_LINE_ACTIVITY_V" T8
> On T1."CUST_ID" = T8."CUST_ID"
> And T1."CUST_LINE_SEQ_ID" = T8."CUST_LINE_SEQ_ID"
> And T1."SOR_ID" = T8."SOR_ID") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."OUTLET_V" T2
> On T1."SOR_ID" = T2."SOR_ID"
> And T1."SLS_OUTLET_ID" = T2."SLS_OUTLET_ID") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."SALES_DIST_CHANNEL_TYPE_V" T3
> On T2."SOR_ID" = T3."SOR_ID"
> And T2."SLS_DIST_CHNL_TYPE_CD" = T3."SLS_DIST_CHNL_TYPE_CD") LEFT
> OUTER JOIN "SDW_PRD_ALLVM"."PRICE_PLAN_V" T4
> On T1."PPLAN_CD" = T4."PPLAN_CD"
> And T1."SOR_ID" = T4."SOR_ID"
> And T1."PPLAN_MKT_CD" = T4."PPLAN_MKT_CD") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."MARKET_V" T5
> On T1."SOR_ID" = T5."SOR_ID"
> And T1."MKT_CD" = T5."MKT_CD") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."SALES_PERSON_V" T7
> On T1."SLS_PRSN_ID" = T7."SLS_PRSN_ID"
> And T1."SOR_ID" = T7."SOR_ID") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."RELATED_ACCT_V" T10
> On T6."RLTD_ACCT_ID" = T10."RLTD_ACCT_ID"
> And T6."SOR_ID" = T10."SOR_ID") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."PRICE_PLAN_SHARE_V" T12
> On T4."PPLAN_SHARE_CD" = T12."PPLAN_SHARE_CD") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."CHANGE_REASON_V" T9
> On T1."SOR_ID" = T9."SOR_ID"
> And T1."DEACT_CHANGE_REAS_CD" = T9."CHANGE_REAS_CD") LEFT OUTER JOIN
> "SDW_PRD_ALLVM"."REGION_HIST_V" T13
> On T5."AREA_CD" = T13."AREA_CD"
> And T5."REGION_CD" = T13."REGION_CD"
> Where T13."REGION_CD" = T11."REGION_CD"
> And T13."AREA_CD" = T11."AREA_CD"
> And T1."LINE_ACT_DT" Between {d '2007-08-01'}
> And {d '2007-08-31'}
> And T10."RLTD_ACCT_ID" In ( sel group_id from
> SDW_PRD_QMTBLS.CARSTNSLSOPS_NAT_GID_tN)
> And T8."ACTIVITY_CD" In ('ac')
>
>
|
Similar Threads
