Replacing Excel Pivot and MSQuery connection sources using VBA 1

[stburkeuk]

We are about to migrate our shared network drives to a new structure that involves the change of the letter ID from J:\ to I:\.

I have written some code to change the connection string on some linked tables in Access using the .connect property and substituting the new path.

However I have an Excel workbook that has several Pivot Tables and MS Queries (almost 20 at last count) in that extract data from a Database on the Shared Drive - I have tried with out sucess to find a way to manipulate the connection property in a similar way using VB.
Does anyone know of a method I Could follow.

We are currently using Excel 97 on an NT platform.

Thanks in advance.

 

[SkipVought]

stburkeuk,

Macro record EDITING your MS Query. Just [Next][Next][Next][Finish]

Post your recorded code, unless you can figger out how to modify the path/data source.

Once modified, run with the new connect string and sql.

For instance, here's some code I have been using

    Dim sConnsect As String, sConnect As String
    With Worksheets(sSheet)
        [Path] = "N:\mfcommon3"
        [DATABASE] = "BlockEff"
        sConnect = "ODBC;" & _
            "DSN=MS Access Database;" & _
            "DBQ=" & [Path] & "\" & [DATABASE] & ".mdb;" & _
            "DefaultDir=N:\mfcommon3\metzgerc;" & _
            "DriverId=25;" & _
            "FIL=MS Access;" & _
            "MaxBufferSize=2048;" & _
            "PageTimeout=5;"
        bQuery = True
        Select Case sSheet
            Case "lstProduct"
                squery = "SELECT DISTINCT AggregatedOrders.Product " & _
                    "FROM `" & [Path] & "\" & [DATABASE] & "`.AggregatedOrders AggregatedOrders "
        End Select
        If bQuery Then
            With .Cells(1, 1).CurrentRegion.QueryTable
                .Connection = sConnect
                .CommandText = squery
                .Refresh BackgroundQuery:=False
            End With
            NameTableRanges .Name
        End If
    End With

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stburkeuk]

I have tried to 'play' around with this method - I have tried edition the ".connect" string but don't really understand the Array(array(....))

Thanks

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 23/03/04 by SIMON TIMOTHY BURKE
'

'
    Range("D7").Select
    
    'This is a sample Query Table
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access 97 Database;DBQ=J:\GLOBCUST\GTS Controls MIS\GTS Controls MIS Secure\OneStopDatabaseMaster.mdb;DefaultDir=H:\MyDo" _
        ), Array("cs\J;DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;"))
        .sql = Array( _
        "SELECT tblMonsterTradesExport.txtResponsibility, tblMonsterTradesExport.Type, tblMonsterTradesExport.`# Standard`, tblMonsterTradesExport.`Value Standard`, tblMonsterTradesExport.`#Standard to 2 mths`" _
        , _
        ", tblMonsterTradesExport.`Value Standard to 2 Mths`, tblMonsterTradesExport.`#2 - 3 Months`, tblMonsterTradesExport.`Value 2-3 Months`, tblMonsterTradesExport.`# 3 Months +`, tblMonsterTradesExport.`V" _
        , _
        "alue 3 Months Plus`" & Chr(13) & "" & Chr(10) & "FROM tblMonsterTradesExport tblMonsterTradesExport" & Chr(13) & "" & Chr(10) & "ORDER BY tblMonsterTradesExport.txtResponsibility, tblMonsterTradesExport.Type" _
        )
        .Refresh False
    End With
    ActiveWindow.SmallScroll ToRight:=44
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'ITS Eurobonds' '23/01/04'" _
        , xlDataOnly
    'Microsoft Excel turned off the background query option, so that the macro you _
        recorded will run correctly. For more information, see Microsoft Excel Help.
    ActiveSheet.PivotTableWizard BackgroundQuery:=False
End Sub

 

[SkipVought]

Loose the array! Here's your data...

    Dim sConnsect As String, sConnect As String
    With Worksheets(sSheet)
        sConnect = "ODBC;" & _
                "DSN=MS Access 97 Database;" & _
                "DBQ=J:\GLOBCUST\GTS Controls MIS\GTS Controls MIS Secure\OneStopDatabaseMaster.mdb;" & _
                "DefaultDir=H:\MyDocs\J;" & _
                "DriverId=25;" & _
                "FIL=MS Access;" & _
                "MaxBufferSize=512;" & _
                "PageTimeout=5;"

        bQuery = True
        squery = "SELECT tblMonsterTradesExport.txtResponsibility, " & _
            "tblMonsterTradesExport.Type, tblMonsterTradesExport.`# Standard`, " & _
            "tblMonsterTradesExport.`Value Standard`, " & _
            "tblMonsterTradesExport.`#Standard to 2 mths`, " & _
            "tblMonsterTradesExport.`Value Standard to 2 Mths`, " & _
            "tblMonsterTradesExport.`#2 - 3 Months`, " & _
            "tblMonsterTradesExport.`Value 2-3 Months`, " & _
            "tblMonsterTradesExport.`# 3 Months +`, " & _
            "tblMonsterTradesExport.`Value 3 Months Plus` " & _
            "FROM tblMonsterTradesExport tblMonsterTradesExport " & _
            "ORDER BY tblMonsterTradesExport.txtResponsibility, tblMonsterTradesExport.Type"
        If bQuery Then
            With .Cells(1, 1).CurrentRegion.QueryTable
                .Connection = sConnect
                .CommandText = squery
                .Refresh BackgroundQuery:=False
            End With
        End If
    End With

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stburkeuk]

