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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.