# Powerpivot How to change existing connection from excel file to txt file



## RiaM (Feb 18, 2015)

Hi all

I have a rather large (many calculated columns and relationships setup) table in powerpivot that uses an Excel file on my hard drive as the source. I now want to use a text file as the source and am struggling to change the relationship. I've tried editing the connection but the wizard does not allow me to get to where I can change the connection type - therefore I currently only change the source file to another Excel file

Any ideas?

Regards
Ria


----------



## ImkeF (Feb 18, 2015)

Hi Ria,
this might help: https://dmoffat.wordpress.com/2013/...nge-to-another-connection-type-in-powerpivot/
there's another very good one: https://dmoffat.wordpress.com/2013/...ent-side-powerpivot-solution-in-excel-2013-2/
hth, Imke


----------



## RiaM (Feb 19, 2015)

ImkeF said:


> Hi Ria,
> this might help: https://dmoffat.wordpress.com/2013/...nge-to-another-connection-type-in-powerpivot/
> there's another very good one: https://dmoffat.wordpress.com/2013/...ent-side-powerpivot-solution-in-excel-2013-2/
> hth, Imke



Hi Imke

Thanks for the feedback. Actually did come across the blogs but failed with the VB part of it. The following is my VB code. The code fails at the "With ThisWorkbook... " line - subscript out of range error

Any ideas?


```
Sub ConnectExcel()
With ThisWorkbook.Connections(“DelColQty”).OLEDBConnection 'DelColQty is the name of the sheet as it would be named inside PowerPivot
.Connection = Range(“ExcelCon”).Value
.Refresh
End With
End Sub


Sub ConnectTxt()
With ThisWorkbook.Connections(“DelColQty”).OLEDBConnection 'DelColQty is the name of the sheet as it would be named inside PowerPivot
.Connection = Range(“TxtCon”).Value
.Refresh
End With
End Sub
```


----------



## ImkeF (Feb 19, 2015)

The value for "ThisWorkbook.Connections" shouldn't be the Name of your PP sheet, but the Name of your Connection:
In Excel: Tab Data - Connections - Properties: Up there you can copy it.

hth, Imke


----------



## RiaM (Feb 19, 2015)

ImkeF said:


> The value for "ThisWorkbook.Connections" shouldn't be the Name of your PP sheet, but the Name of your Connection:
> In Excel: Tab Data - Connections - Properties: Up there you can copy it.
> 
> hth, Imke



Hi Imke

Thanks, that did help but my code then failed at the .connection line - 1004 "application defined or object defined error". I used a string variable called "nm" to fetch the connection string from my worksheet where I have the connection string saved (did this as I'm a little more comfortable with this approach than the Name approach used in the blog)

Any more ideas? Thanks for helping


```
Sub ConnectExcel()
Dim nm As String


Sheets("Inputs").Activate
nm = Range("H15").Value 'where H15 has the Excel connection string


With ThisWorkbook.Connections("Excel DelColQtyData").OLEDBConnection 'DelColQty is the name of the sheet as it would be named inside PowerPivot
.Connection = nm
.Refresh
End With
End Sub
Sub ConnectTxt()
Dim nm As String


Sheets("Inputs").Activate
nm = Range("H16").Value 'where H16 has the Text file connection string


With ThisWorkbook.Connections("Excel DelColQtyData").OLEDBConnection 'DelColQty is the name of the sheet as it would be named inside PowerPivot
.Connection = nm
.Refresh
End With
End Sub
```


----------



## ImkeF (Feb 19, 2015)

Hi Ria,
no sorry, this is definitely not my area of expertise.
You might consider moving this question to the main Excel-Forum.
BR, Imke


----------



## scottsen (Feb 19, 2015)

Really wish they made this easier  

I just resort to moving any measures to another table, nuking the table and re-creating it in the model.  Don't forget to copy/paste your calculated columns either.    Sucks.


----------



## DickMoffat (Apr 16, 2015)

Hey !!  Hope this isn't too late ...  I have no idea how this popped up for me to see but I am the guy who wrote the original post and I think I can help you - if you're still interested  ..

I think your problem is that you used the Connection name "Excel DelColQtyData" (which you said "is the name of the sheet as it would be named inside PowerPivot").  That is the wrong name.  You need to enter the name of the Database Connection from Excel ... You get that by going to the Data tab, choosing Existing Connections ... and getting the name of the Connection you want to use and put that name in instead. 

This code is strictly talking to Excel and it's Objects ... it has nothing directly to do with PowerPivot.  But because when you created your Connection in Excel you said to make a Data Connection then it hosts the data in PowerPivot. But the Connection is strictly an Excel Connection.  That is the key here  - you can only programmatically change Connections that were created in Excel (and not all types btw .. but it should work for any OLEDB Provider like SQL or Access).

Hope this makes sense.  I sense your frustration  .. Welcome to my world.  I guess I have the advantage of 30 years of spreadsheet development and most of that data related so I've learned to suppress my frustrations (most of the time  )...  It's tough.  Please don't give up on PowerPivot and this kind of technique.  I believe it's worth it in the end ...

**** Moffat



RiaM said:


> Hi Imke
> 
> Thanks, that did help but my code then failed at the .connection line - 1004 "application defined or object defined error". I used a string variable called "nm" to fetch the connection string from my worksheet where I have the connection string saved (did this as I'm a little more comfortable with this approach than the Name approach used in the blog)
> 
> ...


----------