Thanks for the prompt responce, That looks great for one table.

What I want to do is to loop through each table on the workbook, extract the "DBQ" string and replace the J with an I.

I can handle the string manipulation but I can't seem to return the string to manipulate -

If I can get these than I can inset the variable in the above code.

 

[SkipVought]

All of the new paths will be identical, right? See my first post that uses PATH and DATABASE name.

I'd loop thru each query table and put each connect & sql in a sheet named 'DB Parms'

r=1
for each ws in worksheets
  for each qt in ws.querytables
    with qt
      sheets("DB Parms").Cells(r, 1).Value = .Connection
      sheets("DB Parms").Cells(r, 2).Value = .CommandText 
      r = r + 1
    end with
  next
next

update the data and then use to update each qt.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stburkeuk]

Great - that looks like the code I was trying to get - a great way to start - many thanks you have saved me several hours work.

With the Pivot Tables that return data from an external source is there a way of doing the same thing - using excel xp I tried the below.

Sub testQery()
Dim r As Integer
Dim ws As Worksheet
Dim qt As PivotTable

r = 1
For Each ws In Worksheets
  For Each qt In ws.PivotTables
    With qt
      Sheets("sheet2").Cells(r, 1).Value = .sourcedata
     ' Sheets("sheet2").Cells(r, 2).Value = .CommandText
      r = r + 1
    End With
  Next
Next

End Sub

.Sourcedata returns the connection data but I cannot find the SQL string to select the table/data. - do I need this or can I just change the one property?

Also with the pivot table would I use pivottable.refreshtable in the place of your ".refresh backgroundquery=false"

many thanks

Simon

 

[SkipVought]

For PivotTables, just change the sourcedata.

Yes, refreshtable method.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stburkeuk]

Skip,

Almost there but I am getting an error message with Pivot tables. - I can read the .sourcedata property without problems, write it to a cell and then edit the string ok
However when I try to amend the Pivot tables properties I get an error message "Application-defined or object-defined error"

The below code is the subroutine to renew the links after the details have been amended.
The Querytable section works fine. - what am I doing wrong?

Sub changeconnection()
'reads the information from the worksheet - "Connection Details" and uses them to change the connection details.
    Dim r As Integer
    Dim strConnection As String
    Set wkDestination = ThisWorkbook.Worksheets("Connection Details")
    For r = 2 To wkDestination.Range("a1").CurrentRegion.Rows.Count
        Select Case wkDestination.Cells(r, 1).Value
        
        Case "PivotTable"
            With wkb.Worksheets(wkDestination.Cells(r, 2).Value).PivotTables(wkDestination.Cells(r, 3).Value)
                strConnection = wkDestination.Cells(r, 4).Value
       [COLOR=black yellow]         .SourceData = strConnection[/color]
                .RefreshTable
            End With
            
        
        Case "QueryTable"
                   
            With wkb.Worksheets(wkDestination.Cells(r, 2).Value).QueryTables(wkDestination.Cells(r, 3).Value)
            
                .Connection = wkDestination.Cells(r, 4).Value
                .Refresh BackgroundQuery:=False
            End With
    
        End Select
    Next r
End Sub

Microsoft Help gives -" External data source - An array. Each row consists of an SQL connection string with the remaining elements as the query string, broken down into 200-character segments."
Is this what I need to do and if so how do I go-about in bearing in mind that I want an general purpose tool to do several workbooks with different connections.

The Pivot Table I am trying has a .sourcedata string of 231 charectars - I can split this but how to create the array?

 

[SkipVought]

This is a function that I used back in the XLODBC days since there was a similar limitation on the length of a query.

