接着求助ODBC透视表,高手救救我啊

hanhhan

有若干个sheet,每个sheet里面有一个透视表,现在要将每个透视表中的某个字段设成sheet名(将ATC_CODE字段设成sheet的名字),应该怎么办啊
我写的代码: 提示说 sname无效的标识,应该怎么写啊


Sub Macro3()
Dim sh As Worksheet
Dim sname As String


For Each sh In ThisWorkbook.Worksheets
    sname = sh.Name

   
    sh.Activate

    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = "ODBC;DSN=Oracle;UID=MI;;SERVER=MI;PWD=123456;"
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT PUMCH_ALL.FEATURE, PUMCH_ALL.OPD_IPD, PUMCH_ALL.MOLECULE, PUMCH_ALL.FORMULATION, PUMCH_ALL.PACKAGE, PUMCH_ALL.DEPARTMENT, PUMCH_ALL.DATA, PUMCH_ALL.YEAR, PUMCH_ALL.QTR, PUMCH_ALL.ATC_CODE" & Chr(13) & "" & Chr(10) & "FROM" _
        , " MI.PUMCH_ALL PUMCH_ALL" & Chr(13) & "" & Chr(10) & "WHERE (PUMCH_ALL.ATC_CODE=" & sname & ")")
        .CreatePivotTable TableDestination:=Range("A3"), TableName:="数据透视表1"
        
    End With

...............后面略

hanhhan

问题已解决......
因为是字符串类型,所以要用 '  ' 引起来
"WHERE (PUMCH_ALL.ATC_CODE=" & sname & ")")
改为:
"WHERE (PUMCH_ALL.ATC_CODE ='" & sname & "')")

hanhhan

基本功不扎实啊..................
得努力学习啊!!1