Connectionstring not being set properly by code - query data source not changing

[ANDYB0ARDMAN]

Hi
I have two excel files stored in Dropbox - one a database that is not opened by users and the other a tool that runs two query tables from the database and is supposed to refresh the query tables on auto_open by setting the connectionstring to the users filepath to find the data source - database file.
Using Query tables is new for me so I'm struggling a bit!
Here's where I am up to at the moment - this runs and then throws an error on the last line.
When I go into query properties I see the data source has not changed.

Sheets("Table-Data").Select
    Range("a1").Select
    
    Dim username As String
    username = Environ("username")
    
    Dim q As WorkbookQuery
    For Each q In ThisWorkbook.Queries
        Connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database;Extended Properties=Excel 12.0 Macro;HDR=YES;"
        
    Next
ActiveWorkbook.connections("Query - Data (13)").refresh

I've scoured the forums and see references to ADODB.Connection etc but the syntax doesn't work for me.

I'm at a loss and getting frustrated....I don't code very often nowadays and I just need to get this done - everything else works fine.....

All help gratefully received.

Thanks Andy

 

[jkpieterse]

You're currently assigning a text value to the variable called Connectionstring. But you do not use that string to update any property of q.

 

[ANDYB0ARDMAN]

You're currently assigning a text value to the variable called Connectionstring. But you do not use that string to update any property of q.

Thanks - ok - I dont know how to update the proerty of q - how do ou do hat pease?

Andy

 

[jkpieterse]

Given that you are trying to update Querytables and not tables you need a different object than ThisWOrkbook.QUeries:

Sub UpdateQueryTables()
    Dim Qt As QueryTable
    Dim Sh As Worksheet
    Dim ListObj As ListObject
    For Each Sh In Worksheets
        'For connections which ar ein tables:
        For Each ListObj In Sh.ListObjects
            If ListObj.SourceType = xlSrcExternal Then
                With ListObj.QueryTable
                    .Connection = "Your connection string goes here"
                End With
            End If
        Next
        'For older connections not in a table:
        For Each Qt In Sh.QueryTables
            Qt.Connection = "Your connection string goes here"
        Next
    Next
End Sub

 

[ANDYB0ARDMAN]

Jan - evening

Thanks so much for this - I'd just worked out I was refreshing connections and not query tables....was about to pack in for day!

I've copied your code in and it runs - but still got an issue though - not changing the query table data source so still get error message on refresh.

    Dim username As String
    username = Environ("username")
    
    Dim Qt As QueryTable
    Dim Sh As Worksheet
    Dim ListObj As ListObject
    For Each Sh In Worksheets
        'For connections which ar ein tables:
        For Each ListObj In Sh.ListObjects
            If ListObj.SourceType = xlSrcExternal Then
                With ListObj.QueryTable
                    .Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
                End With
            End If
        Next
        'For older connections not in a table:
        For Each Qt In Sh.QueryTables
            Qt.Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
        Next
    Next
ActiveWorkbook.RefreshAll

The table is still referencing another location in computers C: Drive rather than the one in the connection string - I've set it up this way to replicate the need for a username driven location.

Any thoughts what i is still an issue?

I really appreciate your time with this.

thanks...........Andy

 

[jkpieterse]

Have you actually tried changing the connection manually through the Excel user interface? I see you're using a dropbox folder, maybe Excel doesn't like that?

 

[ANDYB0ARDMAN]

Jan - hi

Thanks for coming back to me - sorry for delayed response - was away yesterday.
I've tried the code just working between C: and D: drive on my PC and it still doesnt change the query table data source so evaluates as error again - says cannot find c: drive (where table is pointing now).

Manually changing is no issue - either within C, D or Dropbox - that caught me out as it worked for me but then wouldnt work for another user with different user name in file location path....

Code is now:

    Dim username As String
    username = Environ("username")
    
    Dim Qt As QueryTable
    Dim Sh As Worksheet
    Dim ListObj As ListObject
    For Each Sh In Worksheets
        'For connections which are in tables:
        For Each ListObj In Sh.ListObjects
            If ListObj.SourceType = xlSrcExternal Then
                With ListObj.QueryTable
    '                .Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
                    .Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
                End With
                
            End If
        Next
        'For older connections not in a table:
        For Each Qt In Sh.QueryTables
        '    Qt.Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & Environ("UserName") & "\Dropbox\Delphy - Reports\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"
            Qt.Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Crop Chemical Database\Crop Chemical Database.xlsm;Extended Properties=Excel 12.0 Macro;HDR=YES;"


        Next
    Next


    ActiveWorkbook.RefreshAll

As you can see I deactivated the dropbox string and replaced with local drive string.

There would seem to be no reason why this cannot work but....!

Help very much appreciated.

regards..........Andy