0

We have an excel file which contains a connection to a database to retreive data (with a select statement).

We want to update via a (preferrably powershell) script the connection string of that file to make it query another server instead.

So for exemple : I have report.xlsx file which connects to server A. I run update-connection.ps1 And when I open report.xlsx it now connects to server B.

Any idea how we could do that?

Thanks.

Improve this question
2
  • Are you using an ODBC (system DNS) for the connection? Commented Nov 10, 2011 at 10:53
  • I have this in my connection string : Provider=SQLOLEDB.1; does that answer your question ? (and the file is computer-independant) Commented Nov 10, 2011 at 11:01

3 Answers 3

Reset to default
1

It should be fairly easy if you decide (are allowed) to store the connection (server name) in a worksheet. Your VBA code can dynamically build the connection string based on the value of a cell. (I would probably create a named range and use it in the code).

I don't know PowerShell but the code can look something like:

$workbook.Range("Server").Value2 = "PROD_01"

You can make the worksheet hidden if you wish, but it is not a serious security.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sounds like the best idea so far, I think I'll try that.
Mixing this solution with the closed xml project suggested before might bring me where I want, although I had hoped for an easier solution.
0

You could try automating Excel via PowerShell, as in this article: http://kentfinkle.com/PowershellAndExcel.aspx

If you don't want to automate Excel then you could try using something like ClosedXML in your PowerShell script: http://closedxml.codeplex.com/

Improve this answer

1 Comment

that's a good start! After browsing a little bit their code, I'm not sure it's gonna work though...
-1

You can parse the connectionstring with System.Data.Common.DbConnectionStringBuilder. Check this SO thread:

Powershell regex for connectionStrings?

Improve this answer

3 Comments

Thanks for you answer, but I don't see in these threads how to update the connection string of the xlsx file.
It would be easier if you could post the code you are using to read the value.
That's my issue: I don't read nor write the value yet. I'll provide more info in my question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.