how to specify database user credentials in a connection array string (Excel VBA)

The Connection string is an array of simple strings. You need to locate the one containing the user name and password, then break that section apart and insert the values from the cell. Put sections of the connection string on different lines to help break it up. Break up each section if it helps to isolate the substring that you want to manipulate.

.Connection = Array("OLEDB;Provider=MSDASQL.1;Persist Security Info=True;" & _
                    "Extended Properties=""DSN=NZSQL;Database=consumerdb;Servername=192.54.97.102;", _
                    "UID=USERNAME;PWD=****;" & _
                    "Port=5480;ReadOnly=0;SQLBitOneZero=0;LegacySQLTables=0;NumericAsChar=0;" & _
                    "ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateFormat=1;" & _
                    "SecurityLevel=preferredUnSecured;CaCertFile="";Initial Catalog=CONSUMERDB_USERNAME" _
                   )

Now if is easy to find the username and password and concatenate the values into that portion.

Dim usr As String, pwd As String
usr = Range("A1").Value
pwd = Range("B1").Value
.Connection = Array("OLEDB;Provider=MSDASQL.1;Persist Security Info=True;" & _
                    "Extended Properties=""DSN=NZSQL;Database=consumerdb;Servername=192.54.97.102;", _
                    "UID=" & usr & ";PWD=" & pwd & ";" & _
                    "Port=5480;ReadOnly=0;SQLBitOneZero=0;LegacySQLTables=0;NumericAsChar=0;" & _
                    "ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateFormat=1;" & _
                    "SecurityLevel=preferredUnSecured;CaCertFile="";Initial Catalog=CONSUMERDB_USERNAME" _
                   )