+ Reply to Thread
Results 1 to 2 of 2

Using Macro to Reset MS Query Connection String

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Using Macro to Reset MS Query Connection String

    Hello everyone. I've newly joined this forum. However I've browsed through the forum many times trying to trouble shoot various issues.

    I’m having a specific issue now regarding creating a pivot table to consolidate data from various named ranges throughout the workbook (same workbook). Overall this process is well documented and I’ve been able to create the pivot table. It pulls data from the various ranges using MS Query. I can get this to work, however the file that I’m putting this into is a template file used to create cost estimates for large industrial construction projects. Every time the template is copied and renamed, the connection string for the query no longer works and needs to be manually re-established. I’d like to set up a push button macro to reset this connection to whatever the current workbook file name and file path is. I know this is possible, but I don’t have nearly enough skill in VBA coding to figure it out.

    For security reasons I’m not able to post the workbook, but I can list some information that should indicate what needs to be done.

    The name of the query is “SumDataQuery”
    The command text I need to execute is:

    Select * from A01Table
    union all
    Select * from A02Table
    union all
    Select * from A03Table
    union all
    Select * from A04Table
    union all
    Select * from A04DTable
    union all
    Select * from A05Table
    union all
    Select * from A06Table
    union all
    Select * from A07Table
    union all
    Select * from A08Table
    union all
    Select * from A09Table
    union all
    Select * from E02Table

    I would like to set up a button on the same worksheet as the pivot to reset the connection. I’ve named the button macro “ResetConnection”

    Could someone please help me figure out the correcting coding. As stated earlier my VBA skill is poor at best. Getting this pivot to work properly has the potential to drastically improve the functionality of this spreadsheet.

    I would greatly appreciate any help someone could provide.

    Thanks, Kyle

  2. #2
    Valued Forum Contributor
    Join Date
    Great Britain
    MS-Off Ver
    Excel 2003

    Re: Using Macro to Reset MS Query Connection String

    See if this gets you started - use the QueryTable object and its relevant properties:
    Please Login or Register  to view this content.
    Post responsibly. Search for excelforum.com

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel External Connection Dropping Password from Connection String?
    By mar0isa in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-06-2015, 10:35 AM
  2. Replies: 0
    Last Post: 04-12-2013, 10:16 AM
  3. [Macro] Excel Query Connection with Inputbox for changing filters
    By JohnGaltnl in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2011, 04:24 AM
  4. Web Query using ADO(?) connection
    By reloader in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2006, 05:25 PM
  5. Change Connection of a Query
    By Jean-Francois in forum Excel General
    Replies: 1
    Last Post: 10-05-2005, 08:05 PM

Tags for this Thread


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1