Save the changes of data source for Excel

Question

Hello this is my script for changing the data source and catalog.

When I run the script I can see in the console that the source table and catalog are changing, but when I enter in my DataModel in Excel and check Existing Connection I still see the old one.

Also, when I open the folder and check date modified I see that it's saved the excel file, but the changes for Data Source and Catalog are not there.

Sub changeConn()
Dim conn As Variant
Dim connectString As String

  For Each conn In ActiveWorkbook.Connections
  
    connectString = ActiveWorkbook.Connections("NGNSQLMDR MDR Sales").OLEDBConnection.Connection
    Debug.Print "Before changes: "; connectString
    connectString = Replace(connectString, "Catalog=MDR", "Catalog=MDR")
    connectString = Replace(connectString, "Data Source=SQLMDR", "Data Source=sql.Company.local")
    Debug.Print "After Changes: "; connectString

    conn.OLEDBConnection.Connection = connectString

  Next conn

End Sub

Your code is just creating a String which contains your changes. You don't actually USE that new string to do anything! After your replacement lines, you need to use something like conn.ConnectionString = connectString in order to change the actual connection object's connection string to your connectString with the new details. — jamheadart
– jamheadart, Commented Jul 7, 2020 at 13:04
I added before Next conn and I receive error: application defined or object defined error This is the line of code that I added: conn.OLEDBConnection.Connection = connectString — mns
– mns, Commented Jul 7, 2020 at 13:17
Do you know which object the conn is after the For Each conn line? I don't know if you're using OLE or ADODB con objects or what — jamheadart
– jamheadart, Commented Jul 7, 2020 at 13:25
Hi, I'm using OLEDB. I don't really know how to handle this process, I'm reading and trying to figure out how I can save changes. — mns
– mns, Commented Jul 7, 2020 at 13:28
Can you check what the exact object is in the conn variant when the code is running using the VIEW > LOCALS WINDOW - it should be very simple to set a connection string on the conn object but there are different types of connection object so in order to give you an accurate answer it'd be best to know what the conn object is. — jamheadart
– jamheadart, Commented Jul 7, 2020 at 13:31

jamheadart · Accepted Answer · 2020-07-07 14:03:49Z

0

So after the discussion in the comments which showed that your string was changing but not being assigned to the connection conn object, you were then very close when you tried / added to the question conn.OLEDBConnection.Connection = connectString

but according to this thread here...

Modify an embedded Connection String in microsoft excel macro

...you need to set your connection string to have "OLEDB;" at the start, so prepending your current connection string, so maybe try this:

conn.OLEDBConnection.Connection = "OLEDB;" & connectString

The answer in that thread has quite a few upvotes so I'm hoping this works the same for you!

edited Jul 7, 2020 at 14:03

answered Jul 7, 2020 at 13:57

jamheadart

5,3737 gold badges37 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mns Over a year ago

Also it's not working in this way :( It's creating double OLEDB

mns Over a year ago

Here is a screenshot: imgur.com/KvCqBgL It's very strange, because it's not accepting any solutions...

jamheadart Over a year ago

Hmm so your con string already has the OLEDB at the start of it... I don't know why it's not allowing you to set the new string

Collectives™ on Stack Overflow

Save the changes of data source for Excel

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related