ExcelTip.Net留存知识帖 ---【注：附件之前被网盘供应商清空后，现已修复-现已修复-现已修复为本地下载！】
现在位置：首页 > E问E答 > Excel VBA > 为什么录制了基于外部数据源创建数据透视表的代码却不能照搬来使用？

为什么录制了基于外部数据源创建数据透视表的代码却不能照搬来使用？

作者：绿色风 分类： 时间：2022-08-17 浏览：120
楼主
水星钓鱼		 Q：如下图所示某公司2011与2010年两年的财务清单，希望通过编写代码来创建数据透视表，录制宏后的代码如下：

 
  1. Sub 宏1()
  2.     With ActiveWorkbook.Connections("vba+SQL").OLEDBConnection
  3.         .BackgroundQuery = True
  4.         .CommandText = Array( _
  5.         "select ""2011年"" as 年度,* from [2011年$a:f] union all select ""2010年"",* from [2010年$a:f]" _
  6.         )
  7.         .CommandType = xlCmdTable
  8.         .Connection = Array( _
  9.         "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\Administrator\桌面\vba+SQL.xlsm;" _
  10.         , _
  11.         "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database " _
  12.         , _
  13.         "Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk" _
  14.         , _
  15.         " Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet O" _
  16.         , _
  17.         "LEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Com" _
  18.         , "plex Data=False")
  19.         .RefreshOnFileOpen = False
  20.         .SavePassword = False
  21.         .SourceConnectionFile = ""
  22.         .SourceDataFile = "C:\Documents and Settings\Administrator\桌面\vba+SQL.xlsm"
  23.         .ServerCredentialsMethod = xlCredentialsMethodIntegrated
  24.         .AlwaysUseConnectionFile = False
  25.         .ServerFillColor = False
  26.         .ServerFontStyle = False
  27.         .ServerNumberFormat = False
  28.         .ServerTextColor = False
  29.     End With
  30.     With ActiveWorkbook.Connections("vba+SQL")
  31.         .Name = "vba+SQL"
  32.         .Description = ""
  33.     End With
  34.     Sheets.Add
  35.     Workbooks("vba+SQL.xlsm").Connections.Add "vba+SQL", "", Array( _
  36.         "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\Administrator\桌面\vba+SQL.xlsm;" _
  37.         , _
  38.         "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database " _
  39.         , _
  40.         "Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk" _
  41.         , _
  42.         " Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet O" _
  43.         , _
  44.         "LEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Com" _
  45.         , "plex Data=False"), Array( _
  46.         "select ""2011年"" as 年度,* from [2011年$a:f] union all select ""2010年"",* from [2010年$a:f]" _
  47.         ), 3
  48.     ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
  49.         ActiveWorkbook.Connections("vba+SQL"), Version:=xlPivotTableVersion14). _
  50.         CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="数据透视表2", _
  51.         DefaultVersion:=xlPivotTableVersion14
  52. End Sub
现在修改宏代码为如下：
  1. Sub xyf()
  2.     Workbooks("vba+SQL.xlsm").Connections.Add "vba+SQL", "", Array( _
  3.         "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\Administrator\桌面\vba+SQL.xlsm;" _
  4.         , _
  5.         "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database " _
  6.         , _
  7.         "Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk" _
  8.         , _
  9.         " Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet O" _
  10.         , _
  11.         "LEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Com" _
  12.         , "plex Data=False"), Array( _
  13.         "select ""2011年"" as 年度,* from [2011年$a:f] union all select ""2010年"",* from [2010年$a:f]" _
  14.         ), 3
  15.     ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
  16.         ActiveWorkbook.Connections("vba+SQL"), Version:=xlPivotTableVersion14). _
  17.         CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="数据透视表2", _
  18.         DefaultVersion:=xlPivotTableVersion14
  19.     ActiveWorkbook.Connections("vba+SQL").Delete
  20. End Sub
却运行不了了，为什么呢？
A：只要将基于PivotCache创建的Pivottable的Destination设置为空即可。
修改后的代码如下：
  1. Sub xyf()
  2.     Workbooks("vba+SQL.xlsm").Connections.Add "vba+SQL", "", Array( _
  3.         "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\Administrator\桌面\vba+SQL.xlsm;" _
  4.         , _
  5.         "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database " _
  6.         , _
  7.         "Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk" _
  8.         , _
  9.         " Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet O" _
  10.         , _
  11.         "LEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Com" _
  12.         , "plex Data=False"), Array( _
  13.         "select ""2011年"" as 年度,* from [2011年$a:f] union all select ""2010年"",* from [2010年$a:f]" _
  14.         ), 3
  15.     ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
  16.         ActiveWorkbook.Connections("vba+SQL"), Version:=xlPivotTableVersion14). _
  17.         CreatePivotTable TableDestination:="", TableName:="数据透视表2", _
  18.         DefaultVersion:=xlPivotTableVersion14
  19.     ActiveWorkbook.Connections("vba+SQL").Delete
  20. End Sub

vba+SQL.rar


2楼
janne.71		 谢谢老师，实际工作太
需要了
3楼
亡者天下		 看得眼花缭乱啊
4楼
nalnait		 学习   谢谢

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素， 经与ExcelTip.Net站长Apolloh商议并征得其同意， 现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示， 供有需要的人士查询使用，也慰缅曾经的论坛时代。 所示各个帖子的原作者如对版权有异议， 可与本人沟通提出，或于本站点留言，我们会尽快处理。 在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一

猜您喜欢
评论列表
搜索

鄂ICP备2021011689号-2

powered by emlog pro Theme By 蓝叶

sitemap