请教各位关于vba连接外部数据源（另一个excel文件的问题）急，请帮忙

corpse

我写了一个sub 作用的打开一个dbwk指向的文件，然后再在piwk指向的文件中建立透视表 中间连接了dbwk指向的excel文件

可是老提示出错 就在标红色字体的地方

Sub setup_PivotTables()
Dim piwk As Workbook
Dim dbwk As Workbook
Dim ini
Dim opcount
Dim dbcount
Dim becount
ini = 1
opcount = 1
dbcount = 2
becount = 0
Application.DisplayAlerts = False
If Dir(ThisWorkbook.Path & "\" & "PivotTables.xls") = "" Then
   Set piwk = Workbooks.Add
   'piwk.IsAddin = True
   piwk.Sheets(1).Delete
   piwk.Sheets(1).Delete
   piwk.SaveAs Filename:=ThisWorkbook.Path & "\" & "PivotTables.xls", FileFormat _
        :=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
   ini = 0
   opcount = 2
Else
   Set piwk = Workbooks.Open(ThisWorkbook.Path & "\" & "PivotTables.xls", 2, False)
   'piwk.IsAddin = True
End If
Set dbwk = Workbooks.Open(ThisWorkbook.Path & "\" & "database.table", 2, False)
dbwk.SaveAs Filename:=ThisWorkbook.Path & "\" & "database.xls", FileFormat _
        :=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
'dbwk.IsAddin = True
   Do While dbcount <= dbwk.Sheets.Count
      Do While opcount <= piwk.Sheets.Count
         If piwk.Sheets(opcount).Name = dbwk.Sheets(dbcount).Name Then
            becount = 1
            Exit Do
         End If
         dbcount = dbcount + 1
      Loop
      If becount = 0 And ini = 0 Then
        piwk.Sheets.Add after:=piwk.Sheets(piwk.Sheets.Count)
        piwk.Sheets(piwk.Sheets.Count).Name = dbwk.Sheets(dbcount).Name
        piwk.Connections.Add "database", "", Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\mp\bom cost\database.xls;Mode=Share Deny Write;Ext" _
        , _
        "ended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Eng" _
        , _
        "ine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB" _
        , _
        ":New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on" _
        , _
        " Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
        ), Array("Sheet2$"), 3
        piwk.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        piwk.Connections("database"), Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="D:\mp\bom cost]t1.xls" & j & "!R1C4", TableName:="数据透视表1", _
        DefaultVersion:=xlPivotTableVersion12
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("物料组\分群码")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name).PivotFields("MTyp")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name).AddDataField ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name _
        ).PivotFields("IV/PO价格"), "求和项:IV/PO价格", xlSum
    ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name).AddDataField ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name _
        ).PivotFields("月份"), "计数项:月份", xlCount
    With ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name).PivotFields("计数项:月份")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name).PivotFields("分类")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(piwk.Sheets(piwk.Sheets.Count).Name).PivotFields("小类")
        .Orientation = xlRowField
        .Position = 2
    End With
      Else
     
     
     
     
     
     
        
      End If
   opcount = opcount + 1
   dbcount = 1
   becount = 0
   Loop

piwk.Close savechanges:=False
dbwk.Close savechanges:=True
MsgBox "ok"

End Sub

BIN_YANG168

连接外部数据源直接导入数据再创建数据透视表不可以吗？

清水一

请问楼上的，连接外部数据源时，数据源更新后，数据透视编刷新显示找不到数据源中的某张表，但我看了下是存在的，这是为什么？

还有导入数据和新建数据库查询的区别是什么