PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Edit Data Connection Source File-VBForums
Results 1 to 5 of 5

Thread: Edit Data Connection Source File

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    3

    Edit Data Connection Source File

    New to the site, hello to everyone.

    I am looking for help with my code, basically I have two workbooks, wb1 connects to wb2 via a file data connection to retrieve table information. When the files are located in the same folders all is good and everything refreshes as required. However when I send the files to another office on a different server etc. the links obviously fall over.

    To make it easy on the end user to update the source connection file I thought about a macro button "update connection" when pressed it would ask the user to select a new source file, so I hunted the net and came across the code below from 2016 which works in terms of asking the user to select a new file but once selected the data 5 query connections don't then update to the new source file, any help would be much appreciated, thanks in advance


    Sub xlTest()

    Dim i As Long
    Dim cnt As Long
    Dim cn

    cnt = ActiveWorkbook.Connections.Count

    'Choose a File
    strPath = Application.GetOpenFilename(Title:="Choose a file", _
    FileFilter:="Excel Files *.xls* (*.xls*),")

    If strPath = False Then
    MsgBox "No file selected. Exiting now.", vbExclamation
    Exit Sub
    End If
    'Change Connection source filepath for each connection
    For i = cnt To 1 Step -1
    Set cn = ActiveWorkbook.Connections.Item(i)
    cn.OLEDBConnection.SourceDataFile = strPath
    Next i

    End Sub

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Edit Data Connection Source File

    Welcome. For future reference, please use formatting tags when posting code for readability. The last two buttons on the editor toolbar provide two different options.
    vb Code:
    1. Sub xlTest()
    2.  
    3. Dim i As Long
    4. Dim cnt As Long
    5. Dim cn
    6.  
    7. cnt = ActiveWorkbook.Connections.Count
    8.  
    9. 'Choose a File
    10. strPath = Application.GetOpenFilename(Title:="Choose a file", _
    11. FileFilter:="Excel Files *.xls* (*.xls*),")
    12.  
    13. If strPath = False Then
    14.     MsgBox "No file selected. Exiting now.", vbExclamation
    15.     Exit Sub
    16. End If
    17. 'Change Connection source filepath for each connection
    18. For i = cnt To 1 Step -1
    19.     Set cn = ActiveWorkbook.Connections.Item(i)
    20.     cn.OLEDBConnection.SourceDataFile = strPath
    21. Next i
    22.  
    23. End Sub
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: Edit Data Connection Source File

    Also, is that actually VB.NET code? Looks like it's either VBA or VB6, so this is the wrong forum. Decide which it is and use the Report Post icon under your post to ask the mods to move this thread to the correct forum.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    3

    Re: Edit Data Connection Source File

    Quote Originally Posted by jmcilhinney View Post
    Also, is that actually VB.NET code? Looks like it's either VBA or VB6, so this is the wrong forum. Decide which it is and use the Report Post icon under your post to ask the mods to move this thread to the correct forum.
    Hi jmcilhinney,

    Appreciate the info etc, was my first post but i'll know going forward

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,208

    Re: Edit Data Connection Source File

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width