2

I have 10 Excel files that are connected to a SQL Server by OLEDB and I need to refresh them everyday one by one manually. When I refresh them I need to insert a password. I tried to make a macro to automate this process, but I didn't succeed. I did this function to automate one of my workbooks:

Sub updateABC()
  Workbooks("Teste").Connections("SQL Server_Azure1").OLEDBConnection.Refresh
End Sub

And the Excel ask for the password to SQL Server logon. Is there any way to put this password automatically using vba?

Thanks.

Improve this question
6
  • Is it always the same password? I am not totally sure what you are trying to do here. Commented Nov 13, 2017 at 18:43
  • Does the password prompt automatically emerge available for you to type? in other words, are no clicks required? Perhaps you could set to delay an automatic keystroke? Commented Nov 13, 2017 at 18:52
  • I think you need to look at ADO and connection string. Commented Nov 13, 2017 at 19:00
  • stackoverflow.com/questions/35879899/… Commented Nov 13, 2017 at 20:21
  • Sorry for the delay. The password is the same for all workbooks. Every time when I refresh a workbook, a window is "prompt automatically" asking for the password to sql server logon. Commented Nov 16, 2017 at 17:08

1 Answer 1

Reset to default
1

There is a way in Excel without VBA:

  1. You can go to Data then Connections
  2. Click Properties on the top Connection
  3. Click the Definition Tab
  4. There is a tick box "Save Password" about half way down, you will get a prompt about the password being stored un-encrypted.
  5. Say yes to this
  6. Click OK

Do the same for all of your connections

You can then refresh all connections and you will be prompted to enter the password for each connection. Once you do this and save the document it will not ask you for your passwords again.

You can also tick "Refresh data when opening the file" on the Usage tab to have the connections refreshed when the workbook is opened.

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

Comments

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.