FCS wrote:
Agree, is this not a Bug?. The main issue is that I have to rely on
the user specifying the same mdw as in the DNS and keeping
consistency which is clearly a problem.

Is your mdw location changing that much? Generally you would point to the
database and you would never need to change it unless you moved the
database. Are you sending this file to other people who have their database
in different locations? If so, you may want to ask them via GetOpenFilename
where the database is the first time they open the file, then you shouldn't
have to change it.

Is it a bug? Surely MS will claim it's a feature or a decision.


I've also noticed that the PivotCache connection property (as
reported in the VBA watch) seems to truncate the end of the string
when using long file path names although it seems that "internally"
things function properly. I see the same issue in the SourceData
property (array) of the PivotTable. Is this just a reporting issue?
I am concerned that the code that you propose may not work in that
case.

The following may or may not be true: If your connection string is greater
than 255 characters, it's actually stored in an array of strings, not just a
string. Same goes for the CommandText property. If you were to record a
macro that creates an external data table, you would see code like

Connection:=Array("DSN: Some data; somemore data;")

The recorder puts the string in an array even if it's not a long string,
which leads me to believe that it *has* to be in an array if it's too long.
I think if you want to see the rest of the long connection string, you would
have to use

?Sheet1.PivotTables(1).PivotCache.Connection(1) 'may be zero
?Sheet1.PivotTables(1).PivotCache.Connection(2) 'may be one

but I haven't tested it to see if it's true. SourceData is, I believe, a
deprecated property which has not kept up with the changes to the querytable
object. I don't think you can rely on that property in any case. All this
may not be true because I've asked Microsoft directly and have never
received an answer.

I think you're right that my code would fail for long connection strings.
You need to convert the string into an array before you use it. Honestly,
I've never had that code fail even with long connection strings, but it must
be possible.

Function StringToArray(sInput As String) As Variant

Dim i As Long
Dim lCount As Long
Dim aTemp() As String

For i = 1 To Len(sInput) Step 255
lCount = lCount + 1
ReDim Preserve aTemp(1 To lCount)
aTemp(lCount) = Mid(sInput, i, 255)
Next i

StringToArray = aTemp

End Function

Run your strings through that function before you put them into the
Connection or CommandText property and they should work under any
circumstances. You'd use the function like:

Dim vaConn as Variant

vaConn = StringToArray("Somelongstring")


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com