CODEWITHSUNDEEP
sqlexcelvba
F. Valle
F. Valle

Reputation: 117

Run SQL on Excel password protected worksheet. Connection issue

I have an Excel worksheet which is protected with a password.

I am able to connect and retrieve data only if this Excel is open. If the Excel worksheet is closed, code throws an automation error

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=C:\Portfolio v3.02.xlsb;HDR=Yes';User=['FF'];Password=['XXX']"

Conn.Open sconnect

Upvotes: 1

Views: 425

Answers (1)

Rich
Rich

Reputation: 191

Cannot be done with a connection string.

"If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file." Source: https://www.connectionstrings.com/excel/

Potential Workaround if you have a coding environment (prompts with Password dialog) https://www.connectionstrings.com/how-to-open-password-protected-excel-workbook/

Another (obvious) one, is to remove the password.

Been down this road many times.

Upvotes: 1

Related Questions