Change Connection using VBA

[tlindeman]

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

 

[jbeaucaire]

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.

 

[tlindeman]

See reply

 

[tlindeman]

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

 

[tlindeman]

Anybody?

 

[tlindeman]

Basically I would like something like this

CommandText = Range(A2)".

However VBA does not recognize this

 

[jbeaucaire]

What exactly is the text value in cell A2?

 

[tlindeman]

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

 

[jbeaucaire]

Hmm, seems like this should work:

Dim MyStr As String
MyStr = Range("A2").Text

With ActiveWorkbook.Connections("Invoices").OLEDBConnection
   .BackgroundQuery = True
   .CommandText = Array(MyStr)

 

[tlindeman]

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