How to use ActiveSheet.Name in sql query

[ServerDude]

Hi Guys,

I have a script that is working well i am just trying to make it a little more efficent. The workbook is made of about 10 sheets. Each sheet has the name of business area. I am tring to set the criteria in the sql query to filter against this business area instead of filtering at the pivot table report.

The query exectutes, but instead of getting the work sheet name in the query i am getting 'ActiveSheet.Name'. When i use the ActiveSheet.Name in any other area of vba it is working.

What am i doing wrong? I have highlighted in red the area i am having difficulty with.

Sheets(Sheets(x).Name).Range("AB5").Select
Sheets(Sheets(x).Name).PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _
"SELECT uthead.uh_account, uthead.uh_cashexp, uthead.uh_cash, uthead.uh_costprd, uthead.uh_cupchg, uthead.uh_date, uthead.uh_mref, uthead.uh_refund, uthead.uh_test, uthead.uh_visits, uthead.uh_oref, sn" _
, _
"ame.sn_name, uthead.uh_totstar, uthead.uh_totfin, umach.um_type" & Chr(13) & "" & Chr(10) & "FROM sname sname, umach umach, uthead uthead" & Chr(13) & "" & Chr(10) & "WHERE sname.sn_account = uthead.uh_account AND uthead.uh_mref = umach.um_ref AND ((uthead" _
, _
".uh_date>={d '" & Date1 & "'} And uthead.uh_date<={d '" & date2 & "'}) AND (uthead.uh_account<>'CYM001') AND (uthead.uh_oref=' ActiveSheet.Name '))" _
), Connection:=Array(Array( _
"ODBC;DSN=Vendman;UID=;;SourceDB=H:\Opera II V6\Data\Cymraeg\COMP_C.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machi" _
), Array("ne;Null=Yes;Deleted=Yes;"))

Any help appriciated.

p.s. i am using Excel 2003

 

[Richard Schollar]

Hi

The following is a code fragment only (so you need to incorporate it within your code):

AND (uthead.uh_account<>'CYM001') AND (uthead.uh_oref='" &  ActiveSheet.Name  & "'))" _

 

[ServerDude]

Hi Richard,

Works a treat, Many Thanks