How do I create a new workbookconnection for an existing pivtotable?

Question

0.00/5 (No votes)

See more:

I've got an existing pivot table that performs many calculations within various columns that uses an external data source (.csv file). I would like to programmatically change the source file to another .csv file. I know that I need to create a WorkbookConnection, but I haven't had any luck yet finding an example on the web.

Any help you can provide would be appreciated.

What I have tried:

Have not found an example yet.

Posted 5-Jul-23 2:22am

BladesAero

Updated 13-Jul-23 6:17am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andre Oosthuizen · Answer 1 · 2023-07-06T22:49:00

Solution 1

Quote:
I know that I need to create a WorkbookConnection, but I haven't had any luck yet finding an example on the web.

Not sure what you searched for, I found tons of examples when I searched for vb9 create a WorkbookConnection to a csv file [^]

One such an example is How to dynamically change a PivotTable's source file location in VB.NET?[^]

Microsoft also explains the WorkbookConnection object [^] in detail.

Posted 6-Jul-23 22:49pm

Andre Oosthuizen

Comments

BladesAero 11-Jul-23 16:35pm

The first link was a question I posted on Stack Overflow. The answer wasn't really helpful. It helped me identify what connections already existed in the sheet, but didn't provide more info on changing that connection.

The second link does provide background on the properties, but doesn't give an example for changing the connection.

Andre Oosthuizen 12-Jul-23 3:29am

Your message conflicts with your question - Q = I need to create a connection, Com = It helped me identify what connections already existed, meaning that you have a connection, you don't know how to change it - change it to what, where, how, when?

It is like me telling you that I drive a blue car, what brand is it? Share more code in your question, not in the comments, share your code so we don't have to guess what you mean.

BladesAero 13-Jul-23 9:37am

I appreciate your help on this. Currently I have a power PivotTable with an external connection to a csv file. I want to programmatically change that connection to different file on my network. I just used ChatGPT to get started again on this problem. This is what it suggested:

Dim connection As Microsoft.Office.Interop.Excel.WorkbookConnection = wb.Connections(MPPivotTable.PivotCache().WorkbookConnection.Name)
Dim newConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\l46633\Downloads;Extended Properties='Text;HDR=YES;FMT=Delimited(|)'"
connection.OLEDBConnection.Connection = newConnectionString

Andre Oosthuizen 13-Jul-23 12:16pm

You're welcome. What happened when you ran the ChatGPT code?

BladesAero 13-Jul-23 13:24pm

It threw an exception on:
Dim connection As Microsoft.Office.Interop.Excel.WorkbookConnection = wb.Connections(MPPivotTable.PivotCache().WorkbookConnection.Name)

0x800A03EC

Andre Oosthuizen 14-Jul-23 3:35am

Ok, so did you Google the error? You need to find out why your connection failed.

BladesAero 27-Jul-23 12:18pm

Sorry for the vacation delayed response. I think there's a problem with the way the pivot table is set up. It's connection is handled through a query:

let
Source = Csv.Document(File.Contents(GetValue("CSVLocation")),[Delimiter="|", Columns=24, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"REC", Int64.Type}, {"FGCODE", type text}, {"PART NO", type text}, {"DESCRIPTION", type text}, {"INSTANCE", Int64.Type}, {"QTY", Int64.Type}, {"ECA", type text}, {"BAY", type text}, {"BEF", Int64.Type}, {"EEF", type text}, {"MASS", type number}, {"XBAR", type number}, {"YBAR", type number}, {"ZBAR", type number}, {"IXX", type number}, {"IYY", type number}, {"IZZ", type number}, {"PXY", type number}, {"PXZ", type number}, {"PYZ", type number}, {"DENSITY", type number}, {"NEXT ASSY", type text}, {"NEXT INSTANCE", Int64.Type}, {"WETTED AREA", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [MASS] <> 0)
in
#"Filtered Rows"

"CSVLocation" refers to a named range in the excel file which contains the source file location. I've tried manually changing this to a file in my downloads folder and an error pops up stating that the excel table could not be found.

BladesAero 27-Jul-23 12:23pm

Once I change the 2nd line to:
Source = Csv.Document(File.Contents("C:\Users\l46633\Downloads\massprops.csv"),[Delimiter="|", Columns=24, Encoding=1252, QuoteStyle=QuoteStyle.None]),

It seems to work. Now I just need to understand how to change it programmatically.

How do I create a new workbookconnection for an existing pivtotable?

1 solution

Solution 1

Add your solution here

Preview 0

How do I create a new workbookconnection for an existing pivtotable?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us