Hello Ed White,
Thanks for contacting Microsoft Managed Newsgroup Support!
I can reproduce the issue with the codes you post. In fact, to create a
PivotTable with an embedded connection, there are two places need
modifications in the codes,
1.We need to create the PivotCache XlPivotTableSourceType.xlExternal
instead of XlPivotTableSourceType.xlDataBase. Actually, from this MSDN
document
http://msdn.microsoft.com/en-us/library/bb238847.aspx, we can know
that ,
The SourceType can be one of these xlPivotTableSourceType constants:
xlConsolidation, xlDatabase, or xlExternal.(xlPivotTable and xlScenario are
not supported when creating a PivotCache using this method). And the The
SourceData argument is required if SourceType isn't xlExternal. It can be a
Range object (when SourceType is either xlConsolidation or xlDatabase) or
an Excel Workbook Connection object (when SourceType is xlExternal). In our
case, our data source is an Workbook connection, so we need to use the
xlExternal.
2.The first parameter of CreatePivotTable should look like "'[RLSP
anal.xlsm]Sheet7'!R7C1" instead of "[RLSP anal.xlsm]Sheet7!R7C1". The
latter one cannot be understood by Excel object model.
I have tested the following codes on my side and it works fine. Please let
me know if you have any future questions or concerns on this issue. And I
will try my best to help.
Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Startup
Globals.ThisWorkbook.Connections.Add("IntraDay_Pivot", _
"", _
"OLEDB;Provider=SQLOLEDB.1;Password=iLoveyan888;Persist Security
Info=True;User ID=sa;Data Source=.\SQLEXPRESS;Use Procedure for Prepar" +
"e=1;Auto Translate=True;Packet Size=4096;Workstation ID=JIZHOU;Use
Encryption for Data=False;Tag with column collation when poss" +
"ible=False;Initial Catalog=Test", _
"""Test"".""dbo"".""lanes""", _
3)
Dim pt As PivotTable, pc As PivotCache
Me.Columns.Delete()
pc =
Globals.ThisWorkbook.PivotCaches.Create(XlPivotTableSourceType.xlExternal,
Globals.ThisWorkbook.Connections("IntraDay_Pivot"),
Version:=XlPivotTableVersionList.xlPivotTableVersion12)
pt = pc.CreatePivotTable("'[RLSP anal.xlsm]Sheet7'!R7C1")
End Sub
Good day!
Best regards,
Ji Zhou (
[email protected], remove 'online.')
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
