Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Change Connection using VBA

This is a discussion on Change Connection using VBA within the Excel Questions forums, part of the Question Forums category; Is it possible to edit an existing connection (called Invoices) and change the command text to equal a paste special? ...

  1. #1
    Board Regular
    Join Date
    Jun 2005
    Posts
    306

    Default Change Connection using VBA

    Is it possible to edit an existing connection (called Invoices) and change the command text to equal a paste special? I am recording a macro to connect to a SQL database and I need to be able to change the command text to a different date each time. I can set it up in excel, I just get stuck on how to change the comman text in VBA. If you need more information , let me know.

    Thank You
    Tony

  2. #2
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,812

    Default Re: Change Connection using VBA

    Show us the existing code you use to connect to a specific db, then highlight the part you want to become variable and explain how you would like it resolved each time.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  3. #3
    Board Regular
    Join Date
    Jun 2005
    Posts
    306

    Default

    See reply
    Last edited by tlindeman; Aug 18th, 2011 at 02:24 PM.

  4. #4
    Board Regular
    Join Date
    Jun 2005
    Posts
    306

    Default Re: Change Connection using VBA

    Here is the code I would like changed,


    'With ActiveWorkbook.Connections("Invoices").OLEDBConnection
    .BackgroundQuery = True
    .CommandText = Array( _
    "EXEC BG.dbo.GP_AR_Collection @StartDt = ' 2011-01-02 00:00:00', @EndDt =' 2011-06-02 00:00:00'" _
    )'

    I would like to change the Command text to either point to Cell A2, or copy paste special each time.

    Thank You

  5. #5
    Board Regular
    Join Date
    Jun 2005
    Posts
    306

    Default Re: Change Connection using VBA

    Anybody?

  6. #6
    Board Regular
    Join Date
    Jun 2005
    Posts
    306

    Default Re: Change Connection using VBA

    Basically I would like something like this

    CommandText = Range(A2)".
    However VBA does not recognize this

  7. #7
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,812

    Default Re: Change Connection using VBA

    What exactly is the text value in cell A2?
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  8. #8
    Board Regular
    Join Date
    Jun 2005
    Posts
    306

    Default Re: Change Connection using VBA

    It is the input for Sql , so right now it is
    EXEC BG.dbo.GP_AR_Collection @StartDt = ' 2011-01-02 00:00:00', @EndDt =' 2011-06-02 00:00:00'
    I create a macro so that the user can enter a date range in antother cell and then it will paste special into A2, so for example if they want from 3/03/11 to 05/01/11, Cell A2 woudl state

    EXEC BG.dbo.GP_AR_Collection @StartDt = ' 2011-03-11 00:00:00', @EndDt =' 2011-05-01 00:00:00'
    which will change my sql query.

    Thank you
    Tony

  9. #9
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,812

    Default Re: Change Connection using VBA

    Hmm, seems like this should work:
    Code:
    Dim MyStr As String
    MyStr = Range("A2").Text
    
    With ActiveWorkbook.Connections("Invoices").OLEDBConnection
       .BackgroundQuery = True
       .CommandText = Array(MyStr)
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  10. #10
    Board Regular
    Join Date
    Jun 2005
    Posts
    306

    Default Re: Change Connection using VBA

    Perfect thank you very much, I was on the right track, I just used dim MYVALUE as object and it failed. In any case I really appreciate your help.

    Tony

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com