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

ANDYB0ARDMAN

New Member
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.

Code:
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

Well-known Member
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.
 

jkpieterse

Well-known Member
Given that you are trying to update Querytables and not tables you need a different object than ThisWOrkbook.QUeries:
Code:
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

New Member
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.

Code:
    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

Well-known Member
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

New Member
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:
Code:
    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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top