+ Reply to Thread
Results 1 to 7 of 7

Modify Where clause of a Data Connection with VBA

  1. #1
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Modify Where clause of a Data Connection with VBA

    Hello Excel GuRu's,

    I was wondering if this is possible....I would like to add some VBA code that will change the date values I have in my data connection.

    I.E. I have the following Data Connection:

    Data Connection.PNG

    Since this is for a metrics reports, which requires me to pull same data from different date ranges, I would like to be able to have a macro that will prompt me to enter in the values I have highlighted in the image.

    I know I can do this by using SQL in VBA itself and pull a record set, however if this way is possible, I would like to attempt this first.

    Thank You

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Modify Where clause of a Data Connection with VBA

    Can't you just use a parameter query? There's no need for VBA

  3. #3
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Modify Where clause of a Data Connection with VBA

    I have not used a parameter query in Excel before, so not sure how to accomplish this. I have tried using the ?'s where the dates are, based on some links I looked at before I posted this, and excel did not like those at all. Could you provide a little more info on using parameter queries inside a data connection?

    Thanks

  4. #4
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Modify Where clause of a Data Connection with VBA

    Ok, update, so I have went through several attempts now to create a new SQL data connection using the wonderful MS Query so I can add criteria in, HOWEVER I guess the folks who came up with this nifty little tool thought that only single table queries should be used with criteria since the following script will not allow for this in MS Query....errrr

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Modify Where clause of a Data Connection with VBA

    Stick a simple query through msquery to get your parameters, then just copy your sql over the top in the query table options bit of a sneaky work around, but it works

  6. #6
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Modify Where clause of a Data Connection with VBA

    I think maybe the problem I am having is that my existing data connections are OLE DB QUERY and not MS Query and hence they are not recognizing a ? as a parameter input. I may just have to call a "no joy" on this one and just manually change the dates. This really only saves a couple of seconds, just thought it would be cool if Excel had this ability. I did try what you said Kyle, however when I copied over my full SQL it crapped out on me again.

  7. #7
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Modify Where clause of a Data Connection with VBA

    Please note to all that this is NOT a SOLVED issue, just giving up on this as it is no where near the top of my priority lists!! if anyone does know of a solution to do this please feel free to post, however I have just decided that in my case it only saves a total of less than a minute, not worth the extra coding to make it happen.

    Thanks Kyle for the help on this though.

+ 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. Replies: 2
    Last Post: 05-15-2015, 04:12 PM
  2. How do I modify a QueryTable connection string before it executes?
    By Jeff_M in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2012, 06:22 PM
  3. Excel 2007 data connection not using connection file
    By algilstrap in forum Excel General
    Replies: 0
    Last Post: 06-09-2009, 03:01 PM
  4. VBA excel - problem with having clause in sql with adodb.connection/recordset
    By ukp9999 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2005, 03:50 AM
  5. calling all pro's!!! macro that can modify dates in odbc connection to mysql
    By majestik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2005, 03:05 PM

Tags for this Thread

Bookmarks

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