Change Connection using VBA

tlindeman

Active Member
Joined
Jun 29, 2005
Messages
313
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top