Function StringToArray(Query As String) As Variant

    Const StrLen = 127 ' Set the maximum string length for
                       ' each element in the array to return
                       ' to 127 characters.
    Dim NumElems As Integer
    Dim Temp() As String
    
    ' Divide the length of the string Query by StrLen and
    ' add 1 to determine how many elements the String array
    ' Temp should contain, and redimension the Temp array to
    ' contain this number of elements.
    
    NumElems = (Len(Query) / StrLen) + 1
    ReDim Temp(1 To NumElems) As String
    
    ' Build the Temp array by sequentially extracting 127
    ' segments of the Query string into each element of the
    ' Temp array.
    
    For i = 1 To NumElems
       Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
    Next i
    
    ' Set the function StringToArray to the Temp array so it
    ' can be returned to the calling procedure.
    
    StringToArray = Temp

End Function

So use it like this...

.SourceData = StringToArray(strConnection)

Try this and see if it works.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stburkeuk]

back again!

The stringtoarray function works well in that I can return each of the elements of the array indvidually however I still cannot set .sourcedata property.
This time the error message is -

[Microsoft][ODBC Microsoft Access 97 Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The only thing that I have changed in the connection string is the drive letter on the DBQ: and DefaultDIR: properties.
Should I be including the full sql statement selecting the tables etc and if so how do I get hold of it?

The Connection string returned is

DSN=MS Access 97 Database;DBQ=J:\Globcust\GTS Controls MIS\GTS Controls MIS Secure\OneStopDatabaseMaster.mdb;DefaultDir=J:\Globcust\GTS Controls MIS\GTS Controls MIS Secure;DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;

 

[SkipVought]

Did you check out the SQL? THere may be a reference in there to the source.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stburkeuk]

I have got the SQL out of the .sourcedata array

                    sdarray = .SourceData
                    For i = LBound(sdarray) To UBound(sdarray)
                        strSourcedata = strSourcedata & sdarray(i)
                    Next i

                    wkDestination.Cells(r, 4).Value = strSourcedata

I can feed the amended data back into an array but when it comes to

                strConnection = StringToArray(wkDestination.Cells(r, 4).Value)
       [COLOR=black yellow]         .SourceData = strConnection[/color black yellow]

to reload the .source data I think that it is only using the first element of the strConnection array as I am still getting the SQL error message. I have amended the StringtoArray function to ensure that the Select statement starts with in a fresh element of the array - as it does when I "offload" it but this still does not seem to work.

I have tried to explore means of manipulating each element of the .sourcedata array,
While I can identify the number of elements using Ubound(.sourcedata) (3 in the test Pivot) I cannot make reference each element in the same way as normal array;s eg

debug.print .sourcedata(1)

no matter what action i try - reading or writing - to the array in this way I get an error message "Object not a collection".
Do I need to be writing each element to the .sourcedata seperatley? If so is there an alternative way to reference the elements of an array?
Or am I on the wrong track in resolving this?


The connection string is now -

DSN=MS Access 97 Database;DBQ=I:\Globcust\GTS Controls MIS\GTS Controls MIS Secure\OneStopDatabaseMaster.mdb;DefaultDir=I:\Globcust\GTS Controls MIS\GTS Controls MIS Secure;DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;SELECT tblOSTradeHistoryExport.`Report Date`, tblOSTradeHistoryExport.txtResponsibility, tblOSTradeHistoryExport.`Out of Standard`
FROM tblOSTradeHistoryExport tblOSTradeHistoryExport
ORDER BY tblOSTradeHistoryExport.`Report Date`

 

[SkipVought]

Your connect string should be

    sConnect = "DSN=MS Access 97 Database;" & _
        "DBQ=I:\Globcust\GTS Controls MIS\GTS Controls MIS Secure\OneStopDatabaseMaster.mdb;" & _
        "DefaultDir=I:\Globcust\GTS Controls MIS\GTS Controls MIS Secure;" & _
        "DriverId=25;" & _
        "FIL=MS Access;" & _
        "MaxBufferSize=512;" & _
        "PageTimeout=5;"

Your SQL string should be

    sSQL = "SELECT tblOSTradeHistoryExport.`Report Date`, " & _
        "tblOSTradeHistoryExport.txtResponsibility, " & _
        "tblOSTradeHistoryExport.`Out of Standard` " & _
        "FROM tblOSTradeHistoryExport tblOSTradeHistoryExport " & _
        "ORDER BY tblOSTradeHistoryExport.`Report Date` "

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stburkeuk]

Got there - but this has created another question.

I tested the method using a shorter path to the Database (c:\) and it worked.
I added the Full database path back in but left the DefaultDIR as c:\ and it still worked.
However if I add any directories to the defaultdir path the method fails with the above SQL error.

I would really love to know if there is a reason for this or is it just one of those "Microsoft things"?

 

[SkipVought]

I dunno???

Must be a Microsoft THANG! ;-)



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